Resolve "Prevent removing the default organization"
What does this MR do and why?
For #411446 (closed)
We create a default organization with id=1 on every GitLab installation.
We also want to make sure that this default organization cannot be deleted under any circumstance. We already have a Rails level validation to prevent such a delete, but we want to be super careful regarding nobody being able to delete the default organization, even via raw SQL query.
So, this MR introduces a BEFORE DELETE trigger on the organizations table that prevents delete of the default organization with id=1
Migrations
UP Migration
main: == [advisory_lock_connection] object_id: 223440, pg_backend_pid: 26861
main: == 20230706080234 AddTriggerOnOrganizations: migrating ========================
main: -- execute("CREATE OR REPLACE FUNCTION prevent_delete_of_default_organization()\nRETURNS TRIGGER AS\n$$\nBEGIN\nIF OLD.id = 1 THEN\n RAISE EXCEPTION 'Deletion of the default Organization is not allowed.';\nEND IF;\nRETURN OLD;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
main: -> 0.0053s
main: -- execute("CREATE TRIGGER prevent_delete_of_default_organization_before_destroy\nBEFORE DELETE ON organizations\nFOR EACH ROW\n\nEXECUTE FUNCTION prevent_delete_of_default_organization()\n")
main: -> 0.0014s
main: == 20230706080234 AddTriggerOnOrganizations: migrated (0.0164s) ===============
main: == [advisory_lock_connection] object_id: 223440, pg_backend_pid: 26861
ci: == [advisory_lock_connection] object_id: 223680, pg_backend_pid: 26863
ci: == 20230706080234 AddTriggerOnOrganizations: migrating ========================
ci: -- execute("CREATE OR REPLACE FUNCTION prevent_delete_of_default_organization()\nRETURNS TRIGGER AS\n$$\nBEGIN\nIF OLD.id = 1 THEN\n RAISE EXCEPTION 'Deletion of the default Organization is not allowed.';\nEND IF;\nRETURN OLD;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
ci: -> 0.0033s
ci: -- execute("CREATE TRIGGER prevent_delete_of_default_organization_before_destroy\nBEFORE DELETE ON organizations\nFOR EACH ROW\n\nEXECUTE FUNCTION prevent_delete_of_default_organization()\n")
ci: -> 0.0010s
ci: == 20230706080234 AddTriggerOnOrganizations: migrated (0.0175s) ===============
ci: == [advisory_lock_connection] object_id: 223680, pg_backend_pid: 26863DOWN Migration
bundle exec rails db:migrate:down:main VERSION=20230706080234
main: == [advisory_lock_connection] object_id: 223160, pg_backend_pid: 28296
main: == 20230706080234 AddTriggerOnOrganizations: reverting ========================
main: -- execute("DROP TRIGGER IF EXISTS prevent_delete_of_default_organization_before_destroy ON organizations")
main: -> 0.0019s
main: -- execute("DROP FUNCTION IF EXISTS prevent_delete_of_default_organization()")
main: -> 0.0005s
main: == 20230706080234 AddTriggerOnOrganizations: reverted (0.0117s) ===============
main: == [advisory_lock_connection] object_id: 223160, pg_backend_pid: 28296
bundle exec rails db:migrate:down:ci VERSION=20230706080234
ci: == [advisory_lock_connection] object_id: 223140, pg_backend_pid: 27841
ci: == 20230706080234 AddTriggerOnOrganizations: reverting ========================
ci: -- execute("DROP TRIGGER IF EXISTS prevent_delete_of_default_organization_before_destroy ON organizations")
ci: -> 0.0016s
ci: -- execute("DROP FUNCTION IF EXISTS prevent_delete_of_default_organization()")
ci: -> 0.0004s
ci: == 20230706080234 AddTriggerOnOrganizations: reverted (0.0142s) ===============
ci: == [advisory_lock_connection] object_id: 223140, pg_backend_pid: 27841Screenshots or screen recordings
Screenshots are required for UI changes, and strongly recommended for all other merge requests.
| Before | After |
|---|---|
How to set up and validate locally
Deleting the default organization via Rails console - leads to PSQL error
[2] pry(main)> Organizations::Organization.all
Organizations::Organization Load (2.5ms) SELECT "organizations".* FROM "organizations" /*application:console,db_config_name:main,console_hostname:Manojs-MacBook-Pro-2.local,console_username:gitlab,line:bin/rails:4:in `<main>'*/
=> [#<Organizations::Organization:0x0000000131d36468
id: 1,
created_at: Thu, 01 Jun 2023 08:31:13.448427000 UTC +00:00,
updated_at: Thu, 01 Jun 2023 08:31:13.448427000 UTC +00:00,
name: "Default",
path: "default">]
[3] pry(main)> Organizations::Organization.first.delete
Organizations::Organization Load (0.5ms) SELECT "organizations".* FROM "organizations" ORDER BY "organizations"."id" ASC LIMIT 1 /*application:console,db_config_name:main,console_hostname:Manojs-MacBook-Pro-2.local,console_username:gitlab,line:(pry):3:in `__pry__'*/
Organizations::Organization Destroy (1.2ms) DELETE FROM "organizations" WHERE "organizations"."id" = 1 /*application:console,db_config_name:main,console_hostname:Manojs-MacBook-Pro-2.local,console_username:gitlab,line:(pry):3:in `__pry__'*/
ActiveRecord::StatementInvalid: PG::RaiseException: ERROR: Deletion of the default Organization is not allowed.
CONTEXT: PL/pgSQL function prevent_delete_of_default_organization() line 4 at RAISE
from /Users/gitlab/.asdf/installs/ruby/3.1.4/lib/ruby/gems/3.1.0/gems/activerecord-7.0.5.1/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `exec_params'
Caused by PG::RaiseException: ERROR: Deletion of the default Organization is not allowed.
CONTEXT: PL/pgSQL function prevent_delete_of_default_organization() line 4 at RAISE
from /Users/gitlab/.asdf/installs/ruby/3.1.4/lib/ruby/gems/3.1.0/gems/activerecord-7.0.5.1/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `exec_params'Deleting any other organization record - works as expected
[4] pry(main)> Organizations::Organization.create(name: 'abc', path: 'abc')
TRANSACTION (0.5ms) BEGIN /*application:console,db_config_name:main,console_hostname:Manojs-MacBook-Pro-2.local,console_username:gitlab,line:(pry):4:in `__pry__'*/
Organizations::Organization Create (1.7ms) INSERT INTO "organizations" ("created_at", "updated_at", "name", "path") VALUES ('2023-06-28 08:24:53.475442', '2023-06-28 08:24:53.475442', 'abc', 'abc') RETURNING "id" /*application:console,db_config_name:main,console_hostname:Manojs-MacBook-Pro-2.local,console_username:gitlab,line:(pry):4:in `__pry__'*/
TRANSACTION (0.2ms) COMMIT /*application:console,db_config_name:main,console_hostname:Manojs-MacBook-Pro-2.local,console_username:gitlab,line:/lib/gitlab/database.rb:418:in `commit'*/
=> #<Organizations::Organization:0x00000001332f69b0
id: 1000,
created_at: Wed, 28 Jun 2023 08:24:53.475442000 UTC +00:00,
updated_at: Wed, 28 Jun 2023 08:24:53.475442000 UTC +00:00,
name: "abc",
path: "abc">
[5] pry(main)> Organizations::Organization.last.delete
Organizations::Organization Load (0.7ms) SELECT "organizations".* FROM "organizations" ORDER BY "organizations"."id" DESC LIMIT 1 /*application:console,db_config_name:main,console_hostname:Manojs-MacBook-Pro-2.local,console_username:gitlab,line:(pry):5:in `__pry__'*/
Organizations::Organization Destroy (6.7ms) DELETE FROM "organizations" WHERE "organizations"."id" = 1000 /*application:console,db_config_name:main,console_hostname:Manojs-MacBook-Pro-2.local,console_username:gitlab,line:(pry):5:in `__pry__'*/
=> #<Organizations::Organization:0x00000001371d66a8
id: 1000,
created_at: Wed, 28 Jun 2023 08:24:53.475442000 UTC +00:00,
updated_at: Wed, 28 Jun 2023 08:24:53.475442000 UTC +00:00,
name: "abc",
path: "abc">
[6] pry(main)>MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #411446 (closed)
Edited by Manoj M J