public inbox for [email protected]help / color / mirror / Atom feed
Re: grant connect to all databases 3+ messages / 3 participants [nested] [flat]
* Re: grant connect to all databases @ 2024-10-05 14:02 David G. Johnston <[email protected]> 2024-10-05 14:13 ` Re: grant connect to all databases Matt Zagrabelny <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: David G. Johnston @ 2024-10-05 14:02 UTC (permalink / raw) To: Matt Zagrabelny <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]> On Saturday, October 5, 2024, Matt Zagrabelny <[email protected]> wrote: > Hello, > > I'd like to have a read-only user for all databases. > > I found the pg_read_all_data role predefined role, which I granted to my > RO user: > > GRANT pg_read_all_data TO ro_user; > > ...but I cannot connect to my database(s). > > I'd like to not have to iterate over all the databases and "GRANT > CONNECT...". > > Is there a way to do this with just one GRANT or equivalent command? > The pseudo-role Public exists for just this kind of thing. In fact, in a default installation it already is given connect privileges on all databases created by the bootstrap superuser. David J. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: grant connect to all databases 2024-10-05 14:02 Re: grant connect to all databases David G. Johnston <[email protected]> @ 2024-10-05 14:13 ` Matt Zagrabelny <[email protected]> 2024-10-05 15:27 ` Re: grant connect to all databases Adrian Klaver <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Matt Zagrabelny @ 2024-10-05 14:13 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]> 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. Is the Public role something I can leverage to achieve this desire? Thanks for the help! -m On Sat, Oct 5, 2024 at 9:02 AM David G. Johnston <[email protected]> wrote: > On Saturday, October 5, 2024, Matt Zagrabelny <[email protected]> wrote: > >> Hello, >> >> I'd like to have a read-only user for all databases. >> >> I found the pg_read_all_data role predefined role, which I granted to my >> RO user: >> >> GRANT pg_read_all_data TO ro_user; >> >> ...but I cannot connect to my database(s). >> >> I'd like to not have to iterate over all the databases and "GRANT >> CONNECT...". >> >> Is there a way to do this with just one GRANT or equivalent command? >> > > > The pseudo-role Public exists for just this kind of thing. In fact, in a > default installation it already is given connect privileges on all > databases created by the bootstrap superuser. > > David J. > > ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: grant connect to all databases 2024-10-05 14:02 Re: grant connect to all databases David G. Johnston <[email protected]> 2024-10-05 14:13 ` Re: grant connect to all databases Matt Zagrabelny <[email protected]> @ 2024-10-05 15:27 ` Adrian Klaver <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Adrian Klaver @ 2024-10-05 15:27 UTC (permalink / raw) To: Matt Zagrabelny <[email protected]>; David G. Johnston <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]> 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? > > Is the Public role something I can leverage to achieve this desire? You should read: https://www.postgresql.org/docs/current/ddl-priv.html From your original post: "but I cannot connect to my database" Was that due to a GRANT issue or a pg_hba.conf issue? What was the actual complete error? > > Thanks for the help! > > -m > > > > On Sat, Oct 5, 2024 at 9:02 AM David G. Johnston > <[email protected] <mailto:[email protected]>> wrote: > > On Saturday, October 5, 2024, Matt Zagrabelny <[email protected] > <mailto:[email protected]>> wrote: > > Hello, > > I'd like to have a read-only user for all databases. > > I found the pg_read_all_data role predefined role, which I > granted to my RO user: > > GRANT pg_read_all_data TO ro_user; > > ...but I cannot connect to my database(s). > > I'd like to not have to iterate over all the databases and > "GRANT CONNECT...". > > Is there a way to do this with just one GRANT or equivalent command? > > > > The pseudo-role Public exists for just this kind of thing. In fact, > in a default installation it already is given connect privileges on > all databases created by the bootstrap superuser. > > David J. > -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-10-05 15:27 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-10-05 14:02 Re: grant connect to all databases David G. Johnston <[email protected]> 2024-10-05 14:13 ` Matt Zagrabelny <[email protected]> 2024-10-05 15:27 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox