pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
20+ messages / 7 participants
[nested] [flat]

Commits from this thread:
  2022-12-01 ef2d7c6f0b afa4a4f764  Doc: word-smith the discussion of secure schema usage patterns.
  2021-12-17 944dc45d1b  Fix the public schema's permissions in a separate test script.
* pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
@ 2021-09-10 06:39 Noah Misch <noah@leadboat.com>
  2021-12-17 17:52 ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Tom Lane <tgl@sss.pgh.pa.us>
  2022-11-29 19:22 ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  0 siblings, 2 replies; 20+ messages in thread

From: Noah Misch @ 2021-09-10 06:39 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

Revoke PUBLIC CREATE from public schema, now owned by pg_database_owner.

This switches the default ACL to what the documentation has recommended
since CVE-2018-1058.  Upgrades will carry forward any old ownership and
ACL.  Sites that declined the 2018 recommendation should take a fresh
look.  Recipes for commissioning a new database cluster from scratch may
need to create a schema, grant more privileges, etc.  Out-of-tree test
suites may require such updates.

Reviewed by Peter Eisentraut.

Discussion: https://postgr.es/m/20201031163518.GB4039133@rfd.leadboat.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/b073c3ccd06e4cb845e121387a43faa8c68a7b62

Modified Files
--------------
contrib/postgres_fdw/expected/postgres_fdw.out |  2 +-
contrib/postgres_fdw/sql/postgres_fdw.sql      |  2 +-
doc/src/sgml/ddl.sgml                          | 56 ++++++++++++++------------
doc/src/sgml/user-manag.sgml                   | 19 ++++-----
src/bin/initdb/initdb.c                        |  3 +-
src/bin/pg_dump/pg_dump.c                      | 28 ++++++++-----
src/bin/pg_dump/t/002_pg_dump.pl               | 19 ++++-----
src/include/catalog/catversion.h               |  2 +-
src/include/catalog/pg_namespace.dat           |  2 +-
src/pl/plperl/expected/plperl_setup.out        |  4 ++
src/pl/plperl/sql/plperl_setup.sql             |  4 ++
src/test/regress/input/tablespace.source       |  5 ++-
src/test/regress/output/tablespace.source      |  4 +-
13 files changed, 86 insertions(+), 64 deletions(-)



^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
  2021-09-10 06:39 pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
@ 2021-12-17 17:52 ` Tom Lane <tgl@sss.pgh.pa.us>
  2021-12-17 18:25   ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  1 sibling, 1 reply; 20+ messages in thread

From: Tom Lane @ 2021-12-17 17:52 UTC (permalink / raw)
  To: Noah Misch <noah@leadboat.com>; +Cc: pgsql-committers@lists.postgresql.org

Noah Misch <noah@leadboat.com> writes:
> Revoke PUBLIC CREATE from public schema, now owned by pg_database_owner.

I've just stumbled across a testing problem created by this commit:
if you try to skip the tablespace test, the rest of the run falls
over, because this bit doesn't get executed:

-- Rest of this suite can use the public schema freely.
GRANT ALL ON SCHEMA public TO public;

Skipping the tablespace test is something I've been accustomed to do
when testing replication with the standby on the same machine as the
primary, because otherwise you've got to fool with keeping the
standby from overwriting the primary's tablespaces.  This hack made
that a lot more painful.

I'm inclined to think the cleanest fix is to move this step into a
new script, say "test_setup.sql", that is scheduled by itself just
after tablespace.sql.  It's sort of annoying to fire up a psql+backend
for just one command, but perhaps there's other stuff that could be
put there too.

Another possibility is to add that GRANT to the list of stuff that
pg_regress.c does by default.  If there's actually reason for
tablespace.sql to run without that, it could revoke and re-grant
the public permissions.  This way would have the advantage of
being less likely to break other test suites.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
  2021-09-10 06:39 pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2021-12-17 17:52 ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-12-17 18:25   ` Noah Misch <noah@leadboat.com>
  2021-12-17 18:41     ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 20+ messages in thread

From: Noah Misch @ 2021-12-17 18:25 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-committers@lists.postgresql.org

On Fri, Dec 17, 2021 at 12:52:39PM -0500, Tom Lane wrote:
> Noah Misch <noah@leadboat.com> writes:
> > Revoke PUBLIC CREATE from public schema, now owned by pg_database_owner.
> 
> I've just stumbled across a testing problem created by this commit:
> if you try to skip the tablespace test, the rest of the run falls
> over, because this bit doesn't get executed:
> 
> -- Rest of this suite can use the public schema freely.
> GRANT ALL ON SCHEMA public TO public;
> 
> Skipping the tablespace test is something I've been accustomed to do
> when testing replication with the standby on the same machine as the
> primary, because otherwise you've got to fool with keeping the
> standby from overwriting the primary's tablespaces.  This hack made
> that a lot more painful.
> 
> I'm inclined to think the cleanest fix is to move this step into a
> new script, say "test_setup.sql", that is scheduled by itself just
> after tablespace.sql.

I like that solution for your use case.

> It's sort of annoying to fire up a psql+backend
> for just one command, but perhaps there's other stuff that could be
> put there too.

Yes.  The src/test/regress suite would be in a better place if one could run
most test files via a schedule containing only two files, the setup file and
the file of interest.  Adding things like the "CREATE TABLE tenk1" to the
setup file would help that.





^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
  2021-09-10 06:39 pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2021-12-17 17:52 ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Tom Lane <tgl@sss.pgh.pa.us>
  2021-12-17 18:25   ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
@ 2021-12-17 18:41     ` Tom Lane <tgl@sss.pgh.pa.us>
  2021-12-17 19:47       ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  0 siblings, 1 reply; 20+ messages in thread

From: Tom Lane @ 2021-12-17 18:41 UTC (permalink / raw)
  To: Noah Misch <noah@leadboat.com>; +Cc: pgsql-committers@lists.postgresql.org

Noah Misch <noah@leadboat.com> writes:
> On Fri, Dec 17, 2021 at 12:52:39PM -0500, Tom Lane wrote:
>> It's sort of annoying to fire up a psql+backend
>> for just one command, but perhaps there's other stuff that could be
>> put there too.

> Yes.  The src/test/regress suite would be in a better place if one could run
> most test files via a schedule containing only two files, the setup file and
> the file of interest.  Adding things like the "CREATE TABLE tenk1" to the
> setup file would help that.

If we're thinking of a generalized setup file, putting it after the
tablespace test feels pretty weird.  What was your motivation for
doing this at the end of tablespace.source rather than the start?
It doesn't look like that test in itself had any interesting
dependencies on public not being writable.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
  2021-09-10 06:39 pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2021-12-17 17:52 ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Tom Lane <tgl@sss.pgh.pa.us>
  2021-12-17 18:25   ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2021-12-17 18:41     ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-12-17 19:47       ` Noah Misch <noah@leadboat.com>
  2021-12-17 19:57         ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 20+ messages in thread

From: Noah Misch @ 2021-12-17 19:47 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-committers@lists.postgresql.org

On Fri, Dec 17, 2021 at 01:41:00PM -0500, Tom Lane wrote:
> Noah Misch <noah@leadboat.com> writes:
> > On Fri, Dec 17, 2021 at 12:52:39PM -0500, Tom Lane wrote:
> >> It's sort of annoying to fire up a psql+backend
> >> for just one command, but perhaps there's other stuff that could be
> >> put there too.
> 
> > Yes.  The src/test/regress suite would be in a better place if one could run
> > most test files via a schedule containing only two files, the setup file and
> > the file of interest.  Adding things like the "CREATE TABLE tenk1" to the
> > setup file would help that.
> 
> If we're thinking of a generalized setup file, putting it after the
> tablespace test feels pretty weird.  What was your motivation for
> doing this at the end of tablespace.source rather than the start?

I did it that way so a bit of the "make check" suite would exercise the
standard user experience.  That's a minor concern, so putting the setup file
before the tablespace file is fine.  Various contrib and TAP suites will still
test the standard user experience.

> It doesn't look like that test in itself had any interesting
> dependencies on public not being writable.

Right.





^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
  2021-09-10 06:39 pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2021-12-17 17:52 ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Tom Lane <tgl@sss.pgh.pa.us>
  2021-12-17 18:25   ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2021-12-17 18:41     ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Tom Lane <tgl@sss.pgh.pa.us>
  2021-12-17 19:47       ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
@ 2021-12-17 19:57         ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 20+ messages in thread

From: Tom Lane @ 2021-12-17 19:57 UTC (permalink / raw)
  To: Noah Misch <noah@leadboat.com>; +Cc: pgsql-committers@lists.postgresql.org

Noah Misch <noah@leadboat.com> writes:
> On Fri, Dec 17, 2021 at 01:41:00PM -0500, Tom Lane wrote:
>> If we're thinking of a generalized setup file, putting it after the
>> tablespace test feels pretty weird.  What was your motivation for
>> doing this at the end of tablespace.source rather than the start?

> I did it that way so a bit of the "make check" suite would exercise the
> standard user experience.  That's a minor concern, so putting the setup file
> before the tablespace file is fine.  Various contrib and TAP suites will still
> test the standard user experience.

Check.  I'll make it so in a little bit.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
  2021-09-10 06:39 pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
@ 2022-11-29 19:22 ` Robert Haas <robertmhaas@gmail.com>
  2022-11-29 19:31   ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Justin Pryzby <pryzby@telsasoft.com>
  2022-11-30 07:07   ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  1 sibling, 2 replies; 20+ messages in thread

From: Robert Haas @ 2022-11-29 19:22 UTC (permalink / raw)
  To: Noah Misch <noah@leadboat.com>; +Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>

On Fri, Sep 10, 2021 at 2:39 AM Noah Misch <noah@leadboat.com> wrote:
> Revoke PUBLIC CREATE from public schema, now owned by pg_database_owner.
>
> This switches the default ACL to what the documentation has recommended
> since CVE-2018-1058.  Upgrades will carry forward any old ownership and
> ACL.  Sites that declined the 2018 recommendation should take a fresh
> look.  Recipes for commissioning a new database cluster from scratch may
> need to create a schema, grant more privileges, etc.  Out-of-tree test
> suites may require such updates.

I was looking at the changes that this commit made to ddl.sgml today
and I feel that it's not quite ideal. Under "Constrain ordinary users
to user-private schemas" it first says "To implement this, first issue
<literal>REVOKE CREATE ON SCHEMA public FROM PUBLIC</literal>" and
then later says, oh but wait, you actually don't need to do that
unless you're upgrading. That seems a bit backwards to me: I think we
should talk about the current state of play first, and then add the
notes about upgrading afterwards.

Here's a proposed patch to do that.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

Attachments:

ddl-create-public-reorg.patch (application/octet-stream, 110B) download doc/src/sgml/ddl.sgml | 30 +++++++++++++++--------------- 1 file changed, 15 insertions(+), 15 deletions(-)
^ permalink raw reply [nested|flat] 20+ messages in thread

* Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
  2021-09-10 06:39 pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-29 19:22 ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
@ 2022-11-29 19:31   ` Justin Pryzby <pryzby@telsasoft.com>
  2022-11-29 19:34     ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  1 sibling, 1 reply; 20+ messages in thread

From: Justin Pryzby @ 2022-11-29 19:31 UTC (permalink / raw)
  To: Robert Haas <robertmhaas@gmail.com>; +Cc: Noah Misch <noah@leadboat.com>; pgsql-hackers@postgresql.org

On Tue, Nov 29, 2022 at 02:22:59PM -0500, Robert Haas wrote:
> Here's a proposed patch to do that.

If I'm not wrong, you message includes a diffstat but without the patch
itself.





^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
  2021-09-10 06:39 pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-29 19:22 ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-29 19:31   ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Justin Pryzby <pryzby@telsasoft.com>
@ 2022-11-29 19:34     ` Robert Haas <robertmhaas@gmail.com>
  0 siblings, 0 replies; 20+ messages in thread

From: Robert Haas @ 2022-11-29 19:34 UTC (permalink / raw)
  To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Noah Misch <noah@leadboat.com>; pgsql-hackers@postgresql.org

On Tue, Nov 29, 2022 at 2:32 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
> On Tue, Nov 29, 2022 at 02:22:59PM -0500, Robert Haas wrote:
> > Here's a proposed patch to do that.
>
> If I'm not wrong, you message includes a diffstat but without the patch
> itself.

D'oh.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

Attachments:

ddl-create-public-reorg-really.patch (application/octet-stream, 2.7K) download diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index ed034a6b1d..ee7ebb8ec5 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -3224,26 +3224,26 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC; CREATEROLE user can issue "GRANT $dbowner TO $me" and then use the database owner attack. --> <para> - Constrain ordinary users to user-private schemas. To implement this, - first issue <literal>REVOKE CREATE ON SCHEMA public FROM - PUBLIC</literal>. Then, for every user needing to create non-temporary - objects, create a schema with the same name as that user. Recall that - the default search path starts with <literal>$user</literal>, which - resolves to the user name. Therefore, if each user has a separate - schema, they access their own schemas by default. After adopting this - pattern in a database where untrusted users had already logged in, - consider auditing the public schema for objects named like objects in - schema <literal>pg_catalog</literal>. This pattern is a secure schema - usage pattern unless an untrusted user is the database owner or holds - the <literal>CREATEROLE</literal> privilege, in which case no secure + Constrain ordinary users to user-private schemas. For every user + needing to create non-temporary objects, create a schema with the same + name as that user. Recall that the default search path starts with + <literal>$user</literal>, which resolves to the user name. Therefore, + if each user has a separate schema, they access their own schemas by + default. This pattern is a secure schema usage pattern unless an + untrusted user is the database owner or holds the + <literal>CREATEROLE</literal> privilege, in which case no secure schema usage pattern exists. </para> <para> If the database originated in an upgrade from <productname>PostgreSQL</productname> 14 or earlier, - the <literal>REVOKE</literal> is essential. Otherwise, the default - configuration follows this pattern; ordinary users can create only - temporary objects until a privileged user furnishes a schema. + it is necessary to execute <literal>REVOKE CREATE ON SCHEMA public + FROM PUBLIC</literal> in order to implement this pattern, + because those versions granted the <literal>CREATE</literal> privilege + on the <literal>public</literal> schema to <literal>PUBLIC</literal>. + After this <literal>REVOKE</literal>, consider auditing the public + schema for objects named like objects in + schema <literal>pg_catalog</literal>. </para> </listitem>
^ permalink raw reply [nested|flat] 20+ messages in thread

* Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
  2021-09-10 06:39 pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-29 19:22 ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
@ 2022-11-30 07:07   ` Noah Misch <noah@leadboat.com>
  2022-11-30 13:39     ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  1 sibling, 1 reply; 20+ messages in thread

From: Noah Misch @ 2022-11-30 07:07 UTC (permalink / raw)
  To: Robert Haas <robertmhaas@gmail.com>; +Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>

On Tue, Nov 29, 2022 at 02:22:59PM -0500, Robert Haas wrote:
> On Fri, Sep 10, 2021 at 2:39 AM Noah Misch <noah@leadboat.com> wrote:
> > Revoke PUBLIC CREATE from public schema, now owned by pg_database_owner.
> >
> > This switches the default ACL to what the documentation has recommended
> > since CVE-2018-1058.  Upgrades will carry forward any old ownership and
> > ACL.  Sites that declined the 2018 recommendation should take a fresh
> > look.  Recipes for commissioning a new database cluster from scratch may
> > need to create a schema, grant more privileges, etc.  Out-of-tree test
> > suites may require such updates.
> 
> I was looking at the changes that this commit made to ddl.sgml today
> and I feel that it's not quite ideal. Under "Constrain ordinary users
> to user-private schemas" it first says "To implement this, first issue
> <literal>REVOKE CREATE ON SCHEMA public FROM PUBLIC</literal>" and
> then later says, oh but wait, you actually don't need to do that
> unless you're upgrading. That seems a bit backwards to me: I think we
> should talk about the current state of play first, and then add the
> notes about upgrading afterwards.

In general, the documentation should prefer simpler decision trees.
Especially so where the wrong choice causes no error, yet leaves a security
vulnerability.  The unconditional REVOKE has no drawbacks; it's harmless where
it's a no-op.  That was the rationale behind the current text.  Upgrades
aren't the only issue; another DBA may have changed the ACL since initdb.





^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
  2021-09-10 06:39 pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-29 19:22 ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 07:07   ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
@ 2022-11-30 13:39     ` Robert Haas <robertmhaas@gmail.com>
  2022-11-30 15:01       ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  0 siblings, 1 reply; 20+ messages in thread

From: Robert Haas @ 2022-11-30 13:39 UTC (permalink / raw)
  To: Noah Misch <noah@leadboat.com>; +Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>

On Wed, Nov 30, 2022 at 2:07 AM Noah Misch <noah@leadboat.com> wrote:
> In general, the documentation should prefer simpler decision trees.

True, but I found the current text confusing, which is also something
to consider.

-- 
Robert Haas
EDB: http://www.enterprisedb.com





^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
  2021-09-10 06:39 pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-29 19:22 ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 07:07   ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-30 13:39     ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
@ 2022-11-30 15:01       ` Noah Misch <noah@leadboat.com>
  2022-11-30 15:44         ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  0 siblings, 1 reply; 20+ messages in thread

From: Noah Misch @ 2022-11-30 15:01 UTC (permalink / raw)
  To: Robert Haas <robertmhaas@gmail.com>; +Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>

On Wed, Nov 30, 2022 at 08:39:23AM -0500, Robert Haas wrote:
> On Wed, Nov 30, 2022 at 2:07 AM Noah Misch <noah@leadboat.com> wrote:
> > In general, the documentation should prefer simpler decision trees.
> 
> True, but I found the current text confusing, which is also something
> to consider.

Could remove the paragraph about v14.  Could have that paragraph say
explicitly that the REVOKE is a no-op.  Would either of those be an
improvement?





^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
  2021-09-10 06:39 pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-29 19:22 ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 07:07   ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-30 13:39     ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 15:01       ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
@ 2022-11-30 15:44         ` Robert Haas <robertmhaas@gmail.com>
  2022-11-30 22:35           ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 20+ messages in thread

From: Robert Haas @ 2022-11-30 15:44 UTC (permalink / raw)
  To: Noah Misch <noah@leadboat.com>; +Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>

On Wed, Nov 30, 2022 at 10:01 AM Noah Misch <noah@leadboat.com> wrote:
> On Wed, Nov 30, 2022 at 08:39:23AM -0500, Robert Haas wrote:
> > On Wed, Nov 30, 2022 at 2:07 AM Noah Misch <noah@leadboat.com> wrote:
> > > In general, the documentation should prefer simpler decision trees.
> >
> > True, but I found the current text confusing, which is also something
> > to consider.
>
> Could remove the paragraph about v14.  Could have that paragraph say
> explicitly that the REVOKE is a no-op.  Would either of those be an
> improvement?

Well, I thought what I proposed was a nice improvement, but I guess if
you don't like it I'm not inclined to spend a lot of time discussing
other possibilities. If we get some opinions from more people that may
make it clearer which direction to go; if I'm the only one that
doesn't like the way it is now, it's probably not that important.

-- 
Robert Haas
EDB: http://www.enterprisedb.com





^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
  2021-09-10 06:39 pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-29 19:22 ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 07:07   ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-30 13:39     ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 15:01       ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-30 15:44         ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
@ 2022-11-30 22:35           ` Tom Lane <tgl@sss.pgh.pa.us>
  2022-11-30 22:57             ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Isaac Morland <isaac.morland@gmail.com>
  2022-11-30 22:58             ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas David G. Johnston <david.g.johnston@gmail.com>
  2022-12-01 04:32             ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-12-01 08:25             ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  0 siblings, 4 replies; 20+ messages in thread

From: Tom Lane @ 2022-11-30 22:35 UTC (permalink / raw)
  To: Robert Haas <robertmhaas@gmail.com>; +Cc: Noah Misch <noah@leadboat.com>; pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>

Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, Nov 30, 2022 at 10:01 AM Noah Misch <noah@leadboat.com> wrote:
>> Could remove the paragraph about v14.  Could have that paragraph say
>> explicitly that the REVOKE is a no-op.  Would either of those be an
>> improvement?

> Well, I thought what I proposed was a nice improvement, but I guess if
> you don't like it I'm not inclined to spend a lot of time discussing
> other possibilities. If we get some opinions from more people that may
> make it clearer which direction to go; if I'm the only one that
> doesn't like the way it is now, it's probably not that important.

Hey, I'll step up to the plate ;-)

I agree that it's confusing to tell people to do a REVOKE that might do
nothing.  A parenthetical note explaining that might help, but the text
is pretty dense already, so really I'd rather have that info in a
separate para.

Also, I'd like to structure things so that the first para covers what
you need to know in a clean v15+ installation, and details that only
apply in upgrade scenarios are in the second para.  The upgrade scenario
is going to be interesting to fewer and fewer people over time, so let's
not clutter the lede with it.

So maybe about like this?

    Constrain ordinary users to user-private schemas.  To implement
    this pattern, for every user needing to create non-temporary
    objects, create a schema with the same name as that user.  (Recall
    that the default search path starts with $user, which resolves to
    the user name. Therefore, if each user has a separate schema, they
    access their own schemas by default.)  Also ensure that no other
    schemas have public CREATE privileges.  This pattern is a secure
    schema usage pattern unless an untrusted user is the database
    owner or holds the CREATEROLE privilege, in which case no secure
    schema usage pattern exists.

    In PostgreSQL 15 and later, the default configuration supports
    this usage pattern.  In prior versions, or when using a database
    that has been upgraded from a prior version, you will need to
    remove the public CREATE privilege from the public schema (issue
    REVOKE CREATE ON SCHEMA public FROM PUBLIC).  Then consider
    auditing the public schema for objects named like objects in
    schema pg_catalog.

This is close to what Robert wrote, but not exactly the same,
so probably it will make neither of you happy ;-)

BTW, is "create a schema with the same name" sufficient detail?
You have to either make it owned by that user, or explicitly
grant CREATE permission on it.  I'm not sure if that detail
belongs here, but it feels like maybe it does.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
  2021-09-10 06:39 pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-29 19:22 ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 07:07   ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-30 13:39     ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 15:01       ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-30 15:44         ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 22:35           ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-11-30 22:57             ` Isaac Morland <isaac.morland@gmail.com>
  3 siblings, 0 replies; 20+ messages in thread

From: Isaac Morland @ 2022-11-30 22:57 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Robert Haas <robertmhaas@gmail.com>; Noah Misch <noah@leadboat.com>; pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>

On Wed, 30 Nov 2022 at 17:35, Tom Lane <tgl@sss.pgh.pa.us> wrote:

BTW, is "create a schema with the same name" sufficient detail?
> You have to either make it owned by that user, or explicitly
> grant CREATE permission on it.  I'm not sure if that detail
> belongs here, but it feels like maybe it does.


It might be worth mentioning AUTHORIZATION. The easiest way to create an
appropriately named schema for a user is "CREATE SCHEMA AUTHORIZATION
username".

^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
  2021-09-10 06:39 pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-29 19:22 ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 07:07   ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-30 13:39     ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 15:01       ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-30 15:44         ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 22:35           ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-11-30 22:58             ` David G. Johnston <david.g.johnston@gmail.com>
  3 siblings, 0 replies; 20+ messages in thread

From: David G. Johnston @ 2022-11-30 22:58 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Robert Haas <robertmhaas@gmail.com>; Noah Misch <noah@leadboat.com>; pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>

On Wed, Nov 30, 2022 at 3:35 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

>
> BTW, is "create a schema with the same name" sufficient detail?
> You have to either make it owned by that user, or explicitly
> grant CREATE permission on it.  I'm not sure if that detail
> belongs here, but it feels like maybe it does.
>
>
I'd mention the ownership variant and suggest using the AUTHORIZATION
clause, with an explicit example.

CREATE SCHEMA role_name AUTHORIZATION role_name;

David J.

^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
  2021-09-10 06:39 pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-29 19:22 ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 07:07   ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-30 13:39     ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 15:01       ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-30 15:44         ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 22:35           ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-12-01 04:32             ` Robert Haas <robertmhaas@gmail.com>
  3 siblings, 0 replies; 20+ messages in thread

From: Robert Haas @ 2022-12-01 04:32 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Noah Misch <noah@leadboat.com>; pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>

On Wed, Nov 30, 2022 at 5:35 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Also, I'd like to structure things so that the first para covers what
> you need to know in a clean v15+ installation, and details that only
> apply in upgrade scenarios are in the second para.  The upgrade scenario
> is going to be interesting to fewer and fewer people over time, so let's
> not clutter the lede with it.

Right, that was my main feeling about this.

> So maybe about like this?
>
>     Constrain ordinary users to user-private schemas.  To implement
>     this pattern, for every user needing to create non-temporary
>     objects, create a schema with the same name as that user.  (Recall
>     that the default search path starts with $user, which resolves to
>     the user name. Therefore, if each user has a separate schema, they
>     access their own schemas by default.)  Also ensure that no other
>     schemas have public CREATE privileges.  This pattern is a secure
>     schema usage pattern unless an untrusted user is the database
>     owner or holds the CREATEROLE privilege, in which case no secure
>     schema usage pattern exists.
>
>     In PostgreSQL 15 and later, the default configuration supports
>     this usage pattern.  In prior versions, or when using a database
>     that has been upgraded from a prior version, you will need to
>     remove the public CREATE privilege from the public schema (issue
>     REVOKE CREATE ON SCHEMA public FROM PUBLIC).  Then consider
>     auditing the public schema for objects named like objects in
>     schema pg_catalog.
>
> This is close to what Robert wrote, but not exactly the same,
> so probably it will make neither of you happy ;-)

I haven't looked at how it's different from what I wrote exactly, but
it seems fine to me.

-- 
Robert Haas
EDB: http://www.enterprisedb.com





^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
  2021-09-10 06:39 pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-29 19:22 ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 07:07   ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-30 13:39     ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 15:01       ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-30 15:44         ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 22:35           ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-12-01 08:25             ` Noah Misch <noah@leadboat.com>
  2022-12-01 11:16               ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Alvaro Herrera <alvherre@alvh.no-ip.org>
  3 siblings, 1 reply; 20+ messages in thread

From: Noah Misch @ 2022-12-01 08:25 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Robert Haas <robertmhaas@gmail.com>; pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>

On Wed, Nov 30, 2022 at 05:35:01PM -0500, Tom Lane wrote:
> Also, I'd like to structure things so that the first para covers what
> you need to know in a clean v15+ installation, and details that only
> apply in upgrade scenarios are in the second para.  The upgrade scenario
> is going to be interesting to fewer and fewer people over time, so let's
> not clutter the lede with it.
> 
> So maybe about like this?
> 
>     Constrain ordinary users to user-private schemas.  To implement
>     this pattern, for every user needing to create non-temporary
>     objects, create a schema with the same name as that user.  (Recall
>     that the default search path starts with $user, which resolves to
>     the user name. Therefore, if each user has a separate schema, they
>     access their own schemas by default.)  Also ensure that no other
>     schemas have public CREATE privileges.  This pattern is a secure
>     schema usage pattern unless an untrusted user is the database
>     owner or holds the CREATEROLE privilege, in which case no secure
>     schema usage pattern exists.

This is free from the problem found in ddl-create-public-reorg-really.patch.
However, the word "other" doesn't belong there.  (The per-user schemas should
not have public CREATE privilege.)  I would also move that same sentence up
front, like this:

    Constrain ordinary users to user-private schemas.  To implement this
    pattern, first ensure that no schemas have public CREATE privileges.
    Then, for every user needing to create non-temporary objects, create a
    schema with the same name as that user.  (Recall that the default search
    path starts with $user, which resolves to the user name. Therefore, if
    each user has a separate schema, they access their own schemas by
    default.)  This pattern is a secure schema usage pattern unless an
    untrusted user is the database owner or holds the CREATEROLE privilege, in
    which case no secure schema usage pattern exists.

With that, I think you have improved on the status quo.  Thanks.

>     In PostgreSQL 15 and later, the default configuration supports
>     this usage pattern.  In prior versions, or when using a database
>     that has been upgraded from a prior version, you will need to
>     remove the public CREATE privilege from the public schema (issue
>     REVOKE CREATE ON SCHEMA public FROM PUBLIC).  Then consider
>     auditing the public schema for objects named like objects in
>     schema pg_catalog.

> BTW, is "create a schema with the same name" sufficient detail?
> You have to either make it owned by that user, or explicitly
> grant CREATE permission on it.  I'm not sure if that detail
> belongs here, but it feels like maybe it does.

Maybe.  Failing to GRANT that will yield a clear error when the user starts
work, so it's not critical to explain here.





^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
  2021-09-10 06:39 pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-29 19:22 ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 07:07   ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-30 13:39     ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 15:01       ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-30 15:44         ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 22:35           ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Tom Lane <tgl@sss.pgh.pa.us>
  2022-12-01 08:25             ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
@ 2022-12-01 11:16               ` Alvaro Herrera <alvherre@alvh.no-ip.org>
  2022-12-01 14:24                 ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 20+ messages in thread

From: Alvaro Herrera @ 2022-12-01 11:16 UTC (permalink / raw)
  To: Noah Misch <noah@leadboat.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Robert Haas <robertmhaas@gmail.com>; pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>

On 2022-Dec-01, Noah Misch wrote:

> This is free from the problem found in ddl-create-public-reorg-really.patch.
> However, the word "other" doesn't belong there.  (The per-user schemas should
> not have public CREATE privilege.)  I would also move that same sentence up
> front, like this:
> 
>     Constrain ordinary users to user-private schemas.  To implement this
>     pattern, first ensure that no schemas have public CREATE privileges.
>     Then, for every user needing to create non-temporary objects, create a
>     schema with the same name as that user.  (Recall that the default search
>     path starts with $user, which resolves to the user name. Therefore, if
>     each user has a separate schema, they access their own schemas by
>     default.)  This pattern is a secure schema usage pattern unless an
>     untrusted user is the database owner or holds the CREATEROLE privilege, in
>     which case no secure schema usage pattern exists.

+1 LGTM

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/





^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
  2021-09-10 06:39 pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-29 19:22 ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 07:07   ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-30 13:39     ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 15:01       ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-11-30 15:44         ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Robert Haas <robertmhaas@gmail.com>
  2022-11-30 22:35           ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Tom Lane <tgl@sss.pgh.pa.us>
  2022-12-01 08:25             ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
  2022-12-01 11:16               ` Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2022-12-01 14:24                 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 20+ messages in thread

From: Tom Lane @ 2022-12-01 14:24 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Noah Misch <noah@leadboat.com>; Robert Haas <robertmhaas@gmail.com>; pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On 2022-Dec-01, Noah Misch wrote:
>> This is free from the problem found in ddl-create-public-reorg-really.patch.
>> However, the word "other" doesn't belong there.  (The per-user schemas should
>> not have public CREATE privilege.)  I would also move that same sentence up
>> front, like this:
>> 
>> Constrain ordinary users to user-private schemas.  To implement this
>> pattern, first ensure that no schemas have public CREATE privileges.
>> Then, for every user needing to create non-temporary objects, create a
>> schema with the same name as that user.  (Recall that the default search
>> path starts with $user, which resolves to the user name. Therefore, if
>> each user has a separate schema, they access their own schemas by
>> default.)  This pattern is a secure schema usage pattern unless an
>> untrusted user is the database owner or holds the CREATEROLE privilege, in
>> which case no secure schema usage pattern exists.

> +1 LGTM

Sounds good.  I'll make it so in a bit.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 20+ messages in thread


end of thread, other threads:[~2022-12-01 14:24 UTC | newest]

Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-09-10 06:39 pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Noah Misch <noah@leadboat.com>
2021-12-17 17:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-12-17 18:25   ` Noah Misch <noah@leadboat.com>
2021-12-17 18:41     ` Tom Lane <tgl@sss.pgh.pa.us>
2021-12-17 19:47       ` Noah Misch <noah@leadboat.com>
2021-12-17 19:57         ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-29 19:22 ` Robert Haas <robertmhaas@gmail.com>
2022-11-29 19:31   ` Justin Pryzby <pryzby@telsasoft.com>
2022-11-29 19:34     ` Robert Haas <robertmhaas@gmail.com>
2022-11-30 07:07   ` Noah Misch <noah@leadboat.com>
2022-11-30 13:39     ` Robert Haas <robertmhaas@gmail.com>
2022-11-30 15:01       ` Noah Misch <noah@leadboat.com>
2022-11-30 15:44         ` Robert Haas <robertmhaas@gmail.com>
2022-11-30 22:35           ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-30 22:57             ` Isaac Morland <isaac.morland@gmail.com>
2022-11-30 22:58             ` David G. Johnston <david.g.johnston@gmail.com>
2022-12-01 04:32             ` Robert Haas <robertmhaas@gmail.com>
2022-12-01 08:25             ` Noah Misch <noah@leadboat.com>
2022-12-01 11:16               ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-12-01 14:24                 ` Tom Lane <tgl@sss.pgh.pa.us>

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