* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Guyren Howe <guyren@gmail.com> writes:
> > I’m not following. What I would like is just a lightweight way to switch the connections to use a different role,
orsome moral equivalent, that would prevent an SQL injection from wrecking havoc. I’m not proposing anything that will
changeanything else about how the application is using the database.
>
> > SET ROLE doesn’t work, because an SQL injection can just SET ROLE back to the privileged user.
>
> > I would like a regime where there is no permanent privileged relationship between the client application and the
database;a user would need to supply validating information that we can use to switch a connection to something with
minimalprivileges for just that role, for the duration of one session or transaction.
>
> And what happens after that? You can't reuse the session anyway, because
> there would be no way to switch it back to a more-privileged state so you
> could use it for another user ID. (If there were a way, then a successful
> SQL-injection attack could use it too.)
That's based on an incorrect assumption that the state which allows a
connection to be reused by another user ID is 'more privileged'.
If we had a way to require authentication for a 'SET ROLE' call then you
would be able to do what Guyren is asking about and when you're done
with the session something issues 'RESET ROLE;' and you're back to a
role that *isn't* more privileged and to move to another user ID you
have to go through the authentication process again.
This isn't fantasy, it's been done, but it's more complicated than it
really should be, as noted up-thread. There's also complications around
how to handle the authentication credentials so you aren't constantly
asking the user to re-authenticate, but that's not impossible to deal
with either, you just need something akin to the cookie/unique ID used
by every website that authenticates users today.
> If you end up having to dedicate each connection to a particular
> unprivileged userID, then you can just open the connection as that user
> to start with; a magic one-way privilege switch doesn't really help.
Well, to that I'd say "it depends." There are certainly scenarios where
you have multiple userIDs and a connection pooler like pgbouncer which
handles the different connections to the database and it's a lot better
than making new connections because new connections are so painful and
slow to create. This doesn't work great if you have thousands of
concurrently connected independent users, of course.
> We've discussed this problem repeatedly (you might want to search the
> archives a bit) and never found a solution that was both fully secure
> and did much of anything for connection-pooling scenarios.
I don't agree that this is unsolvable, but it would require things like
protocol-level changes which no one has had the gumption to work through
and propose.
In short, I agree with Guyren, there are features needed here that we
don't have and it would be a great deal better if we did.
Thanks!
Stephen