public inbox for [email protected]
help / color / mirror / Atom feedFrom: Matt Zagrabelny <[email protected]>
To: Adrian Klaver <[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 13:24:47 -0500
Message-ID: <CAOLfK3W6K-r0+v7Sa9D6684hM3Jg0UxtZ4iOwZiJsteBj0rfoQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
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>
<[email protected]>
On Sat, Oct 5, 2024 at 11:26 AM Adrian Klaver <[email protected]>
wrote:
> 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?
>
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
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: <CAOLfK3W6K-r0+v7Sa9D6684hM3Jg0UxtZ4iOwZiJsteBj0rfoQ@mail.gmail.com>
* 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