Re: Privileges on PUBLICATION

On Thu, Nov 3, 2022 at 11:12 AM Antonin Houska <ah(at)cybertec(dot)at> wrote:
>
> Peter Eisentraut <peter(dot)eisentraut(at)enterprisedb(dot)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.
>
> There's still one design issue that I haven't mentioned yet: if the USAGE
> 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 plugin 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 subscription
> and add it again, or to drop and re-create the whole subscription. I haven't
> added a note about this problem to the documentation yet, in case someone 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.

[1] - https://www.postgresql.org/message-id/CAHut%2BPvMbCsL8PAz1Qc6LNoL0Ag0y3YJtPVJ8V0xVXJOPb%2B0xw%40mail.gmail.com
[2] - https://www.postgresql.org/message-id/CAA4eK1JTwOAniPua04o2EcOXfzRa8ANax%3D3bpx4H-8dH7M2p%3DA%40mail.gmail.com

--
With Regards,
Amit Kapila.