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
