4 ms·
All authorisation requirements are solved easier and better at the db level. Since you have permissions by columns and row level security, it means you can defi
by ruslan_talpa 9y ago
All authorisation requirements are solved easier and better at the db level.
Since you have permissions by columns and row level security, it means you can define permissions down to a cell.
As someone said, people just don't read the manual of their database past page 100 :) and use "root" when connection to the database from their code.
https://blog.2ndquadrant.com/application-users-vs-row-level-security/ https://blog.2ndquadrant.com/application-users-vs-row-level-...
- virmundi 9y agoDoes anyone know how to do these things in JDBC? Is it possible to issue `SET my.username = 'tomas'` before executing a query?
- snuxoll 9y agoSET ROLE "user" The biggest issue with this is roles are identifiers, which means you can't use parameters for them.
- virmundi 9y agoCan I do that inline with a statement? I don't think I'd mind outsourcing rights to the DB.
- snuxoll 9y agoNo, you'll need to call SET/RESET role as their own statements. The easiest way to do this in Java is to ensure RESET ROLE is called when checking a connection back into the pool, and calling SET ROLE in a servlet interceptor (probably the same one that handles authentication).
- s_kilk 9y agoSo, if I'm following this correctly, you'd need to start a new connection with the end-users credentials? Which would be slow and rule out connection pooling and other such niceties?
- snuxoll 9y agoYou can use connection pooling, the user you are connected with needs to be granted the role you are switching to (preferably with the NOINHERIT flag) - that's the purpose behind the SET ROLE and RESET ROLE statements.
- darksaints 9y agoYeah, but users are a connection level concept, meaning you have to abandon your connection pooling system in order to use the db's user authorization and authentication system. Every new query is a new connection.
- begriffs 9y agoThe link Ruslan shared explains how to use row level security with application users as opposed to db users. Basically PostgREST translates cryptographically signed JWT claims from the client request into local SQL variables accessible by postgresql's "current_setting" function. The additional claims can identify the user beyond their db role. They can specify, for instance, the user's email address. Row level security policies can use current_setting to refer to the extra claims. So multiple users can share a db role and connection pooling works fine.
- ruslan_talpa 9y ago> Every new query is a new connection > users are a connection level concept That is not true. There are users in the db that do not have login privileges. PostgREST connects in with only one user (authenticator), then for each authenticated request it does `set local role alice` within a transaction, where alice is a db user with no login privileges.