<!--IssueSummary start--> <details> <summary> Everyone can contribute. [Help move this issue forward](https://handbook.gitlab.com/handbook/marketing/developer-relations/contributor-success/community-contributors-workflows/#contributor-links) while earning points, leveling up and collecting rewards. </summary> - [Close this issue](https://contributors.gitlab.com/manage-issue?action=close&projectId=278964&issueIid=600561) </details> <!--IssueSummary end--> ### Problem to solve GitLab Geo secondaries that use logical replication can break replication when the application accidentally writes to them. To prevent this, on these secondaries the application should: * Connect as a read-only PostgreSQL user (gitlab_readonly) by default. * Route the small set of Sidekiq workers that legitimately need to write (DDL, partition management) to a dedicated geo_privileged queue. * Run the Sidekiq process listening on geo_privileged with the writable gitlab user. ### Proposal On a Linux package install, Omnibus needs to support this with a minimal operator surface — the operator should only have to provide the readonly credentials. Everything else (the credentials helper Rails invokes at connection time, the `database.yml` wiring, the dedicated Sidekiq child, environment injection for the cookbook-driven write paths) should be automatic. This issue tracks the additions needed in Omnibus: - Two new gitlab_rails attributes for the readonly credentials. - A generated credentials helper script at `/var/opt/gitlab/gitlab-rails/etc/geo-db-credentials.rb`. Rails invokes it via `config_command:` in `database.yml`; the script picks readonly or privileged credentials based on the `DB_ROLE` environment variable. The script also chains through any operator-set `gitlab_rails['db_extra_config_command']` so non-Geo uses of that attribute continue to work. - A branch in the `database.yml` rendering recipe to emit the `config_command:` line for this helper when the feature is engaged. - An auto-append of `geo_privileged` to `sidekiq['queue_groups']` so the dedicated Sidekiq child exists without operator config. - `DB_ROLE=privileged` set on the cookbook-driven write paths: the reconfigure auto-migrate execute resource, and the `gitlab-rake geo:set_secondary_as_primary` invocation inside `gitlab-ctl geo promote`. Other operator-driven entrypoints (`gitlab-rake`, `gitlab-rails`, other `gitlab-ctl` subcommands) are intentionally not auto-wrapped — operators must type the `DB_ROLE=privileged` prefix themselves when they intend to write. ### Implementation plan #### Step 1: Add the two new attributes Add: ```ruby default['gitlab']['gitlab_rails']['geo_logical_replication_readonly_username'] = nil default['gitlab']['gitlab_rails']['geo_logical_replication_readonly_password'] = nil ``` wherever `gitlab_rails` defaults live. #### Step 2: Add the engagement predicate helper _File: a cookbook library file (e.g., files/gitlab-cookbooks/gitlab/libraries/gitlab_rails.rb or a new helper)._ Add a class-level helper: ```ruby def self.readonly_active?(node) geo_secondary_enabled = node.role?('geo_secondary_role') && node['gitlab']['geo-secondary']['enable'] != false geo_secondary_enabled && !node['gitlab']['gitlab_rails']['geo_logical_replication_readonly_username'].to_s.empty? && !node['gitlab']['gitlab_rails']['geo_logical_replication_readonly_password'].to_s.empty? end ``` **NB:** The exact attribute paths above need verification during implementation. Both single-server and multi-node Geo promotion flows must correctly suppress the feature: - Single-server: `gitlab-ctl geo promote` writes `{"primary": true, "secondary": false}` to `gitlab-cluster.json`, which flips the computed `geo_secondary_role` to false. - Multi-node: `gitlab-ctl geo promote` writes `{"geo_secondary": {"enable": false}}` only; the top-level secondary key is not touched, so `geo_secondary_role` may stay `true` on these nodes. The predicate must return `false` in both cases. Verify the actual attribute paths the cookbook exposes for these signals (likely `node['gitlab']['geo-secondary']['enable']` or `node['geo_secondary']['enable']` depending on cookbook conventions). Add specs covering both promotion topologies before merging. Used by every subsequent step that conditionally renders something. #### Step 3: Add reconfigure-time validation In the same library or in the database recipe, raise a clear error if exactly one of the two credential attributes is set: ```ruby username = node['gitlab']['gitlab_rails']['geo_logical_replication_readonly_username'] password = node['gitlab']['gitlab_rails']['geo_logical_replication_readonly_password'] if username.to_s.empty? ^ password.to_s.empty? raise "geo_logical_replication_readonly_username and geo_logical_replication_readonly_password must be set together (or both left unset)." end ``` _Verify:_ spec covering both-set, both-unset, only-username, only-password. #### Step 4: Generate the managed credentials wrapper Files: - `files/gitlab-cookbooks/gitlab/templates/default/geo-db-credentials.rb.erb` (the ERB template). - A new block in `files/gitlab-cookbooks/gitlab/recipes/gitlab-rails.rb` (the recipe that renders it), placed near the other database-related blocks. ##### ERB template (geo-db-credentials.rb.erb): ```ruby #!/opt/gitlab/embedded/bin/ruby require 'yaml' # Only act when DB_ROLE=privileged. Otherwise let Rails use the readonly defaults from database.yml. exit 0 unless ENV['DB_ROLE'] == 'privileged' merged = {} # Chain through the operator's db_extra_config_command if set (preserves omnibus!7356 audience). operator_path = '<%= @operator_db_extra_config_command || '' %>' unless operator_path.empty? out = `#{operator_path}` raise "operator config_command failed: #{operator_path}" unless $?.success? merged = YAML.safe_load(out) || {} end # Auto-discover every configured database by reading database.yml at runtime. # Handles main, ci, embedding, sec, and any future decomposed DB without template changes. db_config = YAML.safe_load_file( '<%= @database_yml_path %>', aliases: true ) || {} production = db_config['production'] || {} merged['production'] ||= {} production.each_key do |db_name| merged['production'][db_name] ||= {} merged['production'][db_name]['username'] ||= '<%= @privileged_username %>' merged['production'][db_name]['password'] ||= '<%= @privileged_password %>' end puts merged.to_yaml ``` ##### Recipe block (new, in recipes/gitlab-rails.rb): ```ruby geo_db_credentials_path = "#{gitlab_rails_etc_dir}/geo-db-credentials.rb" template geo_db_credentials_path do source 'geo-db-credentials.rb.erb' owner 'root' group 'root' mode '0700' variables( privileged_username: node['gitlab']['gitlab_rails']['db_username'], privileged_password: node['gitlab']['gitlab_rails']['db_password'], operator_db_extra_config_command: node['gitlab']['gitlab_rails']['db_extra_config_command'], database_yml_path: "#{gitlab_rails_etc_dir}/database.yml" ) sensitive true only_if { GeoLogicalReplication.readonly_active?(node) } end # Remove a stale wrapper if the feature was disabled (e.g., the node was promoted, or # credentials were unset) so a deactivated secondary doesn't leave the file behind. file geo_db_credentials_path do action :delete not_if { GeoLogicalReplication.readonly_active?(node) } end ``` Notes: - `gitlab_rails_etc_dir` resolves to `/var/opt/gitlab/gitlab-rails/etc` and is already established higher up in the same recipe. - `sensitive true` keeps the privileged password out of Chef logs and `gitlab-ctl tail` output. - The paired template + file resources are the standard pattern for "render this file when feature is on, remove it when off." On a promoted node where `readonly_active?` returns `false` (because `geo_secondary_role` or `geo_secondary.enable` flipped), the next reconfigure deletes the stale wrapper. - `GeoLogicalReplication.readonly_active?(node)` is the helper from step 2. Final namespace is whatever the cookbook conventions pick. _Verify:_ - Spec the recipe block in both states (readonly active → template rendered with the right variables; readonly inactive → file deleted if it exists). - Spec the ERB template by rendering it with sample inputs and parsing the output as YAML; verify the privileged overlay is applied for every key under production. #### Step 5: Branch the `database.yml` rendering _File:_ the recipe or template that emits database.yml (typically under files/gitlab-cookbooks/gitlab/recipes/ or templates/default/database.yml.erb). When readonly_active? is true, the recipe substitutes the readonly attribute values into the default username/password positions for every configured database block (main, ci, embedding, sec, etc.), and emits a config_command: line that points at the cookbook-managed wrapper rather than the operator's script. The operator's `db_extra_config_command`, if set, is not lost — it was passed as `the operator_db_extra_config_command` template variable to the wrapper back in step 4, and the wrapper invokes it transitively at runtime when `DB_ROLE=privileged`. From an outside view, the operator's script still runs; it just runs from inside the wrapper rather than directly from Rails. State table: | `readonly_active?` | operator `db_extra_config_command` set | emit | | --- | --- | --- | | true | any | config_command: '/var/opt/gitlab/gitlab-rails/etc/geo-db-credentials.rb' | | false | yes | config_command: '<operator path>' (existing behaviour) | | false | no | no config_command: line | The first row covers normal LR-secondary operation. The second covers existing non-Geo audiences of `db_extra_config_command` (e.g., AWS Secrets Manager). The third is the default for non-Geo nodes. _Verify:_ spec each of the three states and specifically for the `readonly_active? && operator_path_set` row: assert `database.yml` has `config_command: <wrapper-path>` (not the operator path), and that running the wrapper with `DB_ROLE=privileged` after pointing it at a sample operator script produces merged YAML where operator-provided creds win and cookbook defaults fill the rest. #### Step 6: Auto-append `geo_privileged` to `sidekiq['queue_groups']` File: the cookbook recipe that emits the Sidekiq runit/systemd unit (typically files/gitlab-cookbooks/gitlab/recipes/sidekiq.rb or similar). Before generating the unit invocation: ```ruby queue_groups = node['gitlab']['sidekiq']['queue_groups'].dup if readonly_active?(node) unless queue_groups.any? { |g| g.split(',').include?('geo_privileged') } queue_groups << 'geo_privileged' end end # proceed with the existing logic that turns queue_groups into the sidekiq-cluster invocation ``` Carries a cookbook-local constant `PRIVILEGED_QUEUES = ['geo_privileged']` if you want to match the Rails-side constant by name; the dedupe check uses the same value either way. _Verify:_ spec each of (operator has `*`, operator has explicit groups, operator already added `geo_privileged`). In all cases, exactly one Sidekiq child should listen on `geo_privileged` when `readonly_active?`. #### Step 7: Inject `DB_ROLE=privileged` into the reconfigure auto-migrate path File: the cookbook recipe that runs `db:migrate` when `gitlab_rails['auto_migrate'] = true` (search for `execute 'rake db:migrate'` or equivalent under gitlab-cookbooks/gitlab-ee or gitlab-cookbooks/gitlab). Add `'DB_ROLE' => 'privileged'` to the environment hash of the execute resource when `readonly_active?` is `true`: ```ruby execute 'db_migrate' do # ... existing command, cwd, user ... environment( 'PATH' => '...', # ... existing env ... 'DB_ROLE' => readonly_active?(node) ? 'privileged' : nil ).compact end ``` No wrappers for `gitlab-rake`, `gitlab-rails`, or other `gitlab-ctl` subcommands are modified. Operators running those by hand must prefix `DB_ROLE=privileged` themselves when they mean to write. Verify: spec the execute resource's env hash in both states (`readonly_active?` true and false). #### Step 8: Pass `DB_ROLE=privileged` from `gitlab-ctl geo promote` File: `files/gitlab-ctl-commands-ee/lib/geo/promote.rb` (around line 167). `gitlab-ctl geo promote` writes `gitlab-cluster.json` and then runs `gitlab-rake geo:set_secondary_as_primary` before `gitlab-ctl reconfigure` runs. In that window the `database.yml` on disk is still the one the previous reconfigure produced for a secondary — its default username/password positions hold the readonly user's credentials. Without `DB_ROLE=privileged` in the rake task's env, the wrapper exits earl, Rails uses the readonly credentials from `database.yml`, and the rake task fails on the first write with a PostgreSQL permission error. Passing `DB_ROLE=privileged` triggers the wrapper to emit the privileged credential overrides, enabling `geo:set_secondary_as_primary` to connect with write access. ```ruby !run_task('geo:set_secondary_as_primary', env: { ENABLE_SILENT_MODE: options[:enable_silent_mode].to_s, DB_ROLE: 'privileged' }).error? ``` _Verify:_ spec or behavioural test that the env hash passed to run_task includes `DB_ROLE: 'privileged'`. The injection should be unconditional in this code path — by the time `gitlab-ctl geo promote` is invoked, the operator has explicitly opted into the promotion regardless of any predicate, and the rake task definitively needs to write. #### Step 9: Documentation In Omnibus docs, document the two new attributes and what reconfigure does with them. Cover: - That enabling these on a `geo_secondary_role` node is the complete opt-in. - That `db_extra_config_command`, if also set, is automatically chained through the generated wrapper. I don't think we should insist on the env `DB_ROLE=privileged` to avoid it being used carelessly. #### Step 10: End-to-end check Bring up an LR secondary with the two attributes set: - Verify `/var/opt/gitlab/gitlab-rails/etc/geo-db-credentials.rb` exists, mode 0700. - Verify `database.yml` has `config_command:` pointing at the wrapper. - Verify Sidekiq has a child listening on `geo_privileged` and that child's process env contains `DB_ROLE=privileged` (check via /proc/<pid>/environ). - Verify Rails connects as `gitlab_readonly` from puma and from non-privileged Sidekiq children (check pg_stat_activity). - Verify Rails connects as `gitlab` from the `geo_privileged` Sidekiq child. - Run `gitlab-rake db:migrate` without the `DB_ROLE` prefix → verify it fails with a PG permission error. - Run `DB_ROLE=privileged gitlab-rake db:migrate` → verify it succeeds. - Set `gitlab_rails['auto_migrate'] = true`, run `gitlab-ctl reconfigure` → verify migrations succeed. - Run `gitlab-ctl geo promote` → verify the rake task succeeds, `gitlab-cluster.json` is updated, reconfigure regenerates `database.yml` without `config_command:`, the `geo_privileged` Sidekiq child is gone, Rails reconnects as `gitlab`. - Repeat the promotion test on a multi-node setup: verify the predicate correctly identifies the multi-node-promoted node as no longer being a readonly secondary, regardless of which `cluster.json` keys are flipped. :warning: These tests depend on https://gitlab.com/gitlab-org/gitlab/-/work_items/595707