Re: running logical replication as the subscription owner - Mailing list pgsql-hackers

On Mon, 2023-03-27 at 16:47 -0400, Robert Haas wrote:
> No, not really. It's pretty common for a lot of things to be in the
> public schema, and the public schema is likely to be in the search
> path of every user involved.

Consider this trigger function which uses an unqualified reference to
pfunc(), therefore implicitly depending on the public schema:

  CREATE FUNCTION public.pfunc() RETURNS INT4 LANGUAGE plpgsql AS
    $$ BEGIN RETURN 42; END; $$;
  CREATE FUNCTION foo.tfunc() RETURNS TRIGGER LANGUAGE plpgsql AS
    $$ BEGIN
       NEW.i = pfunc();
       RETURN NEW;
       END; $$;
  CREATE TABLE foo.a(i INT);
  CREATE TRIGGER a_trigger BEFORE INSERT ON foo.a
    FOR EACH ROW EXECUTE PROCEDURE foo.tfunc();
  ALTER TABLE foo.a ENABLE ALWAYS TRIGGER a_trigger;

But that trigger function is broken today for logical replication --
pfunc() isn't found by the subscription process, which has an empty
search_path.

> Let's back up a minute here. Suppose someone were to request a new
> command, ALTER TABLE <name> DO NOT LET THE SUPERUSER ACCESS THIS. We
> would reject that proposal. The reason we would reject it is because
> it wouldn't actually work as documented. We know that the superuser
> has the power to access that account and reverse that command, either
> by SET ROLE or by changing the account password or by changing
> pg_hba.conf or by shelling out to the filesystem and doing whatever.
> The feature purports to do something that it actually cannot do. No
> one can defend themselves against the superuser. Not even another
> superuser can defend themselves against a superuser. Pretending
> otherwise would be confusing and have no security benefits.

Good framing. With you so far...

> Now let's think about this case. Can a table owner defend themselves
> against a subscription owner who wants to usurp their privileges? If
> they cannot, then I think that what the patch does is correct for the
> same reason that I think we would correctly reject the hypothetical
> command proposed above.

Agreed...

[ Aside: A user foo who accesses a table owned by bar has no means to
defend themselves against SECURITY INVOKER code that usurps their
privileges. Applying your logic above, foo should have to grant SET
ROLE privileges to bar before accessing the table. ]

> If
> the table owner has no executable code at all attached to the table -
> -
> not just triggers, but also expression indexes and default
> expressions
> and so forth -- then they can.

Right...

>  If they do have those things then in
> theory they might be able to protect themselves, but in practice they
> are unlikely to be careful enough. I judge that practically every
> installation where table owners use triggers would be easily
> compromised. Only the most security-conscious of table owners are
> going to get this right.

This is the interesting part.

Commit 11da97024ab set the subscription process's search path as empty.
It seems it was done to protect the subscription owner against users
writing into the public schema. But after your apply-as-table-owner
patch, it seems to also offer some protection for table owners against
a malicious subscription owner, too.

But I must be missing something obvious here -- if the subscription
process has an empty search_path, what can an attacker do to trick it?

Regards,
    Jeff Davis