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, or
somemoral 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.)
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.
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.
regards, tom lane