8000 fix: add postgres triggers to remove deleted users from user_links by Emyrk · Pull Request #12117 · coder/coder · GitHub
[go: up one dir, main page]

Skip to content

fix: add postgres triggers to remove deleted users from user_links #12117

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 27 commits into from
Feb 20, 2024
Merged
Show file tree
Hide file tree
Changes from 1 commit
Commits
Show all changes
27 commits
Select commit Hold shift + click to select a range
File filter

Filter by extension

Filter by extension

Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
Prev Previous commit
Next Next commit
Force soft delete of users, do not allow un-delete
  • Loading branch information
Emyrk committed Feb 13, 2024
commit e0902a38319e213309edbe2d37868055b3e7dac9
26 changes: 4 additions & 22 deletions coderd/database/dbauthz/dbauthz.go
8000
Original file line number Diff line number Diff line change
Expand Up @@ -607,16 +607,6 @@ func (q *querier) SoftDeleteTemplateByID(ctx context.Context, id uuid.UUID) erro
return deleteQ(q.log, q.auth, q.db.GetTemplateByID, deleteF)(ctx, id)
}

func (q *querier) SoftDeleteUserByID(ctx context.Context, id uuid.UUID) error {
deleteF := func(ctx context.Context, id uuid.UUID) error {
return q.db.UpdateUserDeletedByID(ctx, database.UpdateUserDeletedByIDParams{
ID: id,
Deleted: true,
})
}
return deleteQ(q.log, q.auth, q.db.GetUserByID, deleteF)(ctx, id)
}

func (q *querier) SoftDeleteWorkspaceByID(ctx context.Context, id uuid.UUID) error {
return deleteQ(q.log, q.auth, q.db.GetWorkspaceByID, func(ctx context.Context, id uuid.UUID) error {
return q.db.UpdateWorkspaceDeletedByID(ctx, database.UpdateWorkspaceDeletedByIDParams{
Expand Down Expand Up @@ -2556,6 +2546,10 @@ func (q *querier) RevokeDBCryptKey(ctx context.Context, activeKeyDigest string)
return q.db.RevokeDBCryptKey(ctx, activeKeyDigest)
}

func (q *querier) SoftDeleteUserByID(ctx context.Context, id uuid.UUID) error {
return deleteQ(q.log, q.auth, q.db.GetUserByID, q.db.SoftDeleteUserByID)(ctx, id)
}

func (q *querier) TryAcquireLock(ctx context.Context, id int64) (bool, error) {
return q.db.TryAcquireLock(ctx, id)
}
Expand Down Expand Up @@ -2877,18 +2871,6 @@ func (q *querier) UpdateUserAppearanceSettings(ctx context.Context, arg database
return q.db.UpdateUserAppearanceSettings(ctx, arg)
}

// UpdateUserDeletedByID
// Deprecated: Delete this function in favor of 'SoftDeleteUserByID'. Deletes are
// irreversible.
func (q *querier) UpdateUserDeletedByID(ctx context.Context, arg database.UpdateUserDeletedByIDParams) error {
fetch := func(ctx context.Context, arg database.UpdateUserDeletedByIDParams) (database.User, error) {
return q.db.GetUserByID(ctx, arg.ID)
}
// This uses the rbac.ActionDelete action always as this function should always delete.
// We should delete this function in favor of 'SoftDeleteUserByID'.
return deleteQ(q.log, q.auth, fetch, q.db.UpdateUserDeletedByID)(ctx, arg)
}

func (q *querier) UpdateUserHashedPassword(ctx context.Context, arg database.UpdateUserHashedPasswordParams) error {
user, err := q.db.GetUserByID(ctx, arg.ID)
if err != nil {
Expand Down
74 changes: 34 additions & 40 deletions coderd/database/dbmem/dbmem.go
Original file line number Diff line number Diff line change
Expand Up @@ -5573,18 +5573,6 @@ func (q *FakeQuerier) InsertUserGroupsByName(_ context.Context, arg database.Ins
return nil
}

// Took the error from the real database.
var deletedUserLinkError = &pq.Error{
Severity: "ERROR",
// "raise_exception" error
Code: "P0001",
Message: "Cannot create user_link for deleted user",
Where: "PL/pgSQL function insert_user_links_fail_if_user_deleted() line 7 at RAISE",
File: "pl_exec.c",
Line: "3864",
Routine: "exec_stmt_raise",
}

func (q *FakeQuerier) InsertUserLink(_ context.Context, args database.InsertUserLinkParams) (database.UserLink, error) {
q.mutex.Lock()
defer q.mutex.Unlock()
Expand Down Expand Up @@ -6138,6 +6126,40 @@ func (q *FakeQuerier) RevokeDBCryptKey(_ context.Context, activeKeyDigest string
return sql.ErrNoRows
}

// Took the error from the real database.
var deletedUserLinkError = &pq.Error{
Severity: "ERROR",
// "raise_exception" error
Code: "P0001",
Message: "Cannot create user_link for deleted user",
Where: "PL/pgSQL function insert_user_links_fail_if_user_deleted() line 7 at RAISE",
File: "pl_exec.c",
Line: "3864",
Routine: "exec_stmt_raise",
}

func (q *FakeQuerier) SoftDeleteUserByID(ctx context.Context, id uuid.UUID) error {
q.mutex.Lock()
defer q.mutex.Unlock()

for i, u := range q.users {
if u.ID == id {
u.Deleted = true
q.users[i] = u
// NOTE: In the real world, this is done by a trigger.
q.apiKeys = slices.DeleteFunc(q.apiKeys, func(u database.APIKey) bool {
return id == u.UserID
})

q.userLinks = slices.DeleteFunc(q.userLinks, func(u database.UserLink) bool {
return id == u.UserID
})
return nil
}
}
return sql.ErrNoRows
}

func (*FakeQuerier) TryAcquireLock(_ context.Context, _ int64) (bool, error) {
return false, xerrors.New("TryAcquireLock must only be called within a transaction")
}
Expand Down Expand Up @@ -6740,34 +6762,6 @@ func (q *FakeQuerier) UpdateUserAppearanceSettings(_ context.Context, arg databa
return database.User{}, sql.ErrNoRows
}

func (q *FakeQuerier) UpdateUserDeletedByID(_ context.Context, params database.UpdateUserDeletedByIDParams) error {
if err := validateDatabaseType(params); err != nil {
return err
}

q.mutex.Lock()
defer q.mutex.Unlock()

for i, u := range q.users {
if u.ID == params.ID {
u.Deleted = params.Deleted
q.users[i] = u
if params.Deleted {
// NOTE: In the real world, this is done by a trigger.
q.apiKeys = slices.DeleteFunc(q.apiKeys, func(u database.APIKey) bool {
return params.ID == u.UserID
})

q.userLinks = slices.DeleteFunc(q.userLinks, func(u database.UserLink) bool {
return params.ID == u.UserID
})
}
return nil
}
}
return sql.ErrNoRows
}

func (q *FakeQuerier) UpdateUserHashedPassword(_ context.Context, arg database.UpdateUserHashedPasswordParams) error {
if err := validateDatabaseType(arg); err != nil {
return err
Expand Down
14 changes: 7 additions & 7 deletions coderd/database/dbmetrics/dbmetrics.go

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

28 changes: 14 additions & 14 deletions coderd/database/dbmock/dbmock.go

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

2 changes: 1 addition & 1 deletion coderd/database/querier.go

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

33 changes: 14 additions & 19 deletions coderd/database/queries.sql.go

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

4 changes: 2 additions & 2 deletions coderd/database/queries/users.sql
Original file line number Diff line number Diff line change
Expand Up @@ -112,11 +112,11 @@ SET
WHERE
id = $1;

-- name: UpdateUserDeletedByID :exec
-- name: SoftDeleteUserByID :exec
UPDATE
users
SET
deleted = $2
deleted = true
WHERE
id = $1;

Expand Down
5 changes: 1 addition & 4 deletions coderd/users.go
Original file line number Diff line number Diff line change
Expand Up @@ -521,10 +521,7 @@ func (api *API) deleteUser(rw http.ResponseWriter, r *http.Request) {
return
}

err = api.Database.UpdateUserDeletedByID(ctx, database.UpdateUserDeletedByIDParams{
ID: user.ID,
Deleted: true,
})
err = api.Database.SoftDeleteUserByID(ctx, user.ID)
if dbauthz.IsNotAuthorizedError(err) {
httpapi.Forbidden(rw)
return
Expand Down
0