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.
Right. Didn't think of that. Thanks.
Or, as Christoph said, if you use v15 or better.
Because Managed Azure is still stuck at 14.2, that's currently not an option.
We need both on-prem and managed Azure.
> 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/
Thanks for the reminder. We already set "search_path" on our functions,
because the client code does not always set the search_path at all,
which was resulting in errors. No DEFINER functions either, yet.
I also plan to look at the new function syntax, that eagerly resolve references
at DDL time, rather than runtime, to avoid the search_path dependency at runtime completely.
Although I'm worried about the introspection rewriting already discussed recently... --DD