Re: Privileges on PUBLICATION

Mark Dilger <mark(dot)dilger(at)enterprisedb(dot)com> wrote:

> > 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?

Right, the privileges need to be added on the publishing side, but the user
that needs those privileges is specified on the subscription side. I didn't
think much in detail how it would work. The "subscription user" certainly
cannot connect to the publisher database and add grant the privileges to
itself. Perhaps some of the workers on the publisher side could do it on
startup.

--
Antonin Houska
Web: https://www.cybertec-postgresql.com