Sep 13, 2022 · Sep 13, 2022 · Sep 13, 2022 · Sep 15, 2022 · Sep 17, 2022 · Sep 19, 2022
diff --git a/coderd/coderd.go b/coderd/coderd.go
 r.Get("/", api.template)
 r.Delete("/", api.deleteTemplate)
 r.Patch("/", api.patchTemplateMeta)
 r.Get("/user-roles", api.templateUserRoles)
 r.Route("/versions", func(r chi.Router) {
 r.Get("/", api.templateVersionsByTemplate)
 r.Patch("/", api.patchActiveTemplateVersion)
diff --git a/coderd/coderdtest/coderdtest.go b/coderd/coderdtest/coderdtest.go
 // with the responses provided. It uses the "echo" provisioner for compatibility
 // with testing.
 func CreateTemplateVersion(t *testing.T, client *codersdk.Client, organizationID uuid.UUID, res *echo.Responses) codersdk.TemplateVersion {
 t.Helper()
 data, err := echo.Tar(res)
 require.NoError(t, err)
 file, err := client.Upload(context.Background(), codersdk.ContentTypeTar, data)
diff --git a/coderd/database/databasefake/databasefake.go b/coderd/database/databasefake/databasefake.go
 "github.com/lib/pq"
 "golang.org/x/exp/maps"
 "golang.org/x/exp/slices"
 "golang.org/x/xerrors"

 "github.com/coder/coder/coderd/database"
 "github.com/coder/coder/coderd/rbac"
 tpl.Icon = arg.Icon
 tpl.MaxTtl = arg.MaxTtl
 tpl.MinAutostartInterval = arg.MinAutostartInterval
 tpl.IsPrivate = arg.IsPrivate
 q.templates[idx] = tpl
 return tpl, nil
 }
 return templates, nil
 }

 func (q *fakeQuerier) UpdateTemplateUserACLByID(_ context.Context, id uuid.UUID, acl database.UserACL) error {
 q.mutex.RLock()
 defer q.mutex.RUnlock()

 for i, t := range q.templates {
 if t.ID == id {
 t = t.SetUserACL(acl)
 q.templates[i] = t
 return nil
 }
 }
 return sql.ErrNoRows
 }

 func (q *fakeQuerier) GetTemplateUserRoles(_ context.Context, id uuid.UUID) ([]database.TemplateUser, error) {
 q.mutex.RLock()
 defer q.mutex.RUnlock()

 var template database.Template
 for _, t := range q.templates {
 if t.ID == id {
 template = t
 break
 }
 }

 if template.ID == uuid.Nil {
 return nil, sql.ErrNoRows
 }

 acl := template.UserACL()

 users := make([]database.TemplateUser, 0, len(acl))
 for k, v := range acl {
 user, err := q.GetUserByID(context.Background(), uuid.MustParse(k))
 if err != nil && xerrors.Is(err, sql.ErrNoRows) {
 return nil, xerrors.Errorf("get user by ID: %w", err)
 }
 // We don't delete users from the map if they
 // get deleted so just skip.
 if xerrors.Is(err, sql.ErrNoRows) {
 continue
 }

 users = append(users, database.TemplateUser{
 User: user,
 Role: v,
 })
 }

 return users, nil
 }

 func (q *fakeQuerier) GetOrganizationMemberByUserID(_ context.Context, arg database.GetOrganizationMemberByUserIDParams) (database.OrganizationMember, error) {
 q.mutex.RLock()
 defer q.mutex.RUnlock()
 MaxTtl:               arg.MaxTtl,
 MinAutostartInterval: arg.MinAutostartInterval,
 CreatedBy:            arg.CreatedBy,
 IsPrivate:            arg.IsPrivate,
 }
 template = template.SetUserACL(database.UserACL{})
 q.templates = append(q.templates, template)
 return template, nil
 }
diff --git a/coderd/database/db.go b/coderd/database/db.go
 "database/sql"
 "errors"

 "github.com/jmoiron/sqlx"
 "golang.org/x/xerrors"
 )

 PrepareContext(context.Context, string) (*sql.Stmt, error)
 QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
 QueryRowContext(context.Context, string, ...interface{}) *sql.Row
 SelectContext(ctx context.Context, dest interface{}, query string, args ...interface{}) error
 GetContext(ctx context.Context, dest interface{}, query string, args ...interface{}) error
 }

 // New creates a new database store using a SQL database connection.
 func New(sdb *sql.DB) Store {
 dbx := sqlx.NewDb(sdb, "postgres")
 return &sqlQuerier{
 db:sdb,
 sdb:sdb,
 db:dbx,
 sdb:dbx,
 }
 }

 // queries encompasses both are sqlc generated
 // queries and our custom queries.
 type querier interface {
 sqlcQuerier
 customQuerier
 }

 type sqlQuerier struct {
 sdb *sql.DB
 sdb *sqlx.DB
 db  DBTX
 }

 // InTx performs database operations inside a transaction.
 func (q *sqlQuerier) InTx(function func(Store) error) error {
 if _, ok := q.db.(*sql.Tx); ok {
 if _, ok := q.db.(*sqlx.Tx); ok {
 // If the current inner "db" is already a transaction, we just reuse it.
 // We do not need to handle commit/rollback as the outer tx will handle
 // that.
 return nil
 }

 transaction, err := q.sdb.Begin()
 transaction, err := q.sdb.BeginTxx(context.Background(), nil)
 if err != nil {
 return xerrors.Errorf("begin transaction: %w", err)
 }
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/generate.sh b/coderd/database/generate.sh
 rm -f queries/*.go

 # Fix struct/interface names.
 gofmt -w -r 'Querier ->querier' -- *.go
 gofmt -w -r 'Querier ->sqlcQuerier' -- *.go
 gofmt -w -r 'Queries -> sqlQuerier' -- *.go

 # Ensure correct imports exist. Modules must all be downloaded so we get correct
diff --git a/coderd/database/migrations/000051_template_acl.down.sql b/coderd/database/migrations/000051_template_acl.down.sql
 BEGIN;

 ALTER TABLE templates DROP COLUMN user_acl;
 ALTER TABLE templates DROP COLUMN is_private;
 DROP TYPE template_role;

 COMMIT;
diff --git a/coderd/database/migrations/000051_template_acl.up.sql b/coderd/database/migrations/000051_template_acl.up.sql
 BEGIN;

 ALTER TABLE templates ADD COLUMN user_acl jsonb NOT NULL default '{}';
 ALTER TABLE templates ADD COLUMN is_private boolean NOT NULL default 'false';

 CREATE TYPE template_role AS ENUM (
 'read',
 'write',
 'admin'
 );

 COMMIT;
diff --git a/coderd/database/modelmethods.go b/coderd/database/modelmethods.go
 package database

 import (
 "encoding/json"
 "fmt"

 "github.com/coder/coder/coderd/rbac"
 )

 // UserACL is a map of user_ids to permissions.
 type UserACL map[string]TemplateRole

 func (u UserACL) Actions() map[string][]rbac.Action {
 aclRBAC := make(map[string][]rbac.Action, len(u))
 for k, v := range u {
 aclRBAC[k] = templateRoleToActions(v)
 }

 return aclRBAC
 }

 func (t Template) UserACL() UserACL {
 var acl UserACL
 if len(t.userACL) == 0 {
 return acl
 }

 err := json.Unmarshal(t.userACL, &acl)
 if err != nil {
 panic(fmt.Sprintf("failed to unmarshal template.userACL: %v", err.Error()))
 }

 return acl
 }

 func (t Template) SetUserACL(acl UserACL) Template {
 raw, err := json.Marshal(acl)
 if err != nil {
 panic(fmt.Sprintf("marshal user acl: %v", err))
 }

 t.userACL = raw
 return t
 }

 func templateRoleToActions(t TemplateRole) []rbac.Action {
 switch t {
 case TemplateRoleRead:
 return []rbac.Action{rbac.ActionRead}
 case TemplateRoleWrite:
 return []rbac.Action{rbac.ActionRead, rbac.ActionUpdate}
 case TemplateRoleAdmin:
 // TODO: Why does rbac.Wildcard not work here?
 return []rbac.Action{rbac.ActionRead, rbac.ActionUpdate, rbac.ActionCreate, rbac.ActionDelete}
 }
 return nil
 }

 func (s APIKeyScope) ToRBAC() rbac.Scope {
 switch s {
 case APIKeyScopeAll:
 }

 func (t Template) RBACObject() rbac.Object {
 return rbac.ResourceTemplate.InOrg(t.OrganizationID)
 obj := rbac.ResourceTemplate
 if t.IsPrivate {
 obj = rbac.ResourceTemplatePrivate
 }
 return obj.InOrg(t.OrganizationID).WithACLUserList(t.UserACL().Actions())
 }

 func (tTemplateVersion) RBACObject() rbac.Object {
 func (TemplateVersion) RBACObject(template Template) rbac.Object {
 // Just use the parent template resource for controlling versions
 returnrbac.ResourceTemplate.InOrg(t.OrganizationID)
 returntemplate.RBACObject()
 }

 func (w Workspace) RBACObject() rbac.Object {
diff --git a/coderd/database/modelqueries.go b/coderd/database/modelqueries.go
 package database

 import (
 "context"
 "encoding/json"

 "github.com/google/uuid"
 "golang.org/x/xerrors"
 )

 // customQuerier encompasses all non-generated queries.
 // It provides a flexible way to write queries for cases
 // where sqlc proves inadequate.
 type customQuerier interface {
 templateQuerier
 }

 type templateQuerier interface {
 UpdateTemplateUserACLByID(ctx context.Context, id uuid.UUID, acl UserACL) error
 GetTemplateUserRoles(ctx context.Context, id uuid.UUID) ([]TemplateUser, error)
 }

 type TemplateUser struct {
 User
 Role TemplateRole `db:"role"`
 }

 func (q *sqlQuerier) UpdateTemplateUserACLByID(ctx context.Context, id uuid.UUID, acl UserACL) error {
 raw, err := json.Marshal(acl)
 if err != nil {
 return xerrors.Errorf("marshal user acl: %w", err)
 }

 const query = `
 UPDATE
 templates
 SET
 user_acl = $2
 WHERE
 id = $1`

 _, err = q.db.ExecContext(ctx, query, id.String(), raw)
 if err != nil {
 return xerrors.Errorf("update user acl: %w", err)
 }

 return nil
 }

 func (q *sqlQuerier) GetTemplateUserRoles(ctx context.Context, id uuid.UUID) ([]TemplateUser, error) {
 const query = `
 SELECT
 perms.value as role, users.*
 FROM
 users
 JOIN
 (
 SELECT
 *
 FROM
 jsonb_each_text(
 (
 SELECT
 templates.user_acl
 FROM
 templates
 WHERE
 id = $1
 )
 )
 ) AS perms
 ON
 users.id::text = perms.key;
 `

 var tus []TemplateUser
 err := q.db.SelectContext(ctx, &tus, query, id.String())
 if err != nil {
 return nil, xerrors.Errorf("select context: %w", err)
 }

 return tus, nil
 }
Original file line number	Diff line number	Diff line change
Expand Up		@@ -320,6 +320,7 @@ func New(options Options) API {
		r.Get("/", api.template)
		r.Delete("/", api.deleteTemplate)
		r.Patch("/", api.patchTemplateMeta)
		r.Get("/user-roles", api.templateUserRoles)
		r.Route("/versions", func(r chi.Router) {
		r.Get("/", api.templateVersionsByTemplate)
		r.Patch("/", api.patchActiveTemplateVersion)
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -394,6 +394,7 @@ func createAnotherUserRetry(t testing.T, client codersdk.Client, organizationI
		// with the responses provided. It uses the "echo" provisioner for compatibility
		// with testing.
		func CreateTemplateVersion(t testing.T, client codersdk.Client, organizationID uuid.UUID, res *echo.Responses) codersdk.TemplateVersion {
		t.Helper()
		data, err := echo.Tar(res)
		require.NoError(t, err)
		file, err := client.Upload(context.Background(), codersdk.ContentTypeTar, data)
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -12,6 +12,7 @@ import (
		"github.com/lib/pq"
		"golang.org/x/exp/maps"
		"golang.org/x/exp/slices"
		"golang.org/x/xerrors"

		"github.com/coder/coder/coderd/database"
		"github.com/coder/coder/coderd/rbac"
Expand DownExpand Up		@@ -997,6 +998,7 @@ func (q *fakeQuerier) UpdateTemplateMetaByID(_ context.Context, arg database.Upd
		tpl.Icon = arg.Icon
		tpl.MaxTtl = arg.MaxTtl
		tpl.MinAutostartInterval = arg.MinAutostartInterval
		tpl.IsPrivate = arg.IsPrivate
		q.templates[idx] = tpl
		return tpl, nil
		}
Expand DownExpand Up		@@ -1229,6 +1231,59 @@ func (q *fakeQuerier) GetTemplates(_ context.Context) ([]database.Template, erro
		return templates, nil
		}

		func (q *fakeQuerier) UpdateTemplateUserACLByID(_ context.Context, id uuid.UUID, acl database.UserACL) error {
		q.mutex.RLock()
		defer q.mutex.RUnlock()

		for i, t := range q.templates {
		if t.ID == id {
		t = t.SetUserACL(acl)
		q.templates[i] = t
		return nil
		}
		}
		return sql.ErrNoRows
		}

		func (q *fakeQuerier) GetTemplateUserRoles(_ context.Context, id uuid.UUID) ([]database.TemplateUser, error) {
		q.mutex.RLock()
		defer q.mutex.RUnlock()

		var template database.Template
		for _, t := range q.templates {
		if t.ID == id {
		template = t
		break
		}
		}

		if template.ID == uuid.Nil {
		return nil, sql.ErrNoRows
		}

		acl := template.UserACL()

		users := make([]database.TemplateUser, 0, len(acl))
		for k, v := range acl {
		user, err := q.GetUserByID(context.Background(), uuid.MustParse(k))
		if err != nil && xerrors.Is(err, sql.ErrNoRows) {
		return nil, xerrors.Errorf("get user by ID: %w", err)
		}
		// We don't delete users from the map if they
		// get deleted so just skip.
		if xerrors.Is(err, sql.ErrNoRows) {
		continue
		}

		users = append(users, database.TemplateUser{
		User: user,
		Role: v,
		})
		}

		return users, nil
		}

		func (q *fakeQuerier) GetOrganizationMemberByUserID(_ context.Context, arg database.GetOrganizationMemberByUserIDParams) (database.OrganizationMember, error) {
		q.mutex.RLock()
		defer q.mutex.RUnlock()
Expand DownExpand Up		@@ -1708,7 +1763,9 @@ func (q *fakeQuerier) InsertTemplate(_ context.Context, arg database.InsertTempl
		MaxTtl: arg.MaxTtl,
		MinAutostartInterval: arg.MinAutostartInterval,
		CreatedBy: arg.CreatedBy,
		IsPrivate: arg.IsPrivate,
		}
		template = template.SetUserACL(database.UserACL{})
		q.templates = append(q.templates, template)
		return template, nil
		}
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -13,6 +13,7 @@ import (
		"database/sql"
		"errors"

		"github.com/jmoiron/sqlx"
		"golang.org/x/xerrors"
		)

Expand All		@@ -30,24 +31,34 @@ type DBTX interface {
		PrepareContext(context.Context, string) (*sql.Stmt, error)
		QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
		QueryRowContext(context.Context, string, ...interface{}) *sql.Row
		SelectContext(ctx context.Context, dest interface{}, query string, args ...interface{}) error
		GetContext(ctx context.Context, dest interface{}, query string, args ...interface{}) error
		}

		// New creates a new database store using a SQL database connection.
		func New(sdb *sql.DB) Store {
		dbx := sqlx.NewDb(sdb, "postgres")
		return &sqlQuerier{
		db:sdb,
		sdb:sdb,
		db:dbx,
		sdb:dbx,
		}
		}

		// queries encompasses both are sqlc generated
		// queries and our custom queries.
		type querier interface {
		sqlcQuerier
		customQuerier
		}

		type sqlQuerier struct {
		sdb *sql.DB
		sdb *sqlx.DB
		db DBTX
		}

		// InTx performs database operations inside a transaction.
		func (q *sqlQuerier) InTx(function func(Store) error) error {
		if _, ok := q.db.(*sql.Tx); ok {
		if _, ok := q.db.(*sqlx.Tx); ok {
		// If the current inner "db" is already a transaction, we just reuse it.
		// We do not need to handle commit/rollback as the outer tx will handle
		// that.
Expand All		@@ -58,7 +69,7 @@ func (q *sqlQuerier) InTx(function func(Store) error) error {
		return nil
		}

		transaction, err := q.sdb.Begin()
		transaction, err := q.sdb.BeginTxx(context.Background(), nil)
		if err != nil {
		return xerrors.Errorf("begin transaction: %w", err)
		}
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -42,7 +42,7 @@ SCRIPT_DIR=$(dirname "${BASH_SOURCE[0]}")
		rm -f queries/*.go

		# Fix struct/interface names.
		gofmt -w -r 'Querier ->querier' -- *.go
		gofmt -w -r 'Querier ->sqlcQuerier' -- *.go
		gofmt -w -r 'Queries -> sqlQuerier' -- *.go

		# Ensure correct imports exist. Modules must all be downloaded so we get correct
Expand Down
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,7 @@
		BEGIN;

		ALTER TABLE templates DROP COLUMN user_acl;
		ALTER TABLE templates DROP COLUMN is_private;
		DROP TYPE template_role;

		COMMIT;
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,12 @@
		BEGIN;

		ALTER TABLE templates ADD COLUMN user_acl jsonb NOT NULL default '{}';
Copy link Member kylecarbsSep 19, 2022 Choose a reason for hiding this comment The reason will be displayed to describe this comment to others.Learn more. Why store this as a massive`jsonb` blob instead of a table? How do we plan on efficiently getting a list of all templates the user has access to? Copy link CollaboratorAuthor sreyaSep 20, 2022 Choose a reason for hiding this comment The reason will be displayed to describe this comment to others.Learn more. It's basically the same way everything else is done. You get a list of all the templates and then you filter it through the auth filter for the subset of templates that you have access to. Just wrote a test for thishere Copy link Member kylecarbsSep 20, 2022 Choose a reason for hiding this comment The reason will be displayed to describe this comment to others.Learn more. For workspaces, itkinda does this, but we still query by the owner so it's lessened significantly. I guess the use-case I have in mind is 10 templates with 10 users on each... for every HTTP request we'll load 100 entries into memory and check against them? That sounds like a lot of excess when we could (I don't see why not, but maybe there's a reason) have a table that just has the user ID indexed... Copy link CollaboratorAuthor sreyaSep 20, 2022 Choose a reason for hiding this comment The reason will be displayed to describe this comment to others.Learn more. The memory overhead is unfortunate but this way plays better with rego is the short answer. We can look into rewriting it if you think it's a showstopper, I'm not sold on`jsonb` but joining tables doesn't play well with sqlc either so the maintainability of the code might suffer as a result of trying to glue everything together.
		ALTER TABLE templates ADD COLUMN is_private boolean NOT NULL default 'false';
Copy link Member kylecarbsSep 20, 2022 Choose a reason for hiding this comment The reason will be displayed to describe this comment to others.Learn more. `is_` isn't necessary here sreya reacted with thumbs up emoji

		CREATE TYPE template_role AS ENUM (
		'read',
		'write',
		'admin'
		);

		COMMIT;
Original file line number	Diff line number	Diff line change
		@@ -1,9 +1,61 @@
		package database

		import (
		"encoding/json"
		"fmt"

		"github.com/coder/coder/coderd/rbac"
		)

		// UserACL is a map of user_ids to permissions.
		type UserACL map[string]TemplateRole

		func (u UserACL) Actions() map[string][]rbac.Action {
		aclRBAC := make(map[string][]rbac.Action, len(u))
		for k, v := range u {
		aclRBAC[k] = templateRoleToActions(v)
		}

		return aclRBAC
		}

		func (t Template) UserACL() UserACL {
		var acl UserACL
		if len(t.userACL) == 0 {
		return acl
		}

		err := json.Unmarshal(t.userACL, &acl)
		if err != nil {
		panic(fmt.Sprintf("failed to unmarshal template.userACL: %v", err.Error()))
		}

		return acl
		}

		func (t Template) SetUserACL(acl UserACL) Template {
		raw, err := json.Marshal(acl)
		if err != nil {
		panic(fmt.Sprintf("marshal user acl: %v", err))
		}

		t.userACL = raw
		return t
		}

		func templateRoleToActions(t TemplateRole) []rbac.Action {
		switch t {
		case TemplateRoleRead:
		return []rbac.Action{rbac.ActionRead}
		case TemplateRoleWrite:
		return []rbac.Action{rbac.ActionRead, rbac.ActionUpdate}
		case TemplateRoleAdmin:
		// TODO: Why does rbac.Wildcard not work here?
		return []rbac.Action{rbac.ActionRead, rbac.ActionUpdate, rbac.ActionCreate, rbac.ActionDelete}
		}
		return nil
		}

		func (s APIKeyScope) ToRBAC() rbac.Scope {
		switch s {
		case APIKeyScopeAll:
Expand All		@@ -16,12 +68,16 @@ func (s APIKeyScope) ToRBAC() rbac.Scope {
		}

		func (t Template) RBACObject() rbac.Object {
		return rbac.ResourceTemplate.InOrg(t.OrganizationID)
		obj := rbac.ResourceTemplate
		if t.IsPrivate {
		obj = rbac.ResourceTemplatePrivate
		}
		return obj.InOrg(t.OrganizationID).WithACLUserList(t.UserACL().Actions())
		}

		func (tTemplateVersion) RBACObject() rbac.Object {
		func (TemplateVersion) RBACObject(template Template) rbac.Object {
		// Just use the parent template resource for controlling versions
		returnrbac.ResourceTemplate.InOrg(t.OrganizationID)
		returntemplate.RBACObject()
		}

		func (w Workspace) RBACObject() rbac.Object {
Expand Down
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,83 @@
		package database

		import (
		"context"
		"encoding/json"

		"github.com/google/uuid"
		"golang.org/x/xerrors"
		)

		// customQuerier encompasses all non-generated queries.
		// It provides a flexible way to write queries for cases
		// where sqlc proves inadequate.
		type customQuerier interface {
		templateQuerier
		}

		type templateQuerier interface {
		UpdateTemplateUserACLByID(ctx context.Context, id uuid.UUID, acl UserACL) error
		GetTemplateUserRoles(ctx context.Context, id uuid.UUID) ([]TemplateUser, error)
		}

		type TemplateUser struct {
		User
		Role TemplateRole `db:"role"`
		}

		func (q *sqlQuerier) UpdateTemplateUserACLByID(ctx context.Context, id uuid.UUID, acl UserACL) error {
		raw, err := json.Marshal(acl)
		if err != nil {
		return xerrors.Errorf("marshal user acl: %w", err)
		}

		const query = `
		UPDATE
		templates
		SET
		user_acl = $2
		WHERE
		id = $1`
Comment on lines +35 to +40 Copy link Member kylecarbsSep 20, 2022 Choose a reason for hiding this comment The reason will be displayed to describe this comment to others.Learn more. Why can't this be done with`sqlc` instead? Copy link CollaboratorAuthor sreyaSep 20, 2022 Choose a reason for hiding this comment The reason will be displayed to describe this comment to others.Learn more. because it exposes a`json.RawMessage` through the API. I wanted to preserve type safety as much as possible since we can't enforce the jsonb structure in the DB

		_, err = q.db.ExecContext(ctx, query, id.String(), raw)
		if err != nil {
		return xerrors.Errorf("update user acl: %w", err)
		}

		return nil
		}

		func (q *sqlQuerier) GetTemplateUserRoles(ctx context.Context, id uuid.UUID) ([]TemplateUser, error) {
		const query = `
Copy link Member kylecarbsSep 20, 2022 Choose a reason for hiding this comment The reason will be displayed to describe this comment to others.Learn more. I'm not convinced we should escape sqlc here... this function is only used once, so why not just do the struct conversion where it's queried from instead? Copy link CollaboratorAuthor sreyaSep 20, 2022• edited Loading Choose a reason for hiding this comment The reason will be displayed to describe this comment to others.Learn more. there's unfortunately multiple problems when I tried to write this with`sqlc`. One is that it didn't recognize the intermediate`value` column in the subquery. When I tried to jank around that the resulting return type was wildly different than what I tried to express 😞
		SELECT
		perms.value as role, users.*
		FROM
		users
		JOIN
		(
		SELECT
		*
		FROM
		jsonb_each_text(
		(
		SELECT
		templates.user_acl
		FROM
		templates
		WHERE
		id = $1
		)
		)
		) AS perms
		ON
		users.id::text = perms.key;
		`

		var tus []TemplateUser
		err := q.db.SelectContext(ctx, &tus, query, id.String())
		if err != nil {
		return nil, xerrors.Errorf("select context: %w", err)
		}

		return tus, nil
		}