Re: Privileges on PUBLICATION

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.

[1] https://www.postgresql.org/docs/current/logical-replication-security.html

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