I'm making a website with multiple login users and I want to setup row-level-security so that my app can scale. I have a lot of many-many relations with access defined via join-tables. I don't want to have to append each db query with a very long where clause each time and I heard row-level-security was the best approach. I connect to postgres in NodeJS via the 'pg' library and query the db using pool.query('sql string', [params]). The user-id is safely stored and retrieved in a json-web-token, so I'm not worried about that. Questions is, how to I apply the user-id to each db query. Is prepending each query with a "SET my.some_session_variable = user_id" safe? If multiple users are querying the database at once could these multiple set session variables interfere with each other? If row-level-security is not recommended what is the best approach?
Asked
Active
Viewed 883 times
4
-
Never use `pool.query`. Always create a client for a dedicated connection, on which you can set the session variable. Then it should be safe. – Bergi Jul 21 '20 at 16:59
-
I did a ```client.connect()``` then ```client.query('my.some_session_variable = user_id)``` then ```client.release()``` but when I connected again the and selected the session variable it was still there. So it wasn't unique for each ```client.connect()``` – Jul 22 '20 at 03:05
-
1Yes, that happens when you get the same client. You'll need to either 1) overwrite it every time 2) use [`RESET ALL`](https://www.postgresql.org/docs/current/sql-reset.html)/[`DISCARD`](https://www.postgresql.org/docs/current/sql-discard.html) or 3) only use `SET LOCAL` inside a transaction. What I meant was that when using `pool.query`, you might use a different client on every call, and session variables would be totally unpredictable. – Bergi Jul 22 '20 at 08:33
-
1See also https://node-postgres.com/features/transactions/ and https://github.com/brianc/node-postgres/issues/391 – Bergi Jul 22 '20 at 08:44