Re: Non-superuser subscription owners

From: Andres Freund <andres(at)anarazel(dot)de>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Mark Dilger <mark(dot)dilger(at)enterprisedb(dot)com>, Jeff Davis <pgsql(at)j-davis(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Andrew Dunstan <andrew(at)dunslane(dot)net>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Non-superuser subscription owners
Date: 2023-02-01 00:01:16
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


On 2023-01-30 10:44:29 -0500, Robert Haas wrote:
> On a technical level, I think that the idea of having a separate
> objection for the connection string vs. the subscription itself is
> perfectly sound, and to repeat what I said earlier, if someone wants
> to implement that, cool. I also agree that it has the advantage that
> you specify, namely, that someone can have rights to modify one of
> those objects but not the other. What that lets you do is define a
> short list of known systems and say, hey, you can replicate whatever
> tables you want with whatever options you want, but only between these
> systems. I'm not quite sure what problem that solves, though.

That does seem somewhat useful, but also fairly limited, at least as
long as it's really just a single connection, rather than a "pattern" of
safe connections.

> Unfortunately, I have even less of an idea about what the run-as
> concept is supposed to accomplish. I mean, at one level, I see it
> quite clearly: the user creating the subscription wants replication to
> have restricted privileges when it's running, and so they make the
> run-as user some role with fewer privileges than their own. Brilliant.
> But then I get stuck: against what kind of attack does that actually
> protect us? If I'm a high privilege user, perhaps even a superuser,
> and it's not safe to have logical replication running as me, then it
> seems like the security model of logical replication is fundamentally
> busted and we need to fix that.

I don't really understand that - the run-as approach seems like a
necessary piece of improving the security model.

I think it's perfectly reasonable to want to replicate from one system
in another, but to not want to allow logical replication to insert into
pg_class or whatnot. So not using superuser to execute the replication
makes sense.

This is particularly the case if you're just replicating a small part of
the tables from one system to another. E.g. in a sharded setup, you may
want to replicate metadata too servers.

Even if all the systems are operated by people you trust (including
possibly even yourself, if you want to go that far), you may want to
reduce the blast radius of privilege escalation, or even just bugs, to a
smaller amount of data.

I think we'll need two things to improve upon the current situation:

1) run-as user, to reduce the scope of potential danger

2) Option to run the database inserts as the owner of the table, with a
check that the run-as is actually allowed to perform work as the
owning role. That prevents escalation from table owner (who could add
default expressions etc) from gettng the privs of the
run-as/replication owner.

I think it makes sense for 1) to be a fairly privileged user, but I
think it's good practice for that user to not be allowed to change the
system configuration etc.

> It can't be right to say that if you have 263 users in a database and
> you want to replicate the whole database to some other node, you need
> 263 different subscriptions with a different run-as user for each. You
> need to be able to run all of that logical replication as the
> superuser or some other high-privilege user and not end up with a
> security compromise.

I'm not quite following along here - are you thinking of 263 tables
owned by 263 users? If yes, that's why I am thinking that we need the
option to perform each table modification as the owner of that table
(with the same security restrictions we use for REINDEX etc).

> And if we suppose that that already works and is safe, well then
> what's the case where I do need a run-as user?

It's not at all safe today, IMO. You need to trust that nothing bad will
be replicated, otherwise the owner of the subscription has to be
considered compromised.


Andres Freund

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Nathan Bossart 2023-02-01 00:05:21 Re: suppressing useless wakeups in logical/worker.c
Previous Message Jacob Champion 2023-02-01 00:00:42 Re: [PoC] Let libpq reject unexpected authentication requests