grant connect to all databases

On Saturday, October 5, 2024, Matt Zagrabelny <mzagrabe@d.umn.edu> 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.

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 <david.g.johnston@gmail.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?

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
<david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:

On Saturday, October 5, 2024, Matt Zagrabelny <mzagrabe@d.umn.edu
<mailto:mzagrabe@d.umn.edu>> 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
adrian.klaver@aklaver.com

On Sat, Oct 5, 2024 at 10:27 AM Adrian Klaver <adrian.klaver@aklaver.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;

...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

On 10/5/24 09:04, Matt Zagrabelny wrote:

On Sat, Oct 5, 2024 at 10:27 AM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.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?

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&gt;

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&gt;&quot; (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
adrian.klaver@aklaver.com

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

On 10/5/24 09:04, Matt Zagrabelny wrote:

On Sat, Oct 5, 2024 at 10:27 AM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.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

Matt Zagrabelny <mzagrabe@d.umn.edu> 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

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
adrian.klaver@aklaver.com

On Sat, Oct 5, 2024 at 3:12 PM Adrian Klaver <adrian.klaver@aklaver.com>
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

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
adrian.klaver@aklaver.com

On Sat, Oct 5, 2024 at 5:43 PM Adrian Klaver <adrian.klaver@aklaver.com>
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