Hacker News Reader

Comment by ruslan_talpa | Hacker News Reader

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-...

View on HN

Yeah, 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.

The 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.

> 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.

Does anyone know how to do these things in JDBC? Is it possible to issue `SET my.username = 'tomas'` before executing a query?

SET ROLE "user"

The biggest issue with this is roles are identifiers, which means you can't use parameters for them.

So, 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?

You 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.

Can I do that inline with a statement? I don't think I'd mind outsourcing rights to the DB.

No, 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).

Reply on news.ycombinator.com