Re:Common DB schema change mistakes in Postgres
by jamessb on Tuesday April 30, 2024 @08:55AM
(#40210524)
> 2. I've no idea how to temporarily drop privileges for a single transaction. Connecting as the correct user on each incoming HTTP request is way too slow.
`SET ROLE`[1] changes the "the current user identifier of the current SQL session"; after running it "permissions checking for SQL commands is carried out as though the named role were the one that had logged in originally".
Whilst it changes the "current user" it doesn't change the current "session user", and this is what determines which roles you can switch to.
The docs also note that:
> SQL does not allow this command during a transaction; PostgreSQL does not make this restriction because there is no reason to.
> I suspect we aren't alone
Honestly I'd be happy to spend the time learning the ins and outs of PostgreSQL IAM stuff, but there's two very good reasons why I won't use it:
1. Still need the "role"/user across other services, so I don't save anything by doing ACL inside the DB.
2. I've no idea how to temporarily drop privileges for a single transaction. Connecting as the correct user on each incoming HTTP request is way too slow.