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

On Fri, Mar 24, 2023 at 3:59 AM Jeff Davis <pgsql@j-davis.com> wrote:
> That's a little confusing, why not just always use the
> SECURITY_RESTRICTED_OPERATION? Is there a use case I'm missing?

Some concern was expressed -- not sure exactly where the email is
exactly, and it might've been on pgsql-security -- that doing that
categorically might break things that are currently working. The
people who were concerned included Andres and I forget who else. My
gut reaction was the same as yours, just do it always and don't worry
about it. But if people think that users are likely to run afoul of
the SECURITY_RESTRICTED_OPERATION restrictions in practice, then this
is better, and the implementation complexity isn't high. We could even
think of extending this kind of logic to other places where
SECURITY_RESTRICTED_OPERATION is enforced, if desired.

> It would be really nice if this could be done with some kind of
> ordinary privilege rather than requiring SET ROLE. A user might expect
> that INSERT/UPDATE/DELETE/TRUNCATE privileges are enough. Or
> pg_write_all_data.
>
> I can see theoretically that a table owner might write some dangerous
> code and attach it to their table. But I don't quite understand why
> they would do that. If the code was vulnerable to attack, would that
> mean that they couldn't even insert into their own table safely?
>
> Requiring SET ROLE seems like it makes the subscription role into
> something very close to a superuser. And that takes away some of the
> benefits of delegating to non-superusers.

I am not thrilled with this either, but if you can arrange to run code
as a certain user without the ability to SET ROLE to that user then
there is, by definition, a potential privilege escalation. I don't
think we can just dismiss that as a non-issue. If the ability of one
user to potentially become some other user weren't a problem, we
wouldn't need any patch at all. Imagine for example that the table
owner has a trigger which doesn't sanitize search_path. The
subscription owner can potentially leverage that to get the table
owner's privileges.

More generally, Stephen Frost has elsewhere argued that we should want
the subscription owner to be a very low-privilege user, so that if
their privileges get stolen, it's no big deal. I disagree with that. I
think it's always a problem if one user can get unauthorized access to
another user's account, regardless of exactly what those accounts can
do. I think our goal should be to make it safe for the subscription
owner to be a very high-privilege user, because you're going to need
to be a very high-privilege user to set up replication. And if you do
have that level of privilege, it's more convenient and simpler if you
can just own the subscription yourself, rather than having to make a
dummy account to own it. To put that another way, I think that what
people are going to want to do in a lot of cases is have the superuser
own the subscription, so I think we need to make that case safe,
whatever it takes. In cases where the subscription owner isn't the
superuser, I think the next most likely possibility is that the
subscription owner is some kind of almost-super-user, like a
CREATEROLE user or someone running with rds_superuser or similar on
some PG fork. So that needs to be safe too, and I think this does
that. Having the subscription owner be some random user that doesn't
have a lot of privileges doesn't seem particularly useful to me. If it
were unproblematic to allow that, sure, but considering how easy it
would be for that low-privilege user to steal table owner privileges,
I don't think it makes sense.

> > It is unclear to me whether we should try to back-port this. It's
> > definitely a behavior change, and changing the behavior in the back
> > branches is not a nice thing to do. On the other hand, at least in my
> > opinion, the security consequences of the status quo are pretty dire.
> > I tend to suspect that a really high percentage of people who are
> > using logical replication at all are vulnerable to this, and lots of
> > people having a way to escalate to superuser isn't good.
>
> It's worth considering given that most subscription owners are
> superusers anyway. What plausible cases might it break?

AFAIU, the main concern is about the SECURITY_RESTRICTED_OPERATION
flag interacting badly with things people are already doing. Other
problems seem possible, e.g. if you're doing something that gets the
current user name and does something with it, the answer's going to
change, and you might like the new answer more or less than the old
one. It's a little hard to predict who will be inconvenienced in what
ways when you change behavior, but problems are certainly possible.

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