On Sat, Oct 5, 2024 at 11:26 AM Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 10/5/24 09:04, Matt Zagrabelny wrote: > > > > > > On Sat, Oct 5, 2024 at 10:27 AM Adrian Klaver <adrian.kla...@aklaver.com > > <mailto:adrian.kla...@aklaver.com>> wrote: > > > > On 10/5/24 07:13, Matt Zagrabelny wrote: > > > Hi David (and others), > > > > > > Thanks for the info about Public. > > > > > > I should expound on my original email. > > > > > > In our dev and test environments our admins (alice, bob, eve) are > > > superusers. In production environments we'd like the admins to be > > read-only. > > > > What are the REVOKE and GRANT commands you use to achieve that? > > > > > > GRANT alice TO pg_read_all_data; > > Does alice have existing GRANTs? > Nope. I create the role (via puppet) and then add the GRANT pg_read_all_data TO (via puppet). > > I would try: > > GRANT pg_read_all_data TO alice; > > As example: > > psql -d test -U postgres > > List of role grants > Role name | Member of | Options | Grantor > ------------+----------------------+--------------+---------- > aklaver | app_admin | INHERIT, SET | postgres > aklaver | production | INHERIT, SET | postgres > dd_admin | dd_owner | ADMIN, SET | postgres > dd_user | dd_admin | INHERIT, SET | postgres > pg_monitor | pg_read_all_settings | INHERIT, SET | postgres > pg_monitor | pg_read_all_stats | INHERIT, SET | postgres > pg_monitor | pg_stat_scan_tables | INHERIT, SET | postgres > postgres | dd_owner | INHERIT, SET | postgres > > > grant pg_read_all_data to adrian; > GRANT ROLE > > test=# \drgS > What is \drgS? I don't believe I have that. > List of role grants > Role name | Member of | Options | Grantor > ------------+----------------------+--------------+---------- > adrian | pg_read_all_data | INHERIT, SET | postgres > aklaver | app_admin | INHERIT, SET | postgres > aklaver | production | INHERIT, SET | postgres > dd_admin | dd_owner | ADMIN, SET | postgres > dd_user | dd_admin | INHERIT, SET | postgres > pg_monitor | pg_read_all_settings | INHERIT, SET | postgres > pg_monitor | pg_read_all_stats | INHERIT, SET | postgres > pg_monitor | pg_stat_scan_tables | INHERIT, SET | postgres > postgres | dd_owner | INHERIT, SET | postgres > > \dt csv_test > List of relations > Schema | Name | Type | Owner > --------+----------+-------+---------- > public | csv_test | table | postgres > > test=# \q > > psql -d test -U adrian > > test=> select * from csv_test ; > id | val > ----+------ > 1 | test > 2 | dog > 3 | cat > 4 | test > 5 | fish > > That looks good. Here is the output of puppet's create role: drop role alice; The next puppet run and I get: 'CREATE ROLE "alice" ENCRYPTED PASSWORD \'$NEWPGPASSWD\' LOGIN NOCREATEROLE NOCREATEDB NOSUPERUSER CONNECTION LIMIT -1' GRANT pg_read_all_data TO alice; test=# \du List of roles Role name | Attributes | Member of ----------------------+------------------------------------------------------------+-------------------- alice | | {pg_read_all_data} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} ...but I still cannot connect: $ psql -d test -U alice psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: permission denied for database "test" DETAIL: User does not have CONNECT privilege. Thanks for the help! -m
