Add PostgreSQL role management utilities
Add PostgreSQL role management utilities.
In order to support operations for Rotate credentials for user gitlab-superuser: https://gitlab.com/gitlab-com/gl-infra/production/-/issues/1847 these scripts have been developed with a default dry-run mode.
Output of executions in dry-run mode on a staging patroni leader node:
root@patroni-04-db-gstg.c.gitlab-staging-1.internal:~/scripts# ./database-gitlab-superuser-session-connection-terminate.sh
Listing all users in the database
List of roles
Role name | Attributes | Member of
----------------------------------+------------------------------------------------------------+-----------
analytics | | {gitlab}
gitlab | 270 connections | {}
gitlab-checkup | Superuser | {}
gitlab-consul | | {}
gitlab-monitor | Superuser | {}
gitlab-psql | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
| 20 connections |
gitlab-repack | Superuser | {}
gitlab-replicator | Replication | {}
gitlab-superuser | Superuser +| {}
| 10 connections |
gitlab-superuser-20200427_181922 | Superuser, Replication +| {}
| 10 connections |
gitlab_geo_fdw | | {}
gitlab_replicator | Replication | {}
gitlab_repmgr | Superuser, Replication | {}
patroni-superuser | Superuser, Replication | {}
pgbouncer | | {}
postgres_exporter | | {}
slony | Superuser, Replication | {}
Listing all connections per user
usename | count
-------------------+-------
gitlab | 18
gitlab-monitor | 1
gitlab-psql | 1
gitlab-superuser | 2
pgbouncer | 1
postgres_exporter | 1
(6 rows)
Listing all connections except my session
pid | usename
-------+-------------------
18688 | gitlab-superuser
18717 | postgres_exporter
16667 | gitlab-psql
16048 | gitlab
29547 | gitlab-monitor
9425 | gitlab
15744 | gitlab
10565 | gitlab
15416 | gitlab
16027 | gitlab
14729 | gitlab
15790 | gitlab
2397 | gitlab
16042 | gitlab
16047 | gitlab
6114 | gitlab
16359 | pgbouncer
16668 | gitlab
16669 | gitlab
14733 | gitlab
14735 | gitlab
325 | gitlab
14738 | gitlab
(23 rows)
Showing who I am
usename
------------------
gitlab-superuser
(1 row)
| pg-ha-cluster | patroni-04-db-gstg.c.gitlab-staging-1.internal | 10.224.29.104 | Leader | running | 151 | 0 |
The current host patroni-04-db-gstg IS the patroni leader
Terminating database session connection(s) for user role gitlab-superuser
[Dry-run] Would have executed psql command: SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'gitlab-superuser';
[Dry-run] Re-execute with the --wet-run parameter to actually execute commands to make modificationsroot@patroni-04-db-gstg.c.gitlab-staging-1.internal:~/scripts# ./database-gitlab-superuser-user-role-create.sh
List of roles
Role name | Attributes | Member of
----------------------------------+------------------------------------------------------------+-----------
analytics | | {gitlab}
gitlab | 270 connections | {}
gitlab-checkup | Superuser | {}
gitlab-consul | | {}
gitlab-monitor | Superuser | {}
gitlab-psql | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
| 20 connections |
gitlab-repack | Superuser | {}
gitlab-replicator | Replication | {}
gitlab-superuser | Superuser +| {}
| 10 connections |
gitlab-superuser-20200427_181922 | Superuser, Replication +| {}
| 10 connections |
gitlab_geo_fdw | | {}
gitlab_replicator | Replication | {}
gitlab_repmgr | Superuser, Replication | {}
patroni-superuser | Superuser, Replication | {}
pgbouncer | | {}
postgres_exporter | | {}
slony | Superuser, Replication | {}
| pg-ha-cluster | patroni-04-db-gstg.c.gitlab-staging-1.internal | 10.224.29.104 | Leader | running | 151 | 0 |
The current host patroni-04-db-gstg IS the patroni leader
Adding new user: gitlab-superuser-20200507_171803
[Dry-run] Would have executed psql command: CREATE USER "gitlab-superuser-20200507_171803" WITH
SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN REPLICATION NOBYPASSRLS
CONNECTION LIMIT 10
ENCRYPTED PASSWORD 'CHAN...[redacted]...';
[Dry-run] Re-execute with the --wet-run parameter to actually execute commands to make modifications
Granting all privileges to: gitlab-superuser-20200507_171803
[Dry-run] Would have executed psql command: GRANT ALL PRIVILEGES ON DATABASE gitlabhq_production TO "gitlab-superuser-20200507_171803";
[Dry-run] Re-execute with the --wet-run parameter to actually execute commands to make modifications
Listing all tables owned by: gitlab-superuser-20200507_171803
Executing psql command: SELECT * FROM pg_tables t WHERE t.tableowner = 'gitlab-superuser';
+ /usr/local/bin/gitlab-psql --command 'SELECT * FROM pg_tables t WHERE t.tableowner = '\''gitlab-superuser'\'';'
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
(0 rows)
+ set +x
Setting statement_timeout to 0 for role: gitlab-superuser-20200507_171803
[Dry-run] Would have executed psql command: ALTER ROLE "gitlab-superuser-20200507_171803" SET statement_timeout TO '0';
[Dry-run] Re-execute with the --wet-run parameter to actually execute commands to make modifications
The new gitlab-superuser-20200507_171803 role has not yet been added to the databaseroot@patroni-04-db-gstg.c.gitlab-staging-1.internal:~/scripts# ./database-gitlab-superuser-user-role-password-update.sh
Listing all users in the database
List of roles
Role name | Attributes | Member of
----------------------------------+------------------------------------------------------------+-----------
analytics | | {gitlab}
gitlab | 270 connections | {}
gitlab-checkup | Superuser | {}
gitlab-consul | | {}
gitlab-monitor | Superuser | {}
gitlab-psql | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
| 20 connections |
gitlab-repack | Superuser | {}
gitlab-replicator | Replication | {}
gitlab-superuser | Superuser +| {}
| 10 connections |
gitlab-superuser-20200427_181922 | Superuser, Replication +| {}
| 10 connections |
gitlab_geo_fdw | | {}
gitlab_replicator | Replication | {}
gitlab_repmgr | Superuser, Replication | {}
patroni-superuser | Superuser, Replication | {}
pgbouncer | | {}
postgres_exporter | | {}
slony | Superuser, Replication | {}
Listing all connections per user
usename | count
-------------------+-------
gitlab | 24
gitlab-monitor | 1
gitlab-superuser | 2
pgbouncer | 5
postgres_exporter | 1
(5 rows)
Listing all connections except my session
pid | usename
-------+-------------------
18688 | gitlab-superuser
18717 | postgres_exporter
17626 | pgbouncer
16048 | gitlab
17668 | gitlab
29547 | gitlab-monitor
17670 | gitlab
9425 | gitlab
17672 | gitlab
15744 | gitlab
10565 | gitlab
15416 | gitlab
16027 | gitlab
14729 | gitlab
17673 | pgbouncer
15790 | gitlab
17335 | gitlab
2397 | gitlab
17337 | gitlab
16042 | gitlab
16047 | gitlab
6114 | gitlab
17338 | gitlab
17674 | pgbouncer
17681 | gitlab
17682 | gitlab
16668 | gitlab
17689 | pgbouncer
14733 | gitlab
14735 | gitlab
17715 | pgbouncer
14738 | gitlab
(32 rows)
Showing who I am
usename
------------------
gitlab-superuser
(1 row)
| pg-ha-cluster | patroni-04-db-gstg.c.gitlab-staging-1.internal | 10.224.29.104 | Leader | running | 151 | 0 |
The current host patroni-04-db-gstg IS the patroni leader
Updating password for user: gitlab-superuser
[Dry-run] Would have executed psql command: ALTER ROLE "gitlab-superuser" WITH
ENCRYPTED PASSWORD 'CHAN...[redacted]...';
[Dry-run] Re-execute with the --wet-run parameter to actually execute commands to make modifications
Terminating database session connection(s) for user role gitlab-superuser
[Dry-run] Would have executed psql command: SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = "gitlab-superuser";
[Dry-run] Re-execute with the --wet-run parameter to actually execute commands to make modificationsroot@patroni-04-db-gstg.c.gitlab-staging-1.internal:~/scripts# ./database-user-role-delete.sh
List of roles
Role name | Attributes | Member of
----------------------------------+------------------------------------------------------------+-----------
analytics | | {gitlab}
gitlab | 270 connections | {}
gitlab-checkup | Superuser | {}
gitlab-consul | | {}
gitlab-monitor | Superuser | {}
gitlab-psql | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
| 20 connections |
gitlab-repack | Superuser | {}
gitlab-replicator | Replication | {}
gitlab-superuser | Superuser +| {}
| 10 connections |
gitlab-superuser-20200427_181922 | Superuser, Replication +| {}
| 10 connections |
gitlab_geo_fdw | | {}
gitlab_replicator | Replication | {}
gitlab_repmgr | Superuser, Replication | {}
patroni-superuser | Superuser, Replication | {}
pgbouncer | | {}
postgres_exporter | | {}
slony | Superuser, Replication | {}
The user role does not exist in the database: CHANGEME
Listing all tables owned by: CHANGEME
Executing psql command: SELECT * FROM pg_tables t WHERE t.tableowner = 'CHANGEME';
+ /usr/local/bin/gitlab-psql --command 'SELECT * FROM pg_tables t WHERE t.tableowner = '\''CHANGEME'\'';'
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
(0 rows)
+ set +x
Aborting because the role does not exist in the database: CHANGEME
root@patroni-04-db-gstg.c.gitlab-staging-1.internal:~/scripts# ./database-user-role-delete.sh gitlab-superuser-20200427_181922
List of roles
Role name | Attributes | Member of
----------------------------------+------------------------------------------------------------+-----------
analytics | | {gitlab}
gitlab | 270 connections | {}
gitlab-checkup | Superuser | {}
gitlab-consul | | {}
gitlab-monitor | Superuser | {}
gitlab-psql | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
| 20 connections |
gitlab-repack | Superuser | {}
gitlab-replicator | Replication | {}
gitlab-superuser | Superuser +| {}
| 10 connections |
gitlab-superuser-20200427_181922 | Superuser, Replication +| {}
| 10 connections |
gitlab_geo_fdw | | {}
gitlab_replicator | Replication | {}
gitlab_repmgr | Superuser, Replication | {}
patroni-superuser | Superuser, Replication | {}
pgbouncer | | {}
postgres_exporter | | {}
slony | Superuser, Replication | {}
gitlab-superuser-20200427_181922 | Superuser, Replication +| {}
The user gitlab-superuser-20200427_181922 role exists in the database
Listing all tables owned by: gitlab-superuser-20200427_181922
Executing psql command: SELECT * FROM pg_tables t WHERE t.tableowner = 'gitlab-superuser-20200427_181922';
+ /usr/local/bin/gitlab-psql --command 'SELECT * FROM pg_tables t WHERE t.tableowner = '\''gitlab-superuser-20200427_181922'\'';'
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
(0 rows)
+ set +x
gitlab-superuser-20200427_181922 | Superuser, Replication +| {}
Dropping objects (privilege settings) owned by user gitlab-superuser-20200427_181922
[Dry-run] Would have executed psql command: DROP OWNED BY "gitlab-superuser-20200427_181922";
[Dry-run] Re-execute with the --wet-run parameter to actually execute commands to make modifications
Deleting user gitlab-superuser-20200427_181922 role
[Dry-run] Would have executed psql command: DROP ROLE "gitlab-superuser-20200427_181922";
[Dry-run] Re-execute with the --wet-run parameter to actually execute commands to make modificationsEdited by Nels Nelson