Re: [PATCH] Simplify permission checking logic in user.c - Mailing list pgsql-hackers

Greetings,

* Paul Martinez (paulmtz@google.com) wrote:
> You've identified exactly the problem we're running into -- we want to
> allow customers, who aren't superusers, to create replication roles.

This is also where it's probably useful to think about what the impact
of that is- after all, since it seems you're considering this for the
purposes of logical replication, it'd make sense to draw your attention
to this:

https://www.postgresql.org/docs/10/logical-replication-security.html

which talks about the rather serious consequences, from a security
perspective, of allowing non-superusers access to logical replication
and modifying publication and subscription sets and tables.  Perhaps
these are things you've already addressed, but we'd need to address them
in core to have such a capability make sense.

> We haven't seriously considered what some sort of general functionality
> would look like to support our managed Postgres use-cases, but here's a
> rough sketch of what we're trying to accomplish with roles and permissions:
>
> - We, ideally, want to give our customers access to as much of Postgres'
>   functionality as possible, including SUPERUSER capabilities
>
> - But we do not want customers to have access to the underlying VM or
>   filesystem

Certainly, the above two things are in pretty direct conflict, as you've
discovered. :)

That said, we've started working in this direction with the initial (or
default) role system, as a way to express capabilities which can then be
inherited or GRANT'd by roles with appropriate rights to other roles.
In general, it seems like that's a better approach and that it'd make
sense to move away from the role attributes.

> - There are certain parts of the system (i.e., certain databases, tables,
>   individual rows in catalog tables, etc.) that we want to manage and we
>   can't allow customers to modify these at all. Examples include:
>   - Our own SUPERUSER role that we use to connect to the cluster; customers
>     shouldn't be able to modify this role at all

Roles, in general, can't modify other roles (except with CREATEROLE,
which is more an argument to get rid of it and not use it than anything
else, imv).

>   - Replication slots used for managed replication shouldn't be able to be
>     modified by customers (even if they have the REPLICATION attribute)

That's an interesting example and one which probably means we need to
have some kind of ownership/privilege model added to replication slots.

> - We want to prevent customers from depending on any data we choose to store
>   in other database, as that limits our flexibility to make future changes
>   - Notably this means we only can support logical replication, and not
>     physical replication.
>

Being able to give users the ability to use logical replication for a
subset of the system is certainly a capability that I think we'd like to
have.

> I suppose this could be roughly summarized as "90% of a superuser, but also
> still obeys SQL privileges for objects created by someone else".

I don't really think that it makes sense to think of it that way-
superuser is superuser, everything is "user with certain privileges
granted to it" and we should be thinking of it that way.

Thanks,

Stephen