pg.ddx.io pgsql-general@postgresql.org mailing list archivehelp / color / mirror / Atom feed
public schema grants to PUBLIC role 4+ messages / 3 participants [nested] [flat]
* public schema grants to PUBLIC role @ 2023-03-09 09:34 Dominique Devienne <ddevienne@gmail.com> 0 siblings, 2 replies; 4+ messages in thread From: Dominique Devienne @ 2023-03-09 09:34 UTC (permalink / raw) To: pgsql-general@lists.postgresql.org 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) ``` ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: public schema grants to PUBLIC role @ 2023-03-09 11:35 Christoph Moench-Tegeder <cmt@burggraben.net> parent: Dominique Devienne <ddevienne@gmail.com> 1 sibling, 0 replies; 4+ messages in thread From: Christoph Moench-Tegeder @ 2023-03-09 11:35 UTC (permalink / raw) To: Dominique Devienne <ddevienne@gmail.com>; +Cc: pgsql-general@lists.postgresql.org ## 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. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: public schema grants to PUBLIC role @ 2023-03-09 13:13 Laurenz Albe <laurenz.albe@cybertec.at> parent: Dominique Devienne <ddevienne@gmail.com> 1 sibling, 1 reply; 4+ messages in thread From: Laurenz Albe @ 2023-03-09 13:13 UTC (permalink / raw) To: Dominique Devienne <ddevienne@gmail.com>; pgsql-general@lists.postgresql.org 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: public schema grants to PUBLIC role @ 2023-03-09 15:18 Dominique Devienne <ddevienne@gmail.com> parent: Laurenz Albe <laurenz.albe@cybertec.at> 0 siblings, 0 replies; 4+ messages in thread From: Dominique Devienne @ 2023-03-09 15:18 UTC (permalink / raw) To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-general@lists.postgresql.org 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-03-09 15:18 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-03-09 09:34 public schema grants to PUBLIC role Dominique Devienne <ddevienne@gmail.com> 2023-03-09 11:35 ` Christoph Moench-Tegeder <cmt@burggraben.net> 2023-03-09 13:13 ` Laurenz Albe <laurenz.albe@cybertec.at> 2023-03-09 15:18 ` Dominique Devienne <ddevienne@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox