Re: public schema grants to PUBLIC role

On Thu, Mar 9, 2023 at 2:13 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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