Re: running logical replication as the subscription owner

On Wed, May 17, 2023 at 10:10 AM Ajin Cherian <itsajin@gmail.com> wrote:
>
> On Mon, May 15, 2023 at 10:47 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
> > On Mon, May 15, 2023 at 5:44 PM Ajin Cherian <itsajin@gmail.com> wrote:
> > >
> > > On Fri, May 12, 2023 at 9:55 PM Ajin Cherian <itsajin@gmail.com> wrote:
> > > >
> > > > If nobody else is working on this, I can come up with a patch to fix this
> > > >
> > >
> > > Attaching a patch which attempts to fix this.
> > >
> >
> > Thank you for the patch! I think we might want to have tests for it.
> >
> I have updated the patch with a test case as well.

Thank you for updating the patch! Here are review comments:

+       /*
+        * Make sure that the copy command runs as the table owner, unless
+        * the user has opted out of that behaviour.
+        */
+       run_as_owner = MySubscription->runasowner;
+       if (!run_as_owner)
+               SwitchToUntrustedUser(rel->rd_rel->relowner, &ucxt);
+
        /* Now do the initial data copy */
        PushActiveSnapshot(GetTransactionSnapshot());

I think we should switch users before the acl check in
LogicalRepSyncTableStart().

---
+# Create a trigger on table alice.unpartitioned that writes
+# to a table that regress_alice does not have permission.
+$node_subscriber->safe_psql(
+               'postgres', qq(
+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 ALICE_TRIGGER AFTER INSERT ON alice.unpartitioned FOR EACH ROW
+EXECUTE PROCEDURE alice.alice_audit();
+ALTER TABLE alice.unpartitioned ENABLE ALWAYS TRIGGER ALICE_TRIGGER;
+));

While this approach works, I'm not sure we really need a trigger for
this test. I've attached a patch for discussion that doesn't use
triggers for the regression tests. We create a new subscription owned
by a user who doesn't have the permission to the target table. The
test passes only if run_as_owner = true works.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com