databases/administrators/postgres/README.org


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

#+title: Postgres Admins

* =admins.sql=

#+begin_src sql
SELECT 
    r.rolname AS role_name,
    r.rolsuper AS is_superuser,
    r.rolinherit AS inherits_privileges,
    r.rolcreaterole AS can_create_roles,
    r.rolcreatedb AS can_create_db,
    r.rolcanlogin AS can_login,
    r.rolreplication AS can_replication,
    r.rolconnlimit AS connection_limit,
    r.rolvaliduntil AS valid_until,
    ARRAY(
        SELECT b.rolname
        FROM pg_auth_members m
        JOIN pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid
    ) AS member_of
FROM pg_roles r;
#+end_src

#+begin_src
| role_name                   | is_superuser | inherits_privileges | can_create_roles | can_create_db | can_login | can_replication | connection_limit | valid_until            | member_of                                                    |
|-----------------------------+--------------+---------------------+------------------+---------------+-----------+-----------------+------------------+------------------------+--------------------------------------------------------------|
| cmc                         | true         | true                | true             | true          | true      | true            |               -1 |                        | {}                                                           |
| pg_database_owner           | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
| pg_read_all_data            | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
| pg_write_all_data           | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
| pg_monitor                  | false        | true                | false            | false         | false     | false           |               -1 |                        | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables} |
| pg_read_all_settings        | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
| pg_read_all_stats           | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
| pg_stat_scan_tables         | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
| pg_read_server_files        | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
| pg_write_server_files       | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
| pg_execute_server_program   | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
| pg_signal_backend           | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
| pg_checkpoint               | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
| pg_maintain                 | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
| pg_use_reserved_connections | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
| pg_create_subscription      | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
| testuser                    | false        | true                | false            | false         | true      | false           |               -1 | 2025-12-31 00:00:00-06 | {}                                                           |
#+end_src