pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / 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>
  2023-03-09 11:35 ` Re: public schema grants to PUBLIC role Christoph Moench-Tegeder <cmt@burggraben.net>
  2023-03-09 13:13 ` Re: public schema grants to PUBLIC role Laurenz Albe <laurenz.albe@cybertec.at>
  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 09:34 public schema grants to PUBLIC role Dominique Devienne <ddevienne@gmail.com>
@ 2023-03-09 11:35 ` Christoph Moench-Tegeder <cmt@burggraben.net>
  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 09:34 public schema grants to PUBLIC role Dominique Devienne <ddevienne@gmail.com>
@ 2023-03-09 13:13 ` Laurenz Albe <laurenz.albe@cybertec.at>
  2023-03-09 15:18   ` Re: public schema grants to PUBLIC role 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 09:34 public schema grants to PUBLIC role Dominique Devienne <ddevienne@gmail.com>
  2023-03-09 13:13 ` Re: public schema grants to PUBLIC role Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-03-09 15:18   ` Dominique Devienne <ddevienne@gmail.com>
  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