Re: grant connect to all databases

On Sat, Oct 5, 2024 at 11:26 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

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