Feedback-ID: i76984098:Fastmail
Message-ID: <0c089041-d4eb-4bf4-bfcb-6451224190c9@aklaver.com>
Date: Sat, 5 Oct 2024 09:26:22 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: grant connect to all databases
To: Matt Zagrabelny <mzagrabe@d.umn.edu>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
 "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <CAOLfK3Vj-PFBJi28y1170ZP3dGeW2qpG_8_9CbaJWvEgXQ8-jQ@mail.gmail.com>
 <CAKFQuwYG8uQhN50MgcF1seg8+dwvgTMFez=wA3Rg2rosob78cg@mail.gmail.com>
 <CAOLfK3XOHnyWsLv_CdFAegWg1FgM3AK3WsO_r+rXSNjp8TQXcg@mail.gmail.com>
 <cebb02fa-5487-4c06-8527-9c8ba29c13a9@aklaver.com>
 <CAOLfK3V10fBPAhXHLJ6hyZ41BVZQTk=Bbq7MODX30O_REPvQrg@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAOLfK3V10fBPAhXHLJ6hyZ41BVZQTk=Bbq7MODX30O_REPvQrg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/0c089041-d4eb-4bf4-bfcb-6451224190c9%40aklaver.com>
Precedence: bulk

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