* Tom Lane (tgl@xxxxxxxxxxxxx) wrote: > Guyren Howe <guyren@xxxxxxxxx> writes: > > I’m not following. What I would like is just a lightweight way to switch the connections to use a different role, or some moral equivalent, that would prevent an SQL injection from wrecking havoc. I’m not proposing anything that will change anything 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 minimal privileges 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
Attachment:
signature.asc
Description: Digital signature
