4 ms·
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 authe
by darksaints 9y ago
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.
- 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.