Re: Privileges on PUBLICATION

Antonin Houska <ah(at)cybertec(dot)at> wrote:

> Antonin Houska <ah(at)cybertec(dot)at> wrote:
>
> > Peter Eisentraut <peter(dot)eisentraut(at)enterprisedb(dot)com> wrote:
> >
> > > On 04.11.22 08:28, Antonin Houska 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.
> > >
> > > Why are the SELECT privileges needed? Maybe that's something to think about
> > > and maybe change.
> >
> > I haven't noticed an explanation in comments nor did I search in the mailing
> > list archives, but the question makes sense: the REPLICATION attribute of a
> > role is sufficient for streaming replication, so why should the logical
> > replication require additional privileges?
> >
> > Technically the SELECT privilege is needed because the sync worker does
> > actually execute SELECT query on the published tables. However, I realize now
> > that it's not checked by the output plugin. Thus if SELECT is revoked from the
> > "subscription user" after the table has been synchronized, the replication
> > continues to work. So the necessity for the SELECT privilege might be an
> > omission rather than a design choice. (Even the documentation says that the
> > SELECT privilege is needed only for the initial synchronization [1], however
> > it does not tell why.)
> >
> > > > 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.
> > >
> > > I think that approach is weird and unusual. Privileges and object creation
> > > should be separate operations.
> >
> > ok. Another approach would be to skip the check for the SELECT privilege (as
> > well as the check for the USAGE privilege on the corresponding schema) if
> > given column is being accessed via a publication which has it on its column
> > list and if the subscription user has the USAGE privilege on that publication.
> >
> > So far I wasn't sure if we can do that because, if pg_upgrade grants the USAGE
> > privilege on all publications to the "public" role, the DBAs who relied on the
> > SELECT privileges might not notice that any role having the REPLICATION
> > attribute can access all the published tables after the upgrade. (pg_upgrade
> > can hardly do anything else because it has no information on the "subscription
> > users", so it cannot convert the SELECT privilege on tables to the USAGE
> > privileges on publications.)
> >
> > But now that I see that the logical replication doesn't check the SELECT
> > privilege properly anyway, I think we can get rid of it.
>
> The attached version tries to do that - as you can see in 0001, the SELECT
> privilege is not required for the walsender process.
>
> I also added PUBLICATION_NAMES option to the COPY TO command so that the
> publisher knows which publications are subject to the ACL check. Only data of
> those publications are returned to the subscriber. (In the previous patch
> version the ACL checks were performed on the subscriber side, but I that's not
> ideal in terms of security.)
>
> I also added the regression tests for publications, enhanced psql (the \dRp+
> command) so that it displays the publication ACL and added a few missing
> pieces of documentation.
>

This is v4. The patch had to be rebased due to the commit 369f09e420.

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