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?
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
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
...and then I could do something like this:
-- for $database in $databases;
GRANT CONNECT ON database $database TO alice;
...but I'd like to achieve it without the `for` loop.
>
> Is the Public role something I can leverage to achieve this desire?
You should read:
https://www.postgresql.org/docs/current/ddl-priv.html
<https://www.postgresql.org/docs/current/ddl-priv.html>
Will do.
From your original post:
"but I cannot connect to my database"
Was that due to a GRANT issue or a pg_hba.conf issue?
It was due to the missing GRANT CONNECT from above. pg_hba looks OK.
What was the actual complete error?
alice$ psql foo
psql: error: connection to server at "db.example.com
<http://db.example.com>" (fe80:100), port 5432 failed: FATAL:
permission denied for database "foo"
...after I GRANT CONNECT, I can connect. However, I don't want to have
to iterate over all the databases to achieve the GRANT CONNECT.
I guess I was hoping that the pg_read_all_data would also allow
connecting. Or if it didn't, there could/would be a
pg_connect_all_databases role.
Cheers,
-m
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
