public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: Matt Zagrabelny <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: grant connect to all databases
Date: Sat, 5 Oct 2024 09:26:22 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAOLfK3V10fBPAhXHLJ6hyZ41BVZQTk=Bbq7MODX30O_REPvQrg@mail.gmail.com>
References: <CAOLfK3Vj-PFBJi28y1170ZP3dGeW2qpG_8_9CbaJWvEgXQ8-jQ@mail.gmail.com>
<CAKFQuwYG8uQhN50MgcF1seg8+dwvgTMFez=wA3Rg2rosob78cg@mail.gmail.com>
<CAOLfK3XOHnyWsLv_CdFAegWg1FgM3AK3WsO_r+rXSNjp8TQXcg@mail.gmail.com>
<[email protected]>
<CAOLfK3V10fBPAhXHLJ6hyZ41BVZQTk=Bbq7MODX30O_REPvQrg@mail.gmail.com>
On 10/5/24 09:04, Matt Zagrabelny wrote:
>
>
> On Sat, Oct 5, 2024 at 10:27 AM Adrian Klaver <[email protected]
> <mailto:[email protected]>> 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
[email protected]
view thread (8+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: grant connect to all databases
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox