On Sun, Feb 4, 2024 at 5:04 PM Graham Leggett <minf...@sharp.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.
