Re: Non-superuser subscription owners

On Tue, 2023-01-24 at 17:00 -0500, Robert Haas wrote:
> It seems to me that the relevant
> question isn't "are the servers tightly coupled?" but rather "could
> some user make a mess if we let them use any arbitrary connection
> string?".

The split I created is much easier for an admin to answer: is the list
of servers finite, or can users connect to new servers the admin isn't
even aware of? If it's a finite list, I feel there's a much better
solution with both security and UI benefits.

With your question, I'm not entirely clear if that's a question that we
already have an answer for (require a password parameter), or that we
will answer in this thread, or that the admin will answer.

> unless you've got users who are really shady 

Or compromised. Unfortunately, a role that's creating subscriptions has
a lot of surface area for escalation-of-privilege attacks, because they
have to trust all the owners of all the tables the subscriptions write
to.

> I think that you're basically trying to make an argument that some
> sort of complex outbound connection filtering is mandatory

No, I'm not asking for the validation to be more complex.

I believe use case (A) is a substantial use case, and I'd like to leave
space in the user interface to solve it a much better way than
connection string validation can offer. But to solve use case (A), we
need to separate the ability to create a subscription from the ability
to create a connection string.

Right now you see those as the same because they are done at the same
time in the same command; but I don't see it that way, because I had
plans to allow a variant of CREATE SUBSCRIPTION that uses foreign
servers. That plan would be consistent with dblink and postgres_fdw,
which already allow specifying foreign servers.

I propose that we have two predefined roles: pg_create_subscription,
and pg_create_connection. If creating a subscription with a connection
string, you'd need to be a member of both roles. But to create a
subscription with a server object, you'd just need to be a member of
pg_create_subscription and have the USAGE privilege on the server
object.

--
Jeff Davis
PostgreSQL Contributor Team - AWS