Re: [17] CREATE SUBSCRIPTION ... SERVER

On Fri, 2024-01-12 at 17:17 -0800, Jeff Davis wrote:
> I think 0004 needs a bit more work, so I'm leaving it off for now,
> but
> I'll bring it back in the next patch set.

Here's the next patch set. 0001 - 0003 are mostly the same with some
improved error messages and some code fixes. I am looking to start
committing 0001 - 0003 soon, as they have received some feedback
already and 0004 isn't required for the earlier patches to be useful.

0004 could use more discussion. The purpose is to split the privileges
of pg_create_subscription into two: pg_create_subscription, and
pg_create_connection. By separating the privileges, it's possible to
allow someone to create/manage subscriptions to a predefined set of
foreign servers (on which they have USAGE privileges) without allowing
them to write an arbitrary connection string.

The reasoning behind the separation is that creating a connection
string has different and more nuanced security implications than
creating a subscription (cf. extensive discussion[1] related to the
password_required setting on a subscription).

By default, pg_create_subscription is a member of pg_create_connection,
so there's no change/break of the default behavior. But administrators
who want the privileges to be separated can simply "REVOKE
pg_create_connection FROM pg_create_subscription".

Given that CREATE SUBSCRIPTION ... SERVER works on a server of any FDW,
we would also need to protect against someone making using an
unexpected FDW (with no validation or different validation) to
construct a foreign server with malicious connection settings. To do
so, I added to the grammar "CREATE SERVER ... FOR SUBSCRIPTION" (and a
boolean catalog entry in pg_foreign_server) that can only be set by a
member of pg_create_connection.

There was some resistance[2] to adding more grammar/catalog impact than
necessary, so I'm not sure if others think this is the right approach.
The earlier patches are still worth it without 0004, but I do think the
idea of separating the privileges is useful and it would be nice to
find an agreeable solution to do so. At least with the 0004, the
approach is a bit more direct.

Regards,
Jeff Davis

[1]
https://www.postgresql.org/message-id/9DFC88D3-1300-4DE8-ACBC-4CEF84399A53%40enterprisedb.com

[2]
https://www.postgresql.org/message-id/172273.1693403385%40sss.pgh.pa.us