From e3a307712ee2118af5471cf4091bdbc0124a27d5 Mon Sep 17 00:00:00 2001 From: Omar Qunsul Date: Fri, 18 Mar 2022 17:59:52 +0100 Subject: [PATCH] Adding a rake task to prevent writes to tables in the wrong schema MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Add `gitlab:db:lock_writes` a trigger to prevent writes due to the different gitlab_schema. This is a mechanism that installs write trigger depending on connections and gitlab_schemas allowed to use to prevent changes to existing data. Co-Authored-By: Kamil Trzciński --- lib/tasks/gitlab/db/lock_writes.rake | 85 ++++++++++ spec/tasks/gitlab/db/lock_writes_rake_spec.rb | 152 ++++++++++++++++++ 2 files changed, 237 insertions(+) create mode 100644 lib/tasks/gitlab/db/lock_writes.rake create mode 100644 spec/tasks/gitlab/db/lock_writes_rake_spec.rb diff --git a/lib/tasks/gitlab/db/lock_writes.rake b/lib/tasks/gitlab/db/lock_writes.rake new file mode 100644 index 00000000000000..1d1a67aa2a12b8 --- /dev/null +++ b/lib/tasks/gitlab/db/lock_writes.rake @@ -0,0 +1,85 @@ +# frozen_string_literal: true + +namespace :gitlab do + namespace :db do + TRIGGER_FUNCTION_NAME = 'gitlab_schema_prevent_write' + + desc "GitLab | DB | Install prevent write triggers on all databases" + task lock_writes: [:environment, 'gitlab:db:validate_config'] do + Gitlab::Database::EachDatabase.each_database_connection do |connection, database_name| + create_write_trigger_function(connection) + + schemas_for_connection = Gitlab::Database.gitlab_schemas_for_connection(connection) + Gitlab::Database::GitlabSchema.tables_to_schema.each do |table_name, schema_name| + connection.transaction do + if schemas_for_connection.include?(schema_name.to_sym) + drop_write_trigger(database_name, connection, table_name) + else + create_write_trigger(database_name, connection, table_name) + end + end + end + end + end + + desc "GitLab | DB | Remove all triggers that prevents writes from all databases" + task unlock_writes: :environment do + Gitlab::Database::EachDatabase.each_database_connection do |connection, database_name| + Gitlab::Database::GitlabSchema.tables_to_schema.each do |table_name, schema_name| + drop_write_trigger(database_name, connection, table_name) + end + drop_write_trigger_function(connection) + end + end + + def create_write_trigger_function(connection) + sql = <<-SQL + CREATE OR REPLACE FUNCTION #{TRIGGER_FUNCTION_NAME}() + RETURNS TRIGGER AS + $$ + BEGIN + RAISE EXCEPTION 'Table: "%" is write protected within this Gitlab database.', TG_TABLE_NAME + USING ERRCODE = 'modifying_sql_data_not_permitted', + HINT = 'Make sure you are using the right database connection'; + END + $$ LANGUAGE PLPGSQL + SQL + + connection.execute(sql) + end + + def drop_write_trigger_function(connection) + sql = <<-SQL + DROP FUNCTION IF EXISTS #{TRIGGER_FUNCTION_NAME}() + SQL + + connection.execute(sql) + end + + def create_write_trigger(database_name, connection, table_name) + puts "#{database_name}: '#{table_name}'... Lock Writes".color(:red) + sql = <<-SQL + DROP TRIGGER IF EXISTS #{write_trigger_name(table_name)} ON #{table_name}; + CREATE TRIGGER #{write_trigger_name(table_name)} + BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE + ON #{table_name} + FOR EACH STATEMENT EXECUTE FUNCTION #{TRIGGER_FUNCTION_NAME}(); + SQL + + connection.execute(sql) + end + + def drop_write_trigger(database_name, connection, table_name) + puts "#{database_name}: '#{table_name}'... Allow Writes".color(:green) + sql = <<-SQL + DROP TRIGGER IF EXISTS #{write_trigger_name(table_name)} ON #{table_name} + SQL + + connection.execute(sql) + end + + def write_trigger_name(table_name) + "gitlab_schema_write_trigger_for_#{table_name}" + end + end +end diff --git a/spec/tasks/gitlab/db/lock_writes_rake_spec.rb b/spec/tasks/gitlab/db/lock_writes_rake_spec.rb new file mode 100644 index 00000000000000..da62d136c25c02 --- /dev/null +++ b/spec/tasks/gitlab/db/lock_writes_rake_spec.rb @@ -0,0 +1,152 @@ +# frozen_string_literal: true + +require 'rake_helper' + +RSpec.describe 'gitlab:db:lock_writes', :silence_stdout, :reestablished_active_record_base do + before :all do + Rake.application.rake_require 'active_record/railties/databases' + Rake.application.rake_require 'tasks/seed_fu' + Rake.application.rake_require 'tasks/gitlab/db/validate_config' + Rake.application.rake_require 'tasks/gitlab/db/lock_writes' + + # empty task as env is already loaded + Rake::Task.define_task :environment + end + + let!(:project) { create(:project) } + let!(:ci_build) { create(:ci_build) } + let(:main_connection) { ApplicationRecord.connection } + let(:ci_connection) { Ci::ApplicationRecord.connection } + + context 'single database' do + before do + skip_if_multiple_databases_are_setup + end + + context 'when locking writes' do + it 'does not add any triggers to the main schema tables' do + expect do + run_rake_task('gitlab:db:lock_writes') + end.to change { + number_of_triggers(main_connection) + }.by(0) + end + + it 'will be still able to modify tables that belong to the main two schemas' do + run_rake_task('gitlab:db:lock_writes') + expect do + Project.last.touch + Ci::Build.last.touch + end.not_to raise_error + end + end + end + + context 'multiple databases' do + before do + skip_if_multiple_databases_not_setup + end + + context 'when locking writes' do + it 'adds 3 triggers to the ci schema tables on the main database' do + expect do + run_rake_task('gitlab:db:lock_writes') + end.to change { + number_of_triggers_on(main_connection, Ci::Build.table_name) + }.by(3) # Triggers to block INSERT / UPDATE / DELETE + # Triggers on TRUNCATE are not added to the information_schema.triggers + # See https://www.postgresql.org/message-id/16934.1568989957%40sss.pgh.pa.us + end + + it 'adds 3 triggers to the main schema tables on the ci database' do + expect do + run_rake_task('gitlab:db:lock_writes') + end.to change { + number_of_triggers_on(ci_connection, Project.table_name) + }.by(3) # Triggers to block INSERT / UPDATE / DELETE + # Triggers on TRUNCATE are not added to the information_schema.triggers + # See https://www.postgresql.org/message-id/16934.1568989957%40sss.pgh.pa.us + end + + it 'still allows writes on the tables with the correct connections' do + Project.update_all(updated_at: Time.now) + Ci::Build.update_all(updated_at: Time.now) + end + + it 'still allows writing to gitlab_shared schema on any connection' do + connections = [main_connection, ci_connection] + connections.each do |connection| + Gitlab::Database::SharedModel.using_connection(connection) do + LooseForeignKeys::DeletedRecord.create!( + fully_qualified_table_name: "public.projects", + primary_key_value: 1, + cleanup_attempts: 0 + ) + end + end + end + + it 'prevents writes on the main tables on the ci database' do + run_rake_task('gitlab:db:lock_writes') + expect do + ci_connection.execute("delete from projects") + end.to raise_error(ActiveRecord::StatementInvalid, /Table: "projects" is write protected/) + end + + it 'prevents writes on the ci tables on the main database' do + run_rake_task('gitlab:db:lock_writes') + expect do + main_connection.execute("delete from ci_builds") + end.to raise_error(ActiveRecord::StatementInvalid, /Table: "ci_builds" is write protected/) + end + + it 'prevents truncating a ci table on the main database' do + run_rake_task('gitlab:db:lock_writes') + expect do + main_connection.execute("truncate ci_build_needs") + end.to raise_error(ActiveRecord::StatementInvalid, /Table: "ci_build_needs" is write protected/) + end + end + + context 'when unlocking writes' do + before do + run_rake_task('gitlab:db:lock_writes') + end + + it 'removes the write protection triggers from the gitlab_main tables on the ci database' do + expect do + run_rake_task('gitlab:db:unlock_writes') + end.to change { + number_of_triggers_on(ci_connection, Project.table_name) + }.by(-3) # Triggers to block INSERT / UPDATE / DELETE + # Triggers on TRUNCATE are not added to the information_schema.triggers + # See https://www.postgresql.org/message-id/16934.1568989957%40sss.pgh.pa.us + + expect do + ci_connection.execute("delete from projects") + end.not_to raise_error + end + + it 'removes the write protection triggers from the gitlab_ci tables on the main database' do + expect do + run_rake_task('gitlab:db:unlock_writes') + end.to change { + number_of_triggers_on(main_connection, Ci::Build.table_name) + }.by(-3) + + expect do + main_connection.execute("delete from ci_builds") + end.not_to raise_error + end + end + end + + def number_of_triggers(connection) + connection.select_value("SELECT count(*) FROM information_schema.triggers") + end + + def number_of_triggers_on(connection, table_name) + connection + .select_value("SELECT count(*) FROM information_schema.triggers WHERE event_object_table=$1", nil, [table_name]) + end +end -- GitLab