From: Antonin Houska <ah@cybertec.at>
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Cc: Euler Taveira <euler@eulerto.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Privileges on PUBLICATION
Date: Mon, 14 Nov 2022 12:07:48 +0100
Message-ID: <3862.1668424068@antos> (raw)
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>
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 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: ah@cybertec.at, peter.eisentraut@enterprisedb.com, euler@eulerto.com, amit.kapila16@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Privileges on PUBLICATION
In-Reply-To: <3862.1668424068@antos>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox