Re: Grant read-only access to exactly one database amongst many

On Sun, Feb 4, 2024 at 5:04 PM Graham Leggett <minfrin(at)sharp(dot)fm> wrote:

> Hi all,
>
> I have a postgresql 15 instance with two databases in it, and I have a
> need to grant read-only access to one of those databases to a given user.
>
> To do this I created a dedicated role for readonly access to the database
> db1:
>
> CREATE ROLE "dv_read_db1"
> GRANT CONNECT ON DATABASE db1 TO dv_read_db1
>

This grant is basically pointless since by default all roles can connect
everywhere via the PUBLIC pseudo-role. You need to revoke that grant, or
even alter it being given out by default.

> Trouble is, I can create tables in db1 which is write access.

Since in v15 PUBLIC also gets CREATE on the public schema.

I can also connect to db2 (bad),

See my comment regarding the pointless grant in a default setup.

and I can enumerate the tables in db2 (bad),
>

Connect privilege grants reading all catalog data by design.

> I appears the mechanism I am using above has insecure side effects.
>

It has, from your expectation, insecure defaults which you never changed.
We changed public schema in v16 but the ease-of-use database connecting
remains.

David J.