Horton

Re: public schema default ACL

Noah Misch <noah@leadboat.com>

View thread View on postgr.es
From: Noah Misch <noah@leadboat.com>
To: Bruce Momjian <bruce@momjian.us>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Robert Haas <robertmhaas@gmail.com>, Peter Eisentraut <peter.eisentraut@2ndquadrant.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>, Stephen Frost <sfrost@snowman.net>, Magnus Hagander <magnus@hagander.net>, "David G. Johnston" <david.g.johnston@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: 2020-11-13T02:36:39Z
Lists: pgsql-hackers 
On Mon, Nov 09, 2020 at 02:56:53PM -0500, Bruce Momjian wrote:
> On Mon, Nov  2, 2020 at 11:05:15PM -0800, Noah Misch wrote:
> > My plan is for the default to become:
> > 
> >   GRANT USAGE ON SCHEMA public TO PUBLIC;
> >   ALTER SCHEMA public OWNER TO DATABASE_OWNER;  -- new syntax
> 
> Seems it would be better to create a predefined role that owns the
> public schema, or at least has create permission for the public schema
> --- that way, when you are creating a role, you can decide if the role
> should have creation permissions in the public schema, rather than
> having people always using the database owner for this purpose.

Defaulting to a specific predefined role empowers the role's members in all
databases simultaneously.  Folks who want it like that can create a role and
issue "ALTER SCHEMA public OWNER TO that_role" in template1.  What's the
better default?  I think that depends on whether you regard this schema as a
per-database phenomenon or a per-cluster phenomenon.

Commits

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

  2. Document security implications of search_path and the public schema.