From: Tom Lane <tgl@sss.pgh.pa.us>
To: Graham Leggett <minfrin@sharp.fm>
cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Grant read-only access to exactly one database amongst many
In-reply-to: <A75E96A2-13D8-441A-8808-2FF7C4349B65@sharp.fm>
References: <A75E96A2-13D8-441A-8808-2FF7C4349B65@sharp.fm>
Comments: In-reply-to Graham Leggett <minfrin@sharp.fm>
	message dated "Mon, 05 Feb 2024 00:03:50 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4173070.1707094432.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 04 Feb 2024 19:53:52 -0500
Message-ID: <4173071.1707094432@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/4173071.1707094432%40sss.pgh.pa.us>
Precedence: bulk

Graham Leggett <minfrin@sharp.fm> writes:
> Trouble is, I can create tables in db1 which is write access. I can also=
 connect to db2 (bad), and I can enumerate the tables in db2 (bad), althou=
gh the queries of the contents say access is denied.

You need to read the docs about default privileges: see about
halfway down

https://www.postgresql.org/docs/15/ddl-priv.html

where it says "PostgreSQL grants privileges on some types of objects
to PUBLIC by default ...".  In this case I think you likely need to
revoke the default public CREATE privilege on schema public in db1,
and revoke the default public CONNECT privilege on database db2.

			regards, tom lane