From: Antonin Houska <ah@cybertec.at>
To: Mark Dilger <mark.dilger@enterprisedb.com>
Cc: 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: Fri, 04 Nov 2022 18:37:31 +0100
Message-ID: <89892.1667583451@antos> (raw)
In-Reply-To: <30297BD0-7EBA-48AB-885C-2B5BAAE7619B@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>
<30297BD0-7EBA-48AB-885C-2B5BAAE7619B@enterprisedb.com>
Mark Dilger <mark.dilger@enterprisedb.com> wrote:
> > On Nov 4, 2022, at 12:28 AM, Antonin Houska <ah@cybertec.at> 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.
> >
> > 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.
> >
> > Of course a question is what to do if the replication user already has that
> > privilege on some columns: either the CREATE SUBSCRIPTION command should raise
> > ERROR, or we should introduce a new privilege (e.g. ACL_SELECT_PUB) for this
> > purpose, which would effectivelly be ACL_SELECT, but hidden from the users of
> > GRANT / REVOKE.
> >
> > If this approach was taken, the USAGE privilege on schema would need to be
> > granted / revoked in a similar way.
>
> When you talk about a user needing to have privileges, it sounds like you
> mean privileges on the publishing database. But then you talk about CREATE
> SUBSCRIPTION granting privileges, which would necessarily be on the
> subscriber database. Can you clarify what you have in mind?
Right, the privileges need to be added on the publishing side, but the user
that needs those privileges is specified on the subscription side. I didn't
think much in detail how it would work. The "subscription user" certainly
cannot connect to the publisher database and add grant the privileges to
itself. Perhaps some of the workers on the publisher side could do it on
startup.
--
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, mark.dilger@enterprisedb.com, peter.eisentraut@enterprisedb.com, euler@eulerto.com, amit.kapila16@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Privileges on PUBLICATION
In-Reply-To: <89892.1667583451@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