Thread: public schema grants to PUBLIC role
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?
OK, then I'll REVOKE that privilege when creating a new DB.
Like I already revoked the default CONNECT to PUBLIC on the DB.
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?
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?
Thanks, --DD
```
=> select grantor::regrole::text, case grantee when 0 then 'PUBLIC' else grantee::regrole::text end, privilege_type as priv, is_grantable as adm from pg_namespace, lateral aclexplode(nspacl) where nspname = 'public';
grantor | grantee | priv | adm
----------+----------+--------+-----
postgres | postgres | USAGE | f
postgres | postgres | CREATE | f
postgres | PUBLIC | USAGE | f
postgres | PUBLIC | CREATE | f
(4 rows)
grantor | grantee | priv | adm
----------+----------+--------+-----
postgres | postgres | USAGE | f
postgres | postgres | CREATE | f
postgres | PUBLIC | USAGE | f
postgres | PUBLIC | CREATE | f
(4 rows)
```
## Dominique Devienne (ddevienne@gmail.com): > 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? You could just use PostgreSQL 15: https://www.postgresql.org/docs/15/release-15.html#id-1.11.6.7.4 > 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? Plus any grants on the extension's object. > 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? Have a look at default privileges and group roles, that will make your life much easier. https://www.postgresql.org/docs/15/ddl-priv.html Regards, Christoph -- Spare Space.
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
On Thu, Mar 9, 2023 at 2:13 PM Laurenz Albe <laurenz.albe@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