From: Antonin Houska <ah@cybertec.at>
To: Mark Dilger <mark.dilger@enterprisedb.com>
cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
    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: <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>
Comments: In-reply-to Mark Dilger <mark.dilger@enterprisedb.com>
   message dated "Fri, 04 Nov 2022 10:17:17 -0700."
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <89891.1667583451.1@antos>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 04 Nov 2022 18:37:31 +0100
Message-ID: <89892.1667583451@antos>
Archived-At: <https://www.postgresql.org/message-id/89892.1667583451%40antos>
Precedence: bulk

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 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.
> > =

> > 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.
> > =

> > Of course a question is what to do if the replication user already has=
 that
> > privilege on some columns: either the CREATE SUBSCRIPTION command shou=
ld raise
> > ERROR, or we should introduce a new privilege (e.g. ACL_SELECT_PUB) fo=
r this
> > purpose, which would effectivelly be ACL_SELECT, but hidden from the u=
sers of
> > GRANT / REVOKE.
> > =

> > If this approach was taken, the USAGE privilege on schema would need t=
o be
> > granted / revoked in a similar way.
> =

> When you talk about a user needing to have privileges, it sounds like yo=
u
> mean privileges on the publishing database.  But then you talk about CRE=
ATE
> 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 use=
r
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