Automatically locking the newly created tables for writes

What does this MR do and why?

As part of the database decomposition project we made a copy of the database on a new cluster. We used the first database cluster, called main for all the database tables that belong to the gitlab_main gitlab schema. And used the new database cluster ci for all the tables that belong to the gitlab_schema gitlab_ci. While the gitlab_shared tables are still used on both clusters

After the decomposition we use gitlab_main and gitlab_shared tables on the database cluster main. And we use the gitlab_ci and gitlab_shared tables on the database cluster ci. But the database schema (not to be confused with gitlab_schema) is still the same on both database clusters. We are keeping it now like this for simplicity. That means we still have some legacy tables, that we have been truncated recently, that the application in theory still can write/read from, but it should not. These tables should be ignored. We decided to lock_writes to them, to detect with errors early on that the application is writing to the wrong database.

For example, the application should not write to the users table on the ci database, even though it exists. And the same applies to the ci_builds table on the main database. We should get an error instead.

Before this MR we had a rake task that would lock writes to these legacy tables (CI tables in the Main database and Main tables in the CI database). The rake task is gitlab:db:lock_writes. We ran this task in production after executing CI decomposition but it was a once off task. And we ran it again once as part of the change request gitlab-com/gl-infra/production#7770 (closed). Since then we’ve created new tables and they aren’t locked. As such there might be some gitlab_ci tables in main, and gitlab_main tables on ci, and which can still be written to but they should not be. This MR takes our original approach to locking and expands it to lock newly created tables by hooking into our DB migrations to detect newly created tables. Once this is deployed we’ll still need to lock writes to any tables that were already created but we should only need to do that 1 more time as this MR will take care of any newly created tables.

After this is merged, deployed and verified, we will need to run the rake task gitlab:db:lock_writes again for the legacy tables that haven't been locked yet.

Bumping the default database migration to Gitlab::Database::Migration[2.1]

Only future database migrations will have this functionality enabled. Old migrations are not affected

Related issue addressed by this MR: #362613 (closed)

How to set up and validate locally

First: Enable the Feature Flag

The feature flag that controls this whole automatic locking on tables functionality is disabled by default. To start testing, enable it from the Rails Console using

Feature.enable(:automatic_lock_writes_on_table)

Testing on a single Databases

Make sure you are running on a single database setup. The ci database should be pointing to the same database as main, and has database_tasks: false

Something like this in the config/database.yml file

development:
  main:
    database: gitlabhq_development
  ci:
    database: gitlabhq_development_ci
    database_tasks: true

Instructions

bin/rails generate model TestLockWritesItem title:string body:text
echo "test_lock_writes_items: :gitlab_ci" >> lib/gitlab/database/gitlab_schemas.yml
rake db:migrate

• This should not raise an error

gdk psql -d "gitlabhq_development" -c "delete from test_lock_writes_items"

Cleanup

rake db:rollback
bin/rails d model TestLockWritesItem title:string body:text
git checkout lib/gitlab/database/gitlab_schemas.yml
git checkout db/structure.sql

Multiple Databases: Testing gitlab_main table

Make sure you are running the databases with multi-db setup, which is the default setup of GDK.

Instructions

bin/rails generate model TestLockWritesItem title:string body:text
echo "test_lock_writes_items: :gitlab_main" >> lib/gitlab/database/gitlab_schemas.yml
rake db:migrate

• This should not raise an error

gdk psql -d "gitlabhq_development" -c "delete from test_lock_writes_items"

• This should raise an error Table: "test_lock_writes_items" is write protected within this Gitlab database.

gdk psql -d "gitlabhq_development_ci" -c "delete from test_lock_writes_items"

Cleanup

rake db:rollback:main
rake db:rollback:ci
bin/rails d model TestLockWritesItem title:string body:text
git checkout lib/gitlab/database/gitlab_schemas.yml
git checkout db/structure.sql

Multiple Databases: Testing gitlab_ci table

bin/rails generate model TestLockWritesItem title:string body:text
echo "test_lock_writes_items: :gitlab_ci" >> lib/gitlab/database/gitlab_schemas.yml
rake db:migrate

• This should raise an error

gdk psql -d "gitlabhq_development" -c "delete from test_lock_writes_items"

• This should not raise an error.

gdk psql -d "gitlabhq_development_ci" -c "delete from test_lock_writes_items"

Cleanup

rake db:rollback:main
rake db:rollback:ci
bin/rails d model TestLockWritesItem title:string body:text
git checkout lib/gitlab/database/gitlab_schemas.yml
git checkout db/structure.sql

Multiple Databases: Testing gitlab_shared table

bin/rails generate model TestLockWritesItem title:string body:text
echo "test_lock_writes_items: :gitlab_shared" >> lib/gitlab/database/gitlab_schemas.yml
rake db:migrate

• This should not raise an error

gdk psql -d "gitlabhq_development" -c "delete from test_lock_writes_items"

• This should not raise an error either.

gdk psql -d "gitlabhq_development_ci" -c "delete from test_lock_writes_items"

Cleanup

rake db:rollback:main
rake db:rollback:ci
bin/rails d model TestLockWritesItem title:string body:text
git checkout lib/gitlab/database/gitlab_schemas.yml
git checkout db/structure.sql

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 #362613 (closed)