feat(coderd): add dbcrypt package

This commit builds upon the previous work in #7959: - Moved dbcrypt package to enterprise/dbcrypt - Modified original dbcrypt behaviour to not delete un-decryptable rows. - Added a table dbcrypt_sentinel used to determine database encryption status. - Added support for multiple encryption keys in dbcrypt. NOTE: This is part 1 of a 2-part PR. This PR focuses mainly on the dbcrypt and database packages. A separate PR will add the required plumbing to integrate this into enterprise/coderd properly. Co-authored-by: Kyle Carberry <kyle@coder.com>
coder · johnstcn · Aug 30, 2023 · Aug 30, 2023 · 8d5701a2ba5ad2b96e604775b41b95d3835f7751
commit 8d5701a2ba5ad2b96e604775b41b95d3835f7751
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -828,6 +828,13 @@ func (q *querier) GetAuthorizationUserRoles(ctx context.Context, userID uuid.UUI
 	return q.db.GetAuthorizationUserRoles(ctx, userID)
 }
 
+func (q *querier) GetDBCryptSentinelValue(ctx context.Context) (string, error) {
+	if err := q.authorizeContext(ctx, rbac.ActionRead, rbac.ResourceSystem); err != nil {
+		return "", err
+	}
+	return q.db.GetDBCryptSentinelValue(ctx)
+}
+
 func (q *querier) GetDERPMeshKey(ctx context.Context) (string, error) {
 	if err := q.authorizeContext(ctx, rbac.ActionRead, rbac.ResourceSystem); err != nil {
 		return "", err
@@ -904,6 +911,13 @@ func (q *querier) GetGitAuthLink(ctx context.Context, arg database.GetGitAuthLin
 	return fetch(q.log, q.auth, q.db.GetGitAuthLink)(ctx, arg)
 }
 
+func (q *querier) GetGitAuthLinksByUserID(ctx context.Context, userID uuid.UUID) ([]database.GitAuthLink, error) {
+	if err := q.authorizeContext(ctx, rbac.ActionRead, rbac.ResourceSystem); err != nil {
+		return nil, err
+	}
+	return q.db.GetGitAuthLinksByUserID(ctx, userID)
+}
+
 func (q *querier) GetGitSSHKey(ctx context.Context, userID uuid.UUID) (database.GitSSHKey, error) {
 	return fetch(q.log, q.auth, q.db.GetGitSSHKey)(ctx, userID)
 }
@@ -1472,6 +1486,13 @@ func (q *querier) GetUserLinkByUserIDLoginType(ctx context.Context, arg database
 	return q.db.GetUserLinkByUserIDLoginType(ctx, arg)
 }
 
+func (q *querier) GetUserLinksByUserID(ctx context.Context, userID uuid.UUID) ([]database.UserLink, error) {
+	if err := q.authorizeContext(ctx, rbac.ActionRead, rbac.ResourceSystem); err != nil {
+		return nil, err
+	}
+	return q.db.GetUserLinksByUserID(ctx, userID)
+}
+
 func (q *querier) GetUsers(ctx context.Context, arg database.GetUsersParams) ([]database.GetUsersRow, error) {
 	// This does the filtering in SQL.
 	prep, err := prepareSQLFilter(ctx, q.auth, rbac.ActionRead, rbac.ResourceUser.Type)
@@ -2134,6 +2155,13 @@ func (q *querier) RegisterWorkspaceProxy(ctx context.Context, arg database.Regis
 	return updateWithReturn(q.log, q.auth, fetch, q.db.RegisterWorkspaceProxy)(ctx, arg)
 }
 
+func (q *querier) SetDBCryptSentinelValue(ctx context.Context, value string) error {
+	if err := q.authorizeContext(ctx, rbac.ActionUpdate, rbac.ResourceSystem); err != nil {
+		return err
+	}
+	return q.db.SetDBCryptSentinelValue(ctx, value)
+}
+
 func (q *querier) TryAcquireLock(ctx context.Context, id int64) (bool, error) {
 	return q.db.TryAcquireLock(ctx, id)
 }

diff --git a/coderd/database/dbfake/dbfake.go b/coderd/database/dbfake/dbfake.go
@@ -44,6 +44,7 @@ func New() database.Store {
 			organizationMembers:       make([]database.OrganizationMember, 0),
 			organizations:             make([]database.Organization, 0),
 			users:                     make([]database.User, 0),
+			dbcryptSentinelValue:      nil,
 			gitAuthLinks:              make([]database.GitAuthLink, 0),
 			groups:                    make([]database.Group, 0),
 			groupMembers:              make([]database.GroupMember, 0),
@@ -116,6 +117,7 @@ type data struct {
 	// New tables
 	workspaceAgentStats           []database.WorkspaceAgentStat
 	auditLogs                     []database.AuditLog
+	dbcryptSentinelValue          *string
 	files                         []database.File
 	gitAuthLinks                  []database.GitAuthLink
 	gitSSHKey                     []database.GitSSHKey
@@ -1150,6 +1152,15 @@ func (q *FakeQuerier) GetAuthorizationUserRoles(_ context.Context, userID uuid.U
 	}, nil
 }
 
+func (q *FakeQuerier) GetDBCryptSentinelValue(_ context.Context) (string, error) {
+	q.mutex.RLock()
+	defer q.mutex.RUnlock()
+	if q.dbcryptSentinelValue == nil {
+		return "", sql.ErrNoRows
+	}
+	return *q.dbcryptSentinelValue, nil
+}
+
 func (q *FakeQuerier) GetDERPMeshKey(_ context.Context) (string, error) {
 	q.mutex.RLock()
 	defer q.mutex.RUnlock()
@@ -1392,6 +1403,18 @@ func (q *FakeQuerier) GetGitAuthLink(_ context.Context, arg database.GetGitAuthL
 	return database.GitAuthLink{}, sql.ErrNoRows
 }
 
+func (q *FakeQuerier) GetGitAuthLinksByUserID(_ context.Context, userID uuid.UUID) ([]database.GitAuthLink, error) {
+	q.mutex.RLock()
+	defer q.mutex.RUnlock()
+	gals := make([]database.GitAuthLink, 0)
+	for _, gal := range q.gitAuthLinks {
+		if gal.UserID == userID {
+			gals = append(gals, gal)
+		}
+	}
+	return gals, nil
+}
+
 func (q *FakeQuerier) GetGitSSHKey(_ context.Context, userID uuid.UUID) (database.GitSSHKey, error) {
 	q.mutex.RLock()
 	defer q.mutex.RUnlock()
@@ -2832,6 +2855,18 @@ func (q *FakeQuerier) GetUserLinkByUserIDLoginType(_ context.Context, params dat
 	return database.UserLink{}, sql.ErrNoRows
 }
 
+func (q *FakeQuerier) GetUserLinksByUserID(_ context.Context, userID uuid.UUID) ([]database.UserLink, error) {
+	q.mutex.RLock()
+	defer q.mutex.RUnlock()
+	uls := make([]database.UserLink, 0)
+	for _, ul := range q.userLinks {
+		if ul.UserID == userID {
+			uls = append(uls, ul)
+		}
+	}
+	return uls, nil
+}
+
 func (q *FakeQuerier) GetUsers(_ context.Context, params database.GetUsersParams) ([]database.GetUsersRow, error) {
 	if err := validateDatabaseType(params); err != nil {
 		return nil, err
@@ -4791,6 +4826,13 @@ func (q *FakeQuerier) RegisterWorkspaceProxy(_ context.Context, arg database.Reg
 	return database.WorkspaceProxy{}, sql.ErrNoRows
 }
 
+func (q *FakeQuerier) SetDBCryptSentinelValue(_ context.Context, value string) error {
+	q.mutex.Lock()
+	defer q.mutex.Unlock()
+	q.dbcryptSentinelValue = &value
+	return nil
+}
+
 func (*FakeQuerier) TryAcquireLock(_ context.Context, _ int64) (bool, error) {
 	return false, xerrors.New("TryAcquireLock must only be called within a transaction")
 }

diff --git a/coderd/database/dbgen/dbgen.go b/coderd/database/dbgen/dbgen.go
@@ -473,7 +473,7 @@ func UserLink(t testing.TB, db database.Store, orig database.UserLink) database.
 		LoginType:         takeFirst(orig.LoginType, database.LoginTypeGithub),
 		LinkedID:          takeFirst(orig.LinkedID),
 		OAuthAccessToken:  takeFirst(orig.OAuthAccessToken, uuid.NewString()),
-		OAuthRefreshToken: takeFirst(orig.OAuthAccessToken, uuid.NewString()),
+		OAuthRefreshToken: takeFirst(orig.OAuthRefreshToken, uuid.NewString()),
 		OAuthExpiry:       takeFirst(orig.OAuthExpiry, database.Now().Add(time.Hour*24)),
 	})
 
@@ -486,7 +486,7 @@ func GitAuthLink(t testing.TB, db database.Store, orig database.GitAuthLink) dat
 		ProviderID:        takeFirst(orig.ProviderID, uuid.New().String()),
 		UserID:            takeFirst(orig.UserID, uuid.New()),
 		OAuthAccessToken:  takeFirst(orig.OAuthAccessToken, uuid.NewString()),
-		OAuthRefreshToken: takeFirst(orig.OAuthAccessToken, uuid.NewString()),
+		OAuthRefreshToken: takeFirst(orig.OAuthRefreshToken, uuid.NewString()),
 		OAuthExpiry:       takeFirst(orig.OAuthExpiry, database.Now().Add(time.Hour*24)),
 		CreatedAt:         takeFirst(orig.CreatedAt, database.Now()),
 		UpdatedAt:         takeFirst(orig.UpdatedAt, database.Now()),

diff --git a/coderd/database/dbmetrics/dbmetrics.go b/coderd/database/dbmetrics/dbmetrics.go
diff --git a/coderd/database/dbmock/dbmock.go b/coderd/database/dbmock/dbmock.go
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000153_dbcrypt_sentinel_value.down.sql b/coderd/database/migrations/000153_dbcrypt_sentinel_value.down.sql
@@ -0,0 +1 @@
+DROP TABLE IF EXISTS dbcrypt_sentinel;
diff --git a/coderd/database/migrations/000153_dbcrypt_sentinel_value.up.sql b/coderd/database/migrations/000153_dbcrypt_sentinel_value.up.sql
@@ -0,0 +1,8 @@
+CREATE TABLE IF NOT EXISTS dbcrypt_sentinel (
+		only_one integer GENERATED ALWAYS AS (1) STORED UNIQUE,
+		val text NOT NULL DEFAULT ''::text
+);
+
+COMMENT ON TABLE dbcrypt_sentinel IS 'A table used to determine if the database is encrypted';
+COMMENT ON COLUMN dbcrypt_sentinel.only_one IS 'Ensures that only one row exists in the table.';
+COMMENT ON COLUMN dbcrypt_sentinel.val IS 'Used to determine if the database is encrypted.';
diff --git a/coderd/database/migrations/migrate_test.go b/coderd/database/migrations/migrate_test.go
@@ -266,6 +266,7 @@ func TestMigrateUpWithFixtures(t *testing.T) {
 		"template_version_parameters",
 		"workspace_build_parameters",
 		"template_version_variables",
+		"dbcrypt_sentinel", // having zero rows is a valid state for this table
 	}
 	s := &tableStats{s: make(map[string]int)}
 

diff --git a/coderd/database/models.go b/coderd/database/models.go
diff --git a/coderd/database/querier.go b/coderd/database/querier.go