Re: Privileges on PUBLICATION

> On Nov 4, 2022, at 12:28 AM, Antonin Houska <ah(at)cybertec(dot)at> wrote:
>
> I thought about the whole concept a bit more and I doubt if the PUBLICATION
> privilege is the best approach. In particular, the user specified in CREATE
> SUBSCRIPTION ... CONNECTION ... (say "subscription user") needs to have SELECT
> privilege on the tables replicated. So if the DBA excludes some columns from
> the publication's column list and sets the (publication) privileges in such a
> way that the user cannot get the column values via other publications, the
> user still can connect to the database directly and get values of the excluded
> columns.
>
> As an alternative to the publication privileges, I think that the CREATE
> SUBSCRIPTION command could grant ACL_SELECT automatically to the subscription
> user on the individual columns contained in the publication column list, and
> DROP SUBSCRIPTION would revoke that privilege.
>
> Of course a question is what to do if the replication user already has that
> privilege on some columns: either the CREATE SUBSCRIPTION command should raise
> ERROR, or we should introduce a new privilege (e.g. ACL_SELECT_PUB) for this
> purpose, which would effectivelly be ACL_SELECT, but hidden from the users of
> GRANT / REVOKE.
>
> If this approach was taken, the USAGE privilege on schema would need to be
> granted / revoked in a similar way.

When you talk about a user needing to have privileges, it sounds like you mean privileges on the publishing database. But then you talk about CREATE SUBSCRIPTION granting privileges, which would necessarily be on the subscriber database. Can you clarify what you have in mind?

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company