Re: Privileges on PUBLICATION

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.

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