Subject: Re: grant connect to all databases

From: Matt Zagrabelny <[email-masked]>
Date: Sat, 5 Oct 2024 11:04:39 -0500

On Sat, Oct 5, 2024 at 10:27 AM Adrian Klaver <[email-masked]>
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

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 5 Oct 2024 09:26:22 -0700

On 10/5/24 09:04, Matt Zagrabelny wrote:
> 
> 
> On Sat, Oct 5, 2024 at 10:27 AM Adrian Klaver <[email-masked] 
> <mailto:[email-masked]>> wrote:
> 

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

>     <https://www.postgresql.org/docs/current/ddl-priv.html>
> 
> 
> Will do.
> 
> 
> 
> 
>       From your original post:
> 
> 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-masked]

---

From: Matt Zagrabelny <[email-masked]>
Date: Sat, 5 Oct 2024 13:24:47 -0500

> Does alice have existing GRANTs?

Nope. I create the role (via puppet) and then add the GRANT
pg_read_all_data TO (via puppet).

>   Role name  |      Member of       |   Options    | Grantor

What is \drgS? I don't believe I have that.

>   Role name  |      Member of       |   Options    | Grantor
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

---

From: Tom Lane <[email-masked]>
Date: Sat, 05 Oct 2024 14:57:59 -0400

> 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

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 5 Oct 2024 13:11:56 -0700

On 10/5/24 11:24, Matt Zagrabelny wrote:

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

> "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  permission denied 
> for database "test"

Something is going on in the background.

What version of Postgres?

Where did you install it from or where are you running it?

---

From: Matt Zagrabelny <[email-masked]>
Date: Sat, 5 Oct 2024 17:04:15 -0500

> That is available in Postgres 16+, you must running be in an instance of

Ah. Yup!

> 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

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 5 Oct 2024 15:43:17 -0700

> Installed from Debian repos via apt via puppet.

To me it looks like something is doing:

REVOKE CONNECT
     ON DATABASE <some_db>
     FROM PUBLIC;

---

From: Matt Zagrabelny <[email-masked]>
Date: Sat, 5 Oct 2024 18:37:39 -0500

> To me it looks like something is doing:
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