From: Antonin Houska <ah@cybertec.at>
To: Amit Kapila <amit.kapila16@gmail.com>
cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
    Euler Taveira <euler@eulerto.com>,
    PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Privileges on PUBLICATION
In-reply-to: 
 <CAA4eK1KNjJiw0RJw+KZxja7RFw6JOngOMR9SPyc5oSFMcbB9Bw@mail.gmail.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>
 <CAA4eK1KNjJiw0RJw+KZxja7RFw6JOngOMR9SPyc5oSFMcbB9Bw@mail.gmail.com>
Comments: In-reply-to Amit Kapila <amit.kapila16@gmail.com>
   message dated "Fri, 04 Nov 2022 09:59:43 +0530."
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2784.1667551049.1@antos>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 04 Nov 2022 09:37:29 +0100
Message-ID: <2785.1667551049@antos>
Archived-At: <https://www.postgresql.org/message-id/2785.1667551049%40antos>
Precedence: bulk

Amit Kapila <amit.kapila16@gmail.com> wrote:

> On Thu, Nov 3, 2022 at 11:12 AM Antonin Houska <ah@cybertec.at> wrote:
> >
> > Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote:
> >
> > > The CF entry is about privileges on publications.  Please rebase tha=
t 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.
> >
> > There's still one design issue that I haven't mentioned yet: if the US=
AGE
> > privilege on a publication is revoked after the synchronization phase
> > completed, the missing privilege on a publication causes ERROR in the =
output
> > plugin. If the privilege is then granted, the error does not disappear=
 because
> > the same (historical) snapshot we use to decode the failed data change=
 again
> > is also used to check the privileges in the catalog, so the output plu=
gin does
> > not see that the privilege has already been granted.
> >
> =

> We have a similar problem even when publication is dropped/created.
> The replication won't be able to proceed.
> =

> > The only solution seems to be to drop the publication from the subscri=
ption
> > and add it again, or to drop and re-create the whole subscription. I h=
aven't
> > added a note about this problem to the documentation yet, in case some=
one has
> > better idea how to approach the problem.
> >
> =

> I think one possibility is that the user advances the slot used in
> replication by using pg_replication_slot_advance() at or after the
> location where the privilege is granted. Some other ideas have been
> discussed in the thread [1], in particular, see email [2] and
> discussion after that but we didn't reach any conclusion.

Thanks for feedback. Regarding the publications, I'm not sure what the use=
r
should expect if he revokes the permissions in the middle of processing. I=
n
such a situation, he should be aware that some privileged data could alrea=
dy
have been replicated. My preference in such a situation would be to trunca=
te
the table(s) on the subscriber side and to restart the replication, rather
than fix the replication and keep the subscriber's access to the leaked da=
ta.

> [1] - https://www.postgresql.org/message-id/CAHut%2BPvMbCsL8PAz1Qc6LNoL0=
Ag0y3YJtPVJ8V0xVXJOPb%2B0xw%40mail.gmail.com
> [2] - https://www.postgresql.org/message-id/CAA4eK1JTwOAniPua04o2EcOXfzR=
a8ANax%3D3bpx4H-8dH7M2p%3DA%40mail.gmail.com

-- =

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