Using PostgreSQL row level security (RLS) policies with current_setting() function

Question

I've applied RLS policy to the "users" table and expect only records with tenant_id=2 to be retrieve:

CREATE TABLE "users" ("name" text UNIQUE NOT NULL, "tenant_id" int NOT NULL DEFAULT current_setting('app.current_tenant')::int);

--Enable Row Security Policies
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE users FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_policy ON users USING (tenant_id = current_setting('app.current_tenant')::int);

--Set "111" as the current tenant.
SET app.current_tenant TO 1;
INSERT INTO users VALUES ('admin');
INSERT INTO users VALUES ('bob');

--Set "222" as the current tenant.
SET app.current_tenant TO 2;
INSERT INTO users VALUES ('alice');

--Data output
SELECT * FROM users;

But I get all users in the result:

name    tenant_id
admin   1
bob     1
alice   2

Why is this happening?

Here is the dbFiddle of what I am stuck with: https://www.db-fiddle.com/f/iFktvVsDNYKggUNT2oDJBV/0

Which (database) user do you use to run that SELECT – Apr 09 '21 at 11:00 — , Apr 09 '21 at 11:00

score 3 · Accepted Answer · answered Apr 09 '21 at 11:05

3

There are four reasons why row level security can be bypassed:

The user is the owner of the table.

You can subject the table owner to row level security with
```
ALTER TABLE users FORCE ROW LEVEL SECURITY;
```
The user is a superuser.
The user was created with BYPASSRLS.
The database parameter row_security is set to off.

Note that using row level security with a placeholder parameter is inherently insecure: if an attacker can issue an SQL statement (say, through SQL injection), they can just change the value to what they like.

answered Apr 09 '21 at 11:05

Laurenz Albe

209,280
17
206
263

Indeed, the db-fiddle.com user is a superuser. – Bergi Apr 09 '21 at 11:11
"*Using RLS with a placeholder parameter is inherently insecure*" - is there a better way to pass parameters to a policy, say from the connection string? – Bergi Apr 09 '21 at 11:13
@Bergi You could write a small C extension to define a real parameter that can only be set once, for example. – Laurenz Albe Apr 09 '21 at 11:16
@Bergi Exactly. Thanks for pointing that out, that was the reason my fiddle wasn't working. Conclusion: If your app connects to the database as the same PostgreSQL role as the table owner your security policies aren't in effect by default. – Yuri Kuschinsky Apr 09 '21 at 12:26
1

Here is an updated fiddle that works correctly: https://www.db-fiddle.com/f/hGfeLXsUmmtpfCkbzWR6Ln/0 – Yuri Kuschinsky Apr 09 '21 at 12:37
@LaurenzAlbe Meh. Not everyone can easily write and deploy custom extensions unfortunately. – Bergi Apr 09 '21 at 12:58
1

@Bergi The best thing is to tie it to the current database user. RLS is best used if you do your user management in the database. I think it is important to understand the security implications. – Laurenz Albe Apr 09 '21 at 13:00
Right, one db user per tenant (and one policy per user) will solve this problem easily, and allow specifying the tenant from the connection string even. Probably the best solution if you have one backend running per tenant. – Bergi Apr 09 '21 at 13:06

Using PostgreSQL row level security (RLS) policies with current_setting() function

1 Answers1