CODEWITHSUNDEEP
postgresqlrow-level-security
Yuri Kuschinsky
Yuri Kuschinsky

Reputation: 33

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

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

Upvotes: 3

Views: 2715

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246308

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.

Upvotes: 4

Related Questions