Re: running logical replication as the subscription owner

On Fri, May 12, 2023 at 1:49 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Fri, May 12, 2023 at 9:10 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> >
> > On Fri, May 12, 2023 at 1:12 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > >
> > > On Thu, May 11, 2023 at 7:38 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > > > Do we want the initial sync to also respect 'run_as_owner' option? I
> > > > might be missing something but I don't see anything in the docs about
> > > > initial sync interaction with this option. In the commit a2ab9c06ea,
> > > > we did the permission checking during the initial sync so I thought we
> > > > should do it here as well.
> > >
> > > It definitely should work that way. lf it doesn't, that's a bug.
> >
> > After some tests, it seems that the initial sync worker respects
> > 'run_as_owner' during catching up but not during COPYing.
> >
>
> Yeah, I was worried during copy phase only. During catchup, the code
> is common with apply worker code, so it will work.
>

I tried the following test:

====================
Repeat On the publisher and subscriber:
/* Create role regress_alice with NOSUPERUSER on
publisher and subscriber and a table for replication */

CREATE ROLE regress_alice NOSUPERUSER LOGIN;
CREATE ROLE regress_admin SUPERUSER LOGIN;
GRANT CREATE ON DATABASE postgres TO regress_alice;
SET SESSION AUTHORIZATION regress_alice;
CREATE SCHEMA alice;
GRANT USAGE ON SCHEMA alice TO regress_admin;
CREATE TABLE alice.test (i INTEGER);
ALTER TABLE alice.test REPLICA IDENTITY FULL;

On the publisher:
postgres=> insert into alice.test values(1);
postgres=> insert into alice.test values(2);
postgres=> insert into alice.test values(3);
postgres=> CREATE PUBLICATION alice FOR TABLE alice.test
WITH (publish_via_partition_root = true);

On the subscriber: /* create table admin_audit which regress_alice
does not have access to */
SET SESSION AUTHORIZATION regress_admin;
create table admin_audit (i integer);

On the subscriber: /* Create a trigger for table alice.test which
inserts on table admin_audit which the table owner of alice.test does
not have access to */
SET SESSION AUTHORIZATION regress_alice;
CREATE OR REPLACE FUNCTION alice.alice_audit()
RETURNS trigger AS
$$
BEGIN
insert into public.admin_audit values(2);
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';
create trigger test_alice after insert on alice.test for each row
execute procedure alice.alice_audit();
alter table alice.test enable always trigger test_alice;

On the subscriber: /* Create a subscription with run_as_owner = false */
CREATE SUBSCRIPTION admin_sub CONNECTION 'dbname=postgres
host=localhost port=6972' PUBLICATION alice WITH (run_as_owner =
false);
===============

What I see is that as part of tablesync, the trigger invokes an
updates admin_audit which it shouldn't, as the table owner
of alice.test should not have access to the
table admin_audit. This means the table copy is being invoked as the
subscription owner and not the table owner.

However, I see subsequent inserts fail on replication with
permission denied error, so the apply worker correctly
applies the inserts as the table owner.

If nobody else is working on this, I can come up with a patch to fix this

regards,
Ajin Cherian
Fujitsu Australia