Re: public schema grants to PUBLIC role

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