Question 267690 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Enforcing row-level security in a shared postgres pool

I’m working on a typical client-server webapp. It is using a system somewhat like GraphQL where the client has some flexibility in specifying what data it needs, without custom API endpoints for every type of data. The server is running node, and is using node-postgres with a typical pg.Pool. A client could send something like this:

{select: '*', from: 'expenses', where: {'op': 'gt', 'lhs': 'expenses.amount', 'rhs': 20}}

which would be translated to SELECT * FROM expenses WHERE expenses.amount > $1 (given $1 = 20). With enough care, this system can be made safe from injection attacks.

I’d also like to incorporate row-level security policies. For example:

create policy only_see_own_expenses on expenses using (expenses.user_id = <USER ID>);

As an extra security barrier, I want to make sure that even if an injection attack is succesful, a client can not "unset" its user ID.

I’ve seen <USER ID> been defined in a few ways:

  1. current_user, in which case every user of the app also needs a postgres user/role
  2. An arbitrary setting like current_setting('myapp.user_id') in combination with a SET LOCAL myapp.user_id = ... at the start of a transaction

Approach (2) seems most flexible to me. I’d just wrap every generated SQL query in a BEGIN; SET LOCAL myapp.user_id = 123; {generated query}; END;. The problem is that an attacker could inject another SET LOCAL statement, and impersonate another user.

In approach (1) you can similarly wrap every generated query with a SET ROLE ... statement at the start, yielding the same problem. An alternative is to create a new connection for each query with that specific role. I believe postgres would never allow that connection to switch to another role. But setting up a new connection per query would result in a lot of overhead.

How do I enforce row-level security without the performance hit of a new connection per query?

Tags:

2

Answers


  1. 0

    As you observe, but setting a placeholder parameter and using SET LOCAL ROLE to temporarily assume a different role can be subverted by attackers who can execute arbitrary SQL, like in an SQL injection attack.

    I don’t think that there is a way to accomplish what you want that is safe from SQL injection. The problem is a fundamental one: you handle authentication in the application, not in the database (where you are using a single application user to benefit from connection pooling), but you want to have the database handle authorization via row-level security. That requires a way for the application to tell the database what the application user is. Now the only way that the application can tell the database anything is by using SQL, and an attacker who can run arbitrary SQL statements can always subvert that.

    I think that the only option for you is to harden your application against SQL injection attacks.

    Login or Signup to reply.
  2. 0

    As an extra security barrier, I want to make sure that even if an injection attack is succesful, a client can not "unset" its user ID.

    Your second solution is not only perfectly viable, it’s exactly what many websites do. Sure, anyone can edit the user_id parameter in a cookie, URL, SQL statement or even custom protocol frame, but as long as you do not

    1. expose the list of valid identifiers
    2. make the identifiers easy/possible to guess/brute-force through
    3. share or re-use them

    changing it just makes no sense because you have nothing to change it to. If someone just "unsets" the id, it’ll break their access. Same if they set it wrong. Your only concern is to make sure if they set it right, there’s no way they got the other id from you. Assuming the above, having another token to change to means it was acquired outside the system, beyond your control (given, stolen, leaked – not by you), or that it’s just multiple sessions of the same person and they have that access anyways.

    That being said, by no means does it free you from the necessity to also lock down your SQL against injections and it kind of requires you to set up and manage this token-based authentication on top of everything else, which might be a challenge on its own. Make sure you know how rules and privileges work and that you’re not leaking anything through security definer routines or views with security_barrier off.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search