On Thu, Mar 9, 2023 at 2:13 PM Laurenz Albe <laurenz.a...@cybertec.at> wrote:
Hi, 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
