3

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.

I'm using 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 LOCAL as 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 LOCAL command 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?
  • 1
    If there's a single db user and it can change postgres.my_user then what's the purpose of the policy? In other words, whom is it supposed to block from reading the table? – Daniel Vérité Jan 31 '17 at 16:07
  • @DanielVérité The idea is to enforce these rules consistently in a way that can't be broken accidentally. Even if I used multiple DB users (which I don't because it doesn't work with RLS and views), the web application would still need the ability to switch between users, which presents the same danger as the ability to set this variable. – Mad Scientist Jan 31 '17 at 16:46
  • How don't multiple roles work with views and RLS? – dezso Feb 3 '17 at 13:45
  • @dezso See stackoverflow.com/a/33863371/347857 . The row level security on the underlying tables sees the view creator as current user. – Mad Scientist Feb 3 '17 at 14:17
  • you can set the "fake" username in a GUC or temp table – Neil McGuigan Sep 6 '17 at 20:11
1

I have a similar setup on a new project.

The issue here is that you have a connection pool that uses a single login. The application that holds that connection pool will need to set the user correctly to get the required row level permissions. If your application is incorrectly configured / coded, then the permissions can be subverted.

It seems to me that row level permissions are only really useful when you have different users/roles with different logins reading from tables directly. In that case there is no where user = xxxx style clause that can be subverted. In this situation they make different connections.

-1

I was also looking to do something similar to what you had said. Came across this blog post and found it useful. Hope this helps.

blog.2ndquadrant.com/application-users-vs-row-level-security

Signed session variables

The first solution is a simple improvement of the session variables – we can’t really prevent the users from setting arbitrary value, but what if we could verify that the value was not subverted? That’s fairly easy to do using a simple digital signature. Instead of just storing the username, the trusted part (connection pool, application) can do something like this:

signature = sha256(username + timestamp + SECRET)

and then store both the value and the signature into the session variable:

SET my.username = 'username:timestamp:signature'

Assuming the user does not know the SECRET string (e.g. 128B of random data), it shouldn’t be possible to modify the value without invalidating the signature.

  • 3
    Since links come and go, you should summarize the blog post so the critical information is preserved here. – Anthony Genovese May 26 '17 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.