On Sat, Oct 5, 2024 at 11:26 AM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 10/5/24 09:04, Matt Zagrabelny wrote:
>
>
> On Sat, Oct 5, 2024 at 10:27 AM Adrian Klaver <adrian.klaver@xxxxxxxxxxx
> <mailto:adrian.klaver@xxxxxxxxxxx>> 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 | {}
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.
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