From: Antonin Houska <ah@cybertec.at>
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
cc: Euler Taveira <euler@eulerto.com>,
    Amit Kapila <amit.kapila16@gmail.com>,
    PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Privileges on PUBLICATION
In-reply-to: <043c52a4-c001-0201-94fc-305dffb8313e@enterprisedb.com>
References: <20330.1652105397@antos>
 <8a00e0e1-dc4e-422c-9dcf-664b16b18625@www.fastmail.com>
 <CAA4eK1Lya6BoMa1m8cTZrQ8KvABQpn3EsHDKRurz8UJeHsGnZw@mail.gmail.com>
 <2810.1652171844@antos>
 <d96103fe-99e2-4119-bd76-952d326b7539@www.fastmail.com>
 <5859.1652423797@antos>
 <e6025f13-fefa-4ff2-9364-24f4c77fcf7a@www.fastmail.com>
 <57022.1652867097@antos>
 <4c420c17-8570-4b52-b7f6-9ce3f53d7ef9@www.fastmail.com>
 <14292.1655733695@antos.home>
 <746c9a65-e2cc-2573-a50e-ab5ea884f680@enterprisedb.com>
 <1537.1667454192@antos>
 <a7b95f74-e3e3-adae-b472-2e8d24a4e4b8@enterprisedb.com>
 <2497.1667546930@antos>
 <043c52a4-c001-0201-94fc-305dffb8313e@enterprisedb.com>
Comments: In-reply-to Peter Eisentraut <peter.eisentraut@enterprisedb.com>
   message dated "Fri, 11 Nov 2022 15:08:16 +0100."
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3861.1668424068.1@antos>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 14 Nov 2022 12:07:48 +0100
Message-ID: <3862.1668424068@antos>
Archived-At: <https://www.postgresql.org/message-id/3862.1668424068%40antos>
Precedence: bulk

Peter Eisentraut <peter.eisentraut@enterprisedb.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 PUBLIC=
ATION
> > privilege is the best approach. In particular, the user specified in C=
REATE
> > SUBSCRIPTION ... CONNECTION ... (say "subscription user") needs to hav=
e SELECT
> > privilege on the tables replicated. So if the DBA excludes some column=
s 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 a=
bout
> and maybe change.

I haven't noticed an explanation in comments nor did I search in the maili=
ng
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 th=
e
SELECT privilege is needed only for the initial synchronization [1], howev=
er
it does not tell why.)

> > As an alternative to the publication privileges, I think that the CREA=
TE
> > SUBSCRIPTION command could grant ACL_SELECT automatically to the subsc=
ription
> > user on the individual columns contained in the publication column lis=
t, and
> > DROP SUBSCRIPTION would revoke that privilege.
> =

> I think that approach is weird and unusual.  Privileges and object creat=
ion
> 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 colum=
n
list and if the subscription user has the USAGE privilege on that publicat=
ion.

So far I wasn't sure if we can do that because, if pg_upgrade grants the U=
SAGE
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_upgra=
de
can hardly do anything else because it has no information on the "subscrip=
tion
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.h=
tml

-- =

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