Re: public schema default ACL

On Mon, Aug 10, 2020 at 10:21:06AM +0200, Magnus Hagander wrote:
> On Thu, Aug 6, 2020 at 3:34 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > Not sure how much it happens in these days of docker and containers, but
> > certainly it was common at one point to have home directories
> > automatically created on login. There's one particularly large
> > difference here though- home directories go in /home/ (or whatever) and
> > have a specific namespace, which our schemas don't. That is to say, if
> > someone has CREATE rights on the database they can create an 'sfrost'
> > schema that they own, dump whatever they want into it, and then it's in
> > my default search_path when I log in, even if this feature to
> > auto-create role schemas exists. Sure, you could argue that in the unix
> > case, that would have been an 'admin' user to be able to make a
> > directory in /home/, but we haven't got any other way to make
> > 'directories', so perhaps the analogy just doesn't fit close enough.
>
> Yeah, the fact that a owner can just create a schema called "postgres" and
> thereby sticking things in the search path of postgres is not great. And
> that's not fixed by changing how "public" works, per any of the suggested
> methods I think. Only the database owner can do mean things there, but
> database owner != superuser (at least in theory).

https://www.postgresql.org/docs/devel/ddl-schemas.html#DDL-SCHEMAS-PATTERNS
does document the power of untrusted database owners. Unfortunately, I've not
seen or thought of a specification of database owner powers that included
enough power to be useful yet not enough power to cause mischief.