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: <a7b95f74-e3e3-adae-b472-2e8d24a4e4b8@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>
Comments: In-reply-to Peter Eisentraut <peter.eisentraut@enterprisedb.com>
   message dated "Thu, 03 Nov 2022 11:49:51 -0400."
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2496.1667546930.1@antos>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 04 Nov 2022 08:28:50 +0100
Message-ID: <2497.1667546930@antos>
Archived-At: <https://www.postgresql.org/message-id/2497.1667546930%40antos>
Precedence: bulk

Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote:

> On 03.11.22 01:43, Antonin Houska wrote:
> > Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote:
> > =

> >> The CF entry is about privileges on publications.  Please rebase that=
 patch
> >> and repost it so that the CF app and the CF bot are up to date.
> > The rebased patch (with regression tests added) is attached here.
> =

> Some preliminary discussion:
> =

> What is the upgrade strategy?  I suppose the options are either that
> publications have a default acl that makes them publicly accessible, =

> thus preserving the existing behavior by default, or pg_dump would need =
to
> create additional GRANT statements when upgrading from pre-PG16.  I don'=
t see
> anything like either of these mentioned in the patch.  What is your plan=
?

So far I considered the first

> You might be interested in this patch, which relates to yours:
> https://commitfest.postgresql.org/40/3955/

ok, I'll check.

> Looking at your patch, I would also like to find a way to refactor away =
the
> ExecGrant_Publication() function.  I'll think about that.
> =

> I think you should add some tests under src/test/regress/ for the new GR=
ANT
> and REVOKE statements, just to have some basic coverage that it works.
> sql/publication.sql would be appropriate, I think.

I thought about the whole concept a bit more and I doubt if the PUBLICATIO=
N
privilege is the best approach. In particular, the user specified in CREAT=
E
SUBSCRIPTION ... CONNECTION ... (say "subscription user") needs to have SE=
LECT
privilege on the tables replicated. So if the DBA excludes some columns fr=
om
the publication's column list and sets the (publication) privileges in suc=
h 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 excl=
uded
columns.

As an alternative to the publication privileges, I think that the CREATE
SUBSCRIPTION command could grant ACL_SELECT automatically to the subscript=
ion
user on the individual columns contained in the publication column list, a=
nd
DROP SUBSCRIPTION would revoke that privilege.

Of course a question is what to do if the replication user already has tha=
t
privilege on some columns: either the CREATE SUBSCRIPTION command should r=
aise
ERROR, or we should introduce a new privilege (e.g. ACL_SELECT_PUB) for th=
is
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.

What do you think about that?

-- =

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