Re: public schema default ACL - Mailing list pgsql-hackers

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> > On Mon, Nov 2, 2020 at 5:51 AM Peter Eisentraut
> > <peter.eisentraut@2ndquadrant.com> wrote:
> >> I'm not convinced, however, that this would would really move the needle
> >> in terms of the general security-uneasiness about the public schema and
> >> search paths.  AFAICT, in any of your proposals, the default would still
> >> be to have the public schema world-writable and in the path.
>
> > Noah's proposed change to initdb appears to involve removing CREATE
> > permission by default, so I don't think this is true.

The original proposal didn't include that change (I don't think anyway,
the change you're referring to seems to have come after most of the
folks had voiced opinions..?), so I can understand someone being unclear
on this point.  Admittedly, I suspect most folks on this thread assumed,
as I did, that Noah was proposing to remove CREATE permission from
public on the public schema, and Peter was actually responding to the
email which included Noah's suggest initdb change, so it should have
been clear at that point anyway.  The only other relevant vote, I
believe, was from Magnus, so might be good to just make sure he's also
in favor of (b)(3)(X) with the understanding that it also involves
removing CREATE rights from the public schema from the public role.

(there are definitely days when I wish we didn't have a public schema,
simply because it would result in 'public' only ever meaning 'the
special role called public' ...)

> I assume that means removing *public* CREATE permissions, not the
> owner's (which'd be the DB owner with the proposed changes).

Yes, that's correct.

> > It's hard to predict how many users that might inconvenience, but I
> > suppose it's probably a big number. On the other hand, the only
> > alternative is to continue shipping a configuration that, by default,
> > is potentially insecure. It's hard to decide which thing we should
> > care more about.
>
> Yeah.  The thing is, if we make it harder to create stuff in "public",
> that's going to result in the path-of-least-resistance being to run
> everything as the DB owner.  Which is better than running everything as
> superuser (at least if DB owner != postgres), but still not exactly great.
> Second least difficult thing is to re-grant public CREATE permissions,
> putting things right back where they were.
>
> I'm not sure how far we can expect to move things without creating a
> bad on-boarding experience.  The folks who actually need cross-user
> security already know what they have to do (or if not, that's a docs
> problem not a code problem).  I'm inclined to think that first-time
> users do not need that, though.

This proposal strikes me as the right balance between having a decent
on-boarding experience for new users, who are likely to be using
superuser or DB owner from the start because they just want to get in
and look at things and play with PG, while still meaningfully moving us
away from having a world-writable schema in the default search path.

At least from seeing the users that start out with PG and then come to
the Slack or IRC channel asking questions, the on-boarding experience
today typically consists of 'apt install postgresql' and then complaints
that they aren't able to figure out how to log into PG (often asking
about what the default password is to log in as 'postgres', or why the
system is saying 'role "root" does not exist').  Once a user gets to the
point of understanding or wanting to create other roles in the system,
saying they need to create a schema for that role if they want it to be
able to create objects (just like a user needing a home directory)
doesn't seem likely to be all that unexpected.

Where we could possibly help in this regard might be to add some syntax
to CREATE ROLE to have it create a schema for the role too- this would
help in a couple of ways: we could give new users a single command to
get going with being able to create objects in a safe way, for their
user, and we would get information about schemas included in the
CREATE ROLE documentation, which doesn't say anything about schemas
currently.

> What potentially could move the needle is separate search paths for
> relation lookup and function/operator lookup.  We have sort of stuck
> our toe in that pond already by discriminating against pg_temp for
> function/operator lookup, but we could make that more formalized and
> controllable if there were distinct settings.  I'm not sure offhand
> how much of a compatibility problem that produces.

While I agree with the general idea of giving users more granularity
when it comes to what objects are allowed to be created by users, and
where, and how objects are looked up, I really don't think this would
end up being a sufficiently complete answer to a world-writable public
schema.  You don't have to be able to create functions or operators in
the public schema to make things dangerous for some other user poking
around at the tables or views that you are allowed to create there.

Thanks,

Stephen