Re: Non-superuser subscription owners

On Fri, Jan 27, 2023 at 5:56 PM Mark Dilger
<mark(dot)dilger(at)enterprisedb(dot)com> wrote:
> If the owner cannot modify the subscription, then the owner degenerates into a mere "run-as" user. Note that this isn't how things work now, and even if we disallowed owners from modifying the connection string, there would still be other attributes the owner could modify, such as the set of publications subscribed.

The proposed patch blocks every form of ALTER SUBSCRIPTION if
password_required false is set and you aren't a superuser. Is there
some other DML command that could be used to modify the set of
publications subscribed?

> More generally, my thinking on this thread is that there needs to be two nosuperuser roles: A higher privileged role which can create a subscription, and a lower privileged role serving the "run-as" function. Those shouldn't be the same, because the "run-as" concept doesn't logically need to have subscription creation power, and likely *shouldn't* have that power. Depending on which sorts of attributes a subscription object has, such as the connection string, the answer differs for whether the owner/"run-as" user should get to change those attributes. One advantage of Jeff's idea of using a server object rather than a string is that it becomes more plausibly safe to allow the subscription owner to make changes to that attribute of the subscription.

There's some question in my mind about what these different mechanisms
are intended to accomplish.

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.

From my point of view, the two things that the superuser is most
likely to want to do are (1) control the replication setup themselves
and delegate nothing to any non-superuser or (2) give a non-superuser
pretty much complete control over replication with just enough
restrictions to avoid letting them do things that would compromise
security, such as hacking the local superuser account. In other words,
I expect that delegation of the logical replication configuration is
usually going to be all or nothing. Jeff's system allows for a
situation where you want to delegate some stuff but not everything,
and specifically where you want to dedicate control over the
subscription options and the tables being replicated, but not the
connection strings. To me, that feels like a bit of an awkward
configuration; I don't really understand in what situation that
division of responsibility would be particularly useful. I trust that
Jeff is proposing it because he knows of such a situation, but I don't
know what it is. I feel like, even if I wanted to let people use some
connection strings and not others, I'd probably want that control in
some form other than listing a specific list of allowable connection
strings -- I'd want to say things like "you have to use SSL" or "no
connecting back to the local host," because that lets me enforce some
general organizational policy without having to care specifically
about how each subscription is being set up.

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. 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. 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?

--
Robert Haas
EDB: http://www.enterprisedb.com