I'm using node-postgres to connect to a PostgreSQL 9.6 database with connection pooling enabled. All connections user the same database user. One reason I can't use multiple DB users is that as far as I read, row level security and views don't work well together in that case as the owner of the view is used for RLS.
I'm now looking at using row level security in Postgres, and I'd like to make sure I'm doing this correctly.
SET LOCAL to set the current application user id which is then used by the row level security USING clause. The only way I can think of achieving this with connection pooling is to wrap every query in a transaction with node-postgres and execute the
SET LOCAL command like the following in every transaction.
SET LOCAL postgres.my_user = 20;
The following code is a simplified example of how I want to define the row level security, the real version has a few more conditions:
CREATE POLICY table_a_read_policy ON table_a FOR SELECT USING ( EXISTS ( SELECT * FROM permissions WHERE user_id = current_setting('postgres.my_user')::int AND permission_type = 'read' ) );
My understanding is that any SQL injection in this case would also give the attacker the ability to just set any user id and circumvent row level security this way. But I don't see any way to avoid this as the db user I connect with must have the ability to see all data for the entire application.
There are a few points I'm not entirely sure about:
- Is using
SET LOCALas a way to store the current application user identity for row level security safe? Especially in the context of connection pooling?
- Is there a better way than wrapping every query in a transaction in node-postgres and executing the
SET LOCALcommand every single time?
- Is there a way to structure this (with the restriction that the database connection is made using a single database user) in a way where an SQL injection would not automatically give the ability to circumvent row level security?