Re: running logical replication as the subscription owner

From: Ajin Cherian <itsajin(at)gmail(dot)com>
To: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
Cc: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Noah Misch <noah(at)leadboat(dot)com>, Jeff Davis <pgsql(at)j-davis(dot)com>, Jelte Fennema <postgres(at)jeltef(dot)nl>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>, Andres Freund <andres(at)anarazel(dot)de>
Subject: Re: running logical replication as the subscription owner
Date: 2023-05-12 11:55:46
Message-ID: CAFPTHDbiww8Nwf8+wG9hfpibTPbtoAPaJZ79UowVbhjMoBotaw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bharath Rupireddy 2023-05-12 11:58:25 Re: walsender performance regression due to logical decoding on standby changes
Previous Message Robert Haas 2023-05-12 11:39:29 Re: v16 regression - wrong query results with LEFT JOINs + join removal