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
