Setup Siphon users rake task

What does this MR do and why?

This MR adds a rake task to set up the GitLab database (or databases when decomposed) for production use of Siphon:

• Assumes that the siphon* users are created. (the default gitlab user cannot create users)
• Adjusts user permissions.
• Creates PUBLICATION.
• Installs the secure alter publication function. https://gitlab.com/gitlab-org/analytics-section/siphon/#following-the-least-privilege-model

TLDR: we port the bash script to ruby for easier integration with self managed and dedicated.

One time user setup:

    CREATE USER siphon WITH PASSWORD 'siphon' NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;
    CREATE USER siphon_replicator WITH REPLICATION LOGIN PASSWORD 'siphon' NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;
    CREATE USER siphon_snapshot WITH PASSWORD 'siphon' NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;

Invocation and output:

 $ bundle exec rake gitlab:siphon:setup

=== main ===
  Created or replaced public.siphon_alter_publication
  Granted EXECUTE on public.siphon_alter_publication to siphon only
  Publication siphon_publication_main_1 already exists
  Granted USAGE and SELECT on public to siphon, siphon_replicator, siphon_snapshot
  Granted USAGE and SELECT on gitlab_partitions_dynamic to siphon, siphon_replicator, siphon_snapshot
  Granted USAGE and SELECT on gitlab_partitions_static to siphon, siphon_replicator, siphon_snapshot

=== ci ===
  Created or replaced public.siphon_alter_publication
  Granted EXECUTE on public.siphon_alter_publication to siphon only
  Publication siphon_publication_ci_1 already exists
  Granted USAGE and SELECT on public to siphon, siphon_replicator, siphon_snapshot
  Granted USAGE and SELECT on gitlab_partitions_dynamic to siphon, siphon_replicator, siphon_snapshot
  Granted USAGE and SELECT on gitlab_partitions_static to siphon, siphon_replicator, siphon_snapshot

=== sec ===
  Created or replaced public.siphon_alter_publication
  Granted EXECUTE on public.siphon_alter_publication to siphon only
  Publication siphon_publication_sec_1 already exists
  Granted USAGE and SELECT on public to siphon, siphon_replicator, siphon_snapshot
  Granted USAGE and SELECT on gitlab_partitions_dynamic to siphon, siphon_replicator, siphon_snapshot
  Granted USAGE and SELECT on gitlab_partitions_static to siphon, siphon_replicator, siphon_snapshot

After this step, Siphon should be ready to be used

References

Move Siphon provisioning SQL out of Instrumentor (gitlab-org/analytics-section/siphon#274 - closed)

How to set up and validate locally

The rake task can be tested with GDK. You may have multiple databases set up for your GDK, in that case you can run multiple docker images.

1. Start a new PG docker image

   docker run \
     --name pg-17-sec \
     --rm -p 5433:5432 \
     -e POSTGRES_USER=postgres \
     -e POSTGRES_PASSWORD=postgres \
     postgres:17 \
     -c wal_level=logical

2. Create the gitlab user

     CREATE USER gitlab WITH PASSWORD 'gitlab' CREATEDB;
     CREATE DATABASE gitlabhq_production OWNER gitlab;

3. Create the siphon* users

     CREATE USER siphon WITH PASSWORD 'siphon' NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;
     CREATE USER siphon_replicator WITH REPLICATION LOGIN PASSWORD 'siphon' NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;
     CREATE USER siphon_snapshot WITH PASSWORD 'siphon' NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;

4. Dump your current GDK data

     pg_dump "host=your_gdk_pg_host user=your_user dbname=gitlabhq_development"  > db.sql

5. Restore the data into the new DB

   psql "user=gitlab password=gitlab port=5433 host=localhost dbname=gitlabhq_production" < db.sql

6. Reconfigure config/database.yml

   development:
     main:
       adapter: postgresql
       encoding: unicode
       database: gitlabhq_production
       host: localhost
       port: 5433
       user: gitlab
       password: gitlab
       pool: 10
       gssencmode: disable
       prepared_statements: false
       variables:
         statement_timeout: 120s

7. Run the rake task: bundle exec rake gitlab:siphon:setup
8. Verify that the publication is created.

   psql "user=gitlab password=gitlab port=5433 host=localhost dbname=gitlabhq_production" -c "select * from pg_publication"

9. Verify that the alter publication function works.

   psql "user=siphon password=siphon port=5433 host=localhost dbname=gitlabhq_production" -c "select siphon_alter_publication('siphon_publication_main_1', 'public.issues', 0)"
    siphon_alter_publication
   --------------------------
   
   (1 row)

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.