Home > mailing lists

Thread: grant connect to all databases

grant connect to all databases

From

Matt Zagrabelny

Date:

05 October 2024, 16:44:32

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?

Thanks for any help!

-m

Re: grant connect to all databases

From

"David G. Johnston"

Date:

05 October 2024, 17:02:43

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.

Re: grant connect to all databases

From

Matt Zagrabelny

Date:

05 October 2024, 17:13:59

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

Re: grant connect to all databases

From

Adrian Klaver

Date:

05 October 2024, 18:27:21

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

Re: grant connect to all databases

From

Matt Zagrabelny

Date:

05 October 2024, 19:04:39

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

Re: grant connect to all databases

From

Adrian Klaver

Date:

05 October 2024, 19:26:22

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

Re: grant connect to all databases

From

Matt Zagrabelny

Date:

05 October 2024, 21:24:47

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

Re: grant connect to all databases

From

Tom Lane

Date:

05 October 2024, 21:57:59

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

Re: grant connect to all databases

From

Adrian Klaver

Date:

05 October 2024, 23:11:56

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

Re: grant connect to all databases

From

Matt Zagrabelny

Date:

06 October 2024, 01:04:15

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

Re: grant connect to all databases

From

Adrian Klaver

Date:

06 October 2024, 01:43:17

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

Re: grant connect to all databases

From

Matt Zagrabelny

Date:

06 October 2024, 02:37:39

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