On Thu, 2023-03-09 at 10:34 +0100, Dominique Devienne wrote:
> Hi. I've recently realized via a post (or article?) from Laurenz that the PUBLIC
> role has CREATE privilege on the 'public' schema by default (see query below).
> I guess it can't be avoided?
It can be avoided if you connect to "template1" and
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
there *before* you create a new database.
Or, as Christoph said, if you use v15 or better.
> OK, then I'll REVOKE that privilege when creating a new DB.
> Like I already revoked the default CONNECT to PUBLIC on the DB.
Excellent.
> But I'm wondering about unexpected side-effets.
> In particular, we need extensions, which are loaded in public by default.
> Will USAGE of public be enough for LOGIN users having access to the DB to use extensions?
Yes, that is enough.
> More broadly, we want to secure the DB so that all DB access and schema access are explicit.
> Anything else to be aware of please, beside the two mentioned above?
Avoid SECURITY DEFINER functions with no "search_path" set:
https://www.cybertec-postgresql.com/en/abusing-security-definer-functions/
Yours,
Laurenz Albe