public inbox for [email protected]help / color / mirror / Atom feed
Re: grant connect to all databases 8+ messages / 3 participants [nested] [flat]
* Re: grant connect to all databases @ 2024-10-05 16:04 Matt Zagrabelny <[email protected]> 2024-10-05 16:26 ` Re: grant connect to all databases Adrian Klaver <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Matt Zagrabelny @ 2024-10-05 16:04 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: David G. Johnston <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On Sat, Oct 5, 2024 at 10:27 AM Adrian Klaver <[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; ...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 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" (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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: grant connect to all databases 2024-10-05 16:04 Re: grant connect to all databases Matt Zagrabelny <[email protected]> @ 2024-10-05 16:26 ` Adrian Klaver <[email protected]> 2024-10-05 18:24 ` Re: grant connect to all databases Matt Zagrabelny <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Adrian Klaver @ 2024-10-05 16:26 UTC (permalink / raw) To: Matt Zagrabelny <[email protected]>; +Cc: David G. Johnston <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> 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] ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: grant connect to all databases 2024-10-05 16:04 Re: grant connect to all databases Matt Zagrabelny <[email protected]> 2024-10-05 16:26 ` Re: grant connect to all databases Adrian Klaver <[email protected]> @ 2024-10-05 18:24 ` Matt Zagrabelny <[email protected]> 2024-10-05 18:57 ` Re: grant connect to all databases Tom Lane <[email protected]> 2024-10-05 20:11 ` Re: grant connect to all databases Adrian Klaver <[email protected]> 0 siblings, 2 replies; 8+ messages in thread From: Matt Zagrabelny @ 2024-10-05 18:24 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: David G. Johnston <[email protected]>; pgsql-generallists.postgresql.org <[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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: grant connect to all databases 2024-10-05 16:04 Re: grant connect to all databases Matt Zagrabelny <[email protected]> 2024-10-05 16:26 ` Re: grant connect to all databases Adrian Klaver <[email protected]> 2024-10-05 18:24 ` Re: grant connect to all databases Matt Zagrabelny <[email protected]> @ 2024-10-05 18:57 ` Tom Lane <[email protected]> 1 sibling, 0 replies; 8+ messages in thread From: Tom Lane @ 2024-10-05 18:57 UTC (permalink / raw) To: Matt Zagrabelny <[email protected]>; +Cc: Adrian Klaver <[email protected]>; David G. Johnston <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> Matt Zagrabelny <[email protected]> writes: > ...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. This shouldn't be happening, since as mentioned upthread our default for newly-created databases is that they have CONNECT granted to PUBLIC. It works fine for me, even for a user with no special permissions: postgres=# create user alice; CREATE ROLE postgres=# create database test; CREATE DATABASE postgres=# \c test alice You are now connected to database "test" as user "alice". If I explicitly revoke the privilege, I can duplicate your results: test=> \c postgres postgres You are now connected to database "postgres" as user "postgres". postgres=# revoke connect on database test from public; REVOKE postgres=# \c test alice connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: permission denied for database "test" DETAIL: User does not have CONNECT privilege. I wonder if your puppet recipe is revoking that behind your back, or if you are using some modified version of Postgres with different ideas about default privileges. Looking at psql \l output for the test DB might be informative. regards, tom lane ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: grant connect to all databases 2024-10-05 16:04 Re: grant connect to all databases Matt Zagrabelny <[email protected]> 2024-10-05 16:26 ` Re: grant connect to all databases Adrian Klaver <[email protected]> 2024-10-05 18:24 ` Re: grant connect to all databases Matt Zagrabelny <[email protected]> @ 2024-10-05 20:11 ` Adrian Klaver <[email protected]> 2024-10-05 22:04 ` Re: grant connect to all databases Matt Zagrabelny <[email protected]> 1 sibling, 1 reply; 8+ messages in thread From: Adrian Klaver @ 2024-10-05 20:11 UTC (permalink / raw) To: Matt Zagrabelny <[email protected]>; +Cc: David G. Johnston <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On 10/5/24 11:24, Matt Zagrabelny wrote: > > > Nope. I create the role (via puppet) and then add the GRANT > pg_read_all_data TO (via puppet). > > What is \drgS? I don't believe I have that. That is available in Postgres 16+, you must running be in an instance of Postgres before that. > 'CREATE ROLE "alice" ENCRYPTED PASSWORD \'$NEWPGPASSWD\' LOGIN > NOCREATEROLE NOCREATEDB NOSUPERUSER CONNECTION LIMIT -1' > GRANT pg_read_all_data TO alice; > > ...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. Something is going on in the background. What version of Postgres? Where did you install it from or where are you running it? > > Thanks for the help! > > -m -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: grant connect to all databases 2024-10-05 16:04 Re: grant connect to all databases Matt Zagrabelny <[email protected]> 2024-10-05 16:26 ` Re: grant connect to all databases Adrian Klaver <[email protected]> 2024-10-05 18:24 ` Re: grant connect to all databases Matt Zagrabelny <[email protected]> 2024-10-05 20:11 ` Re: grant connect to all databases Adrian Klaver <[email protected]> @ 2024-10-05 22:04 ` Matt Zagrabelny <[email protected]> 2024-10-05 22:43 ` Re: grant connect to all databases Adrian Klaver <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Matt Zagrabelny @ 2024-10-05 22:04 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: David G. Johnston <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On Sat, Oct 5, 2024 at 3:12 PM Adrian Klaver <[email protected]> wrote: > On 10/5/24 11:24, Matt Zagrabelny wrote: > > > > > > > Nope. I create the role (via puppet) and then add the GRANT > > pg_read_all_data TO (via puppet). > > > > > What is \drgS? I don't believe I have that. > > That is available in Postgres 16+, you must running be in an instance of > Postgres before that. > Ah. Yup! > > > > 'CREATE ROLE "alice" ENCRYPTED PASSWORD \'$NEWPGPASSWD\' LOGIN > > NOCREATEROLE NOCREATEDB NOSUPERUSER CONNECTION LIMIT -1' > > GRANT pg_read_all_data TO alice; > > > > > ...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. > > Something is going on in the background. > Agreed. > > What version of Postgres? > psql (15.8 (Debian 15.8-0+deb12u1)) > Where did you install it from or where are you running it? > Installed from Debian repos via apt via puppet. Still digging... -m ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: grant connect to all databases 2024-10-05 16:04 Re: grant connect to all databases Matt Zagrabelny <[email protected]> 2024-10-05 16:26 ` Re: grant connect to all databases Adrian Klaver <[email protected]> 2024-10-05 18:24 ` Re: grant connect to all databases Matt Zagrabelny <[email protected]> 2024-10-05 20:11 ` Re: grant connect to all databases Adrian Klaver <[email protected]> 2024-10-05 22:04 ` Re: grant connect to all databases Matt Zagrabelny <[email protected]> @ 2024-10-05 22:43 ` Adrian Klaver <[email protected]> 2024-10-05 23:37 ` Re: grant connect to all databases Matt Zagrabelny <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Adrian Klaver @ 2024-10-05 22:43 UTC (permalink / raw) To: Matt Zagrabelny <[email protected]>; +Cc: David G. Johnston <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On 10/5/24 15:04, Matt Zagrabelny wrote: > psql (15.8 (Debian 15.8-0+deb12u1)) > > > Where did you install it from or where are you running it? > > > Installed from Debian repos via apt via puppet. > > Still digging... To me it looks like something is doing: REVOKE CONNECT ON DATABASE <some_db> FROM PUBLIC; > > -m -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: grant connect to all databases 2024-10-05 16:04 Re: grant connect to all databases Matt Zagrabelny <[email protected]> 2024-10-05 16:26 ` Re: grant connect to all databases Adrian Klaver <[email protected]> 2024-10-05 18:24 ` Re: grant connect to all databases Matt Zagrabelny <[email protected]> 2024-10-05 20:11 ` Re: grant connect to all databases Adrian Klaver <[email protected]> 2024-10-05 22:04 ` Re: grant connect to all databases Matt Zagrabelny <[email protected]> 2024-10-05 22:43 ` Re: grant connect to all databases Adrian Klaver <[email protected]> @ 2024-10-05 23:37 ` Matt Zagrabelny <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Matt Zagrabelny @ 2024-10-05 23:37 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: David G. Johnston <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On Sat, Oct 5, 2024 at 5:43 PM Adrian Klaver <[email protected]> wrote: > On 10/5/24 15:04, Matt Zagrabelny wrote: > > > psql (15.8 (Debian 15.8-0+deb12u1)) > > > > > > Where did you install it from or where are you running it? > > > > > > Installed from Debian repos via apt via puppet. > > > > Still digging... > > To me it looks like something is doing: > > REVOKE CONNECT > ON DATABASE <some_db> > FROM PUBLIC; > > I think we have a winner. When puppet creates the database, it revokes connect: https://github.com/puppetlabs/puppetlabs-postgresql/blob/main/manifests/server/database.pp#L55-L80 I'll have to ponder a solution. :( Thanks for all the help (David, Adrian, and Tom)! I very much appreciate you taking time to give me some guidance and pointers. Cheers! -m ^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-10-05 23:37 UTC | newest] Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-10-05 16:04 Re: grant connect to all databases Matt Zagrabelny <[email protected]> 2024-10-05 16:26 ` Adrian Klaver <[email protected]> 2024-10-05 18:24 ` Matt Zagrabelny <[email protected]> 2024-10-05 18:57 ` Tom Lane <[email protected]> 2024-10-05 20:11 ` Adrian Klaver <[email protected]> 2024-10-05 22:04 ` Matt Zagrabelny <[email protected]> 2024-10-05 22:43 ` Adrian Klaver <[email protected]> 2024-10-05 23:37 ` Matt Zagrabelny <[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