Re: grant connect to all databases - Mailing list pgsql-general

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

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@aklaver.com