coder · Emyrk · Oct 4, 2022 · Sep 27, 2022 · Sep 27, 2022 · Sep 27, 2022
diff --git a/coderd/authorize.go b/coderd/authorize.go
@@ -13,6 +13,9 @@ import (
 	"github.com/coder/coder/codersdk"
 )
 
+// AuthorizeFilter takes a list of objects and returns the filtered list of
+// objects that the user is authorized to perform the given action on.
+// This is faster than calling Authorize() on each object.
 func AuthorizeFilter[O rbac.Objecter](h *HTTPAuthorizer, r *http.Request, action rbac.Action, objects []O) ([]O, error) {
 	roles := httpmw.UserAuthorization(r)
 	objects, err := rbac.Filter(r.Context(), h.Authorizer, roles.ID.String(), roles.Roles, roles.Scope.ToRBAC(), action, objects)
@@ -85,6 +88,26 @@ func (h *HTTPAuthorizer) Authorize(r *http.Request, action rbac.Action, object r
 	return true
 }
 
+// AuthorizeSQLFilter returns an authorization filter that can used in a
+// SQL 'WHERE' clause. If the filter is used, the resulting rows returned
+// from postgres are already authorized, and the caller does not need to
+// call 'Authorize()' on the returned objects.
+// Note the authorization is only for the given action and object type.
+func (h *HTTPAuthorizer) AuthorizeSQLFilter(r *http.Request, action rbac.Action, objectType string) (rbac.AuthorizeFilter, error) {
+	roles := httpmw.UserAuthorization(r)
+	prepared, err := h.Authorizer.PrepareByRoleName(r.Context(), roles.ID.String(), roles.Roles, roles.Scope.ToRBAC(), action, objectType)
+	if err != nil {
+		return nil, xerrors.Errorf("prepare filter: %w", err)
+	}
+
+	filter, err := prepared.Compile()
+	if err != nil {
+		return nil, xerrors.Errorf("compile filter: %w", err)
+	}
+
+	return filter, nil
+}
+
 // checkAuthorization returns if the current API key can use the given
 // permissions, factoring in the current user's roles and the API key scopes.
 func (api *API) checkAuthorization(rw http.ResponseWriter, r *http.Request) {

diff --git a/coderd/coderdtest/authorize.go b/coderd/coderdtest/authorize.go
@@ -128,11 +128,6 @@ func AGPLRoutes(a *AuthTester) (map[string]string, map[string]RouteCheck) {
 			AssertAction: rbac.ActionCreate,
 			AssertObject: workspaceExecObj,
 		},
-		"GET:/api/v2/workspaces/": {
-			StatusCode:   http.StatusOK,
-			AssertAction: rbac.ActionRead,
-			AssertObject: workspaceRBACObj,
-		},
 		"GET:/api/v2/organizations/{organization}/templates": {
 			StatusCode:   http.StatusOK,
 			AssertAction: rbac.ActionRead,
@@ -250,6 +245,9 @@ func AGPLRoutes(a *AuthTester) (map[string]string, map[string]RouteCheck) {
 		"PUT:/api/v2/organizations/{organization}/members/{user}/roles": {NoAuthorize: true},
 		"POST:/api/v2/workspaces/{workspace}/builds":                    {StatusCode: http.StatusBadRequest, NoAuthorize: true},
 		"POST:/api/v2/organizations/{organization}/templateversions":    {StatusCode: http.StatusBadRequest, NoAuthorize: true},
+
+		// Endpoints that use the SQLQuery filter.
+		"GET:/api/v2/workspaces/": {StatusCode: http.StatusOK, NoAuthorize: true},
 	}
 
 	// Routes like proxy routes support all HTTP methods. A helper func to expand
@@ -517,6 +515,12 @@ type RecordingAuthorizer struct {
 
 var _ rbac.Authorizer = (*RecordingAuthorizer)(nil)
 
+// ByRoleNameSQL does not record the call. This matches the postgres behavior
+// of not calling Authorize()
+func (r *RecordingAuthorizer) ByRoleNameSQL(_ context.Context, _ string, _ []string, _ rbac.Scope, _ rbac.Action, _ rbac.Object) error {
+	return r.AlwaysReturn
+}
+
 func (r *RecordingAuthorizer) ByRoleName(_ context.Context, subjectID string, roleNames []string, scope rbac.Scope, action rbac.Action, object rbac.Object) error {
 	r.Called = &authCall{
 		SubjectID: subjectID,
@@ -530,11 +534,12 @@ func (r *RecordingAuthorizer) ByRoleName(_ context.Context, subjectID string, ro
 
 func (r *RecordingAuthorizer) PrepareByRoleName(_ context.Context, subjectID string, roles []string, scope rbac.Scope, action rbac.Action, _ string) (rbac.PreparedAuthorized, error) {
 	return &fakePreparedAuthorizer{
-		Original:  r,
-		SubjectID: subjectID,
-		Roles:     roles,
-		Scope:     scope,
-		Action:    action,
+		Original:           r,
+		SubjectID:          subjectID,
+		Roles:              roles,
+		Scope:              scope,
+		Action:             action,
+		HardCodedSQLString: "true",
 	}, nil
 }
 
@@ -543,13 +548,39 @@ func (r *RecordingAuthorizer) reset() {
 }
 
 type fakePreparedAuthorizer struct {
-	Original  *RecordingAuthorizer
-	SubjectID string
-	Roles     []string
-	Scope     rbac.Scope
-	Action    rbac.Action
+	Original            *RecordingAuthorizer
+	SubjectID           string
+	Roles               []string
+	Scope               rbac.Scope
+	Action              rbac.Action
+	HardCodedSQLString  string
+	HardCodedRegoString string
 }
 
 func (f *fakePreparedAuthorizer) Authorize(ctx context.Context, object rbac.Object) error {
 	return f.Original.ByRoleName(ctx, f.SubjectID, f.Roles, f.Scope, f.Action, object)
 }
+
+// Compile returns a compiled version of the authorizer that will work for
+// in memory databases. This fake version will not work against a SQL database.
+func (f *fakePreparedAuthorizer) Compile() (rbac.AuthorizeFilter, error) {
+	return f, nil
+}
+
+func (f *fakePreparedAuthorizer) Eval(object rbac.Object) bool {
+	return f.Original.ByRoleNameSQL(context.Background(), f.SubjectID, f.Roles, f.Scope, f.Action, object) == nil
+}
+
+func (f fakePreparedAuthorizer) RegoString() string {
+	if f.HardCodedRegoString != "" {
+		return f.HardCodedRegoString
+	}
+	panic("not implemented")
+}
+
+func (f fakePreparedAuthorizer) SQLString(_ rbac.SQLConfig) string {
+	if f.HardCodedSQLString != "" {
+		return f.HardCodedSQLString
+	}
+	panic("not implemented")
+}
diff --git a/coderd/database/custom_queries.go b/coderd/database/custom_queries.go
@@ -0,0 +1,61 @@
+package database
+
+import (
+	"context"
+	"fmt"
+
+	"github.com/lib/pq"
+	"golang.org/x/xerrors"
+
+	"github.com/coder/coder/coderd/rbac"
+)
+
+type customQuerier interface {
+	AuthorizedGetWorkspaces(ctx context.Context, arg GetWorkspacesParams, authorizedFilter rbac.AuthorizeFilter) ([]Workspace, error)
+}
+
+// AuthorizedGetWorkspaces returns all workspaces that the user is authorized to access.
+// This code is copied from `GetWorkspaces` and adds the authorized filter WHERE
+// clause.
+func (q *sqlQuerier) AuthorizedGetWorkspaces(ctx context.Context, arg GetWorkspacesParams, authorizedFilter rbac.AuthorizeFilter) ([]Workspace, error) {
+	query := fmt.Sprintf("%s AND %s", getWorkspaces, authorizedFilter.SQLString(rbac.DefaultConfig()))
+	rows, err := q.db.QueryContext(ctx, query,
+		arg.Deleted,
+		arg.OwnerID,
+		arg.OwnerUsername,
+		arg.TemplateName,
+		pq.Array(arg.TemplateIds),
+		arg.Name,
+	)
+	if err != nil {
+		return nil, xerrors.Errorf("get authorized workspaces: %w", err)
+	}
+	defer rows.Close()
+	var items []Workspace
+	for rows.Next() {
+		var i Workspace
+		if err := rows.Scan(
+			&i.ID,
+			&i.CreatedAt,
+			&i.UpdatedAt,
+			&i.OwnerID,
+			&i.OrganizationID,
+			&i.TemplateID,
+			&i.Deleted,
+			&i.Name,
+			&i.AutostartSchedule,
+			&i.Ttl,
+			&i.LastUsedAt,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
diff --git a/coderd/database/databasefake/databasefake.go b/coderd/database/databasefake/databasefake.go
@@ -520,7 +520,13 @@ func (q *fakeQuerier) GetAuthorizationUserRoles(_ context.Context, userID uuid.U
 	}, nil
 }
 
-func (q *fakeQuerier) GetWorkspaces(_ context.Context, arg database.GetWorkspacesParams) ([]database.Workspace, error) {
+func (q *fakeQuerier) GetWorkspaces(ctx context.Context, arg database.GetWorkspacesParams) ([]database.Workspace, error) {
+	// A nil auth filter means no auth filter.
+	workspaces, err := q.AuthorizedGetWorkspaces(ctx, arg, nil)
+	return workspaces, err
+}
+
+func (q *fakeQuerier) AuthorizedGetWorkspaces(_ context.Context, arg database.GetWorkspacesParams, authorizedFilter rbac.AuthorizeFilter) ([]database.Workspace, error) {
 	q.mutex.RLock()
 	defer q.mutex.RUnlock()
 
@@ -560,6 +566,11 @@ func (q *fakeQuerier) GetWorkspaces(_ context.Context, arg database.GetWorkspace
 				continue
 			}
 		}
+
+		// If the filter exists, ensure the object is authorized.
+		if authorizedFilter != nil && !authorizedFilter.Eval(workspace.RBACObject()) {
+			continue
+		}
 		workspaces = append(workspaces, workspace)
 	}
 

diff --git a/coderd/database/db.go b/coderd/database/db.go
@@ -20,6 +20,8 @@ import (
 // It extends the generated interface to add transaction support.
 type Store interface {
 	querier
+	// customQuerier contains custom queries that are not generated.
+	customQuerier
 
 	InTx(func(Store) error) error
 }

@@ -21,6 +21,7 @@ type Authorizer interface {
 
 type PreparedAuthorized interface {
 	Authorize(ctx context.Context, object Object) error
+	Compile() (AuthorizeFilter, error)
 }
 
 // Filter takes in a list of objects, and will filter the list removing all

@@ -781,6 +781,11 @@ func testAuthorize(t *testing.T, name string, subject subject, sets ...[]authTes
 					partialAuthz, err := authorizer.Prepare(ctx, subject.UserID, subject.Roles, subject.Scope, a, c.resource.Type)
 					require.NoError(t, err, "make prepared authorizer")
 
+					// Ensure the partial can compile to a SQL clause.
+					// This does not guarantee that the clause is valid SQL.
+					_, err = Compile(partialAuthz.partialQueries)
+					require.NoError(t, err, "compile prepared authorizer")
+
 					// Also check the rego policy can form a valid partial query result.
 					// This ensures we can convert the queries into SQL WHERE clauses in the future.
 					// If this function returns 'Support' sections, then we cannot convert the query into SQL.

@@ -28,6 +28,14 @@ type PartialAuthorizer struct {
 
 var _ PreparedAuthorized = (*PartialAuthorizer)(nil)
 
+func (pa *PartialAuthorizer) Compile() (AuthorizeFilter, error) {
+	filter, err := Compile(pa.partialQueries)
+	if err != nil {
+		return nil, xerrors.Errorf("compile: %w", err)
+	}
+	return filter, nil
+}
+
 func (pa *PartialAuthorizer) Authorize(ctx context.Context, object Object) error {
 	if pa.alwaysTrue {
 		return nil