RLS Policy doesn't applied for table in PostgreSQL while the RLS has been enabled

Question

I've trying to apply RLS on my PostgreSQL 14. But it seems didn't work. Here it's my SQL (i run it on PgAdmin4):

CREATE TABLE IF NOT EXISTS public.employee
(
    tenant_id character varying(255) NOT NULL,
    username character varying(255) NOT NULL,
    CONSTRAINT employee_pkey PRIMARY KEY (username)
)

ALTER TABLE IF EXISTS employee
    OWNER to postgres;

ALTER TABLE IF EXISTS employee
    ENABLE ROW LEVEL SECURITY;

CREATE POLICY employee_tenant_isolation_policy
    ON employee
    AS PERMISSIVE
    FOR ALL
    TO public
    USING (((tenant_id)::text = ((current_setting('app.tenant_id'::text))::character varying)::text));

i'm also already have to insert the data:

INSERT INTO public.employee(
    tenant_id, username)
    VALUES ('tenant1', 'tenant1');
INSERT INTO public.employee(
    tenant_id, username)
    VALUES ('tenant2', 'tenant2');

As you can see, i have enabled the RLS and created the policy. But, when i try this query:

SET app.tenant_id to 'tenant1';
SELECT * FROM employee;

I got two values (tenant1, and tenant2). I expect it to only return row with 'tenant1' values.

Any ideas?

Thanks.

You shouldn't be using the superuser at all. The tables should be owned (and created) by a regular user. Only use `postgres` for DBA tasks. — , Sep 07 '22 at 08:17

score 2 · Accepted Answer · answered Sep 07 '22 at 06:55

As per the documentation, superusers bypass RLS:

Superusers and roles with the BYPASSRLS attribute always bypass the row security system when accessing a table. Table owners normally bypass row security as well, though a table owner can choose to be subject to row security with ALTER TABLE ... FORCE ROW LEVEL SECURITY.

If you create a non-superuser, you should be able to see the filtering:


edb=# create user foobar with login;
CREATE ROLE
edb=# grant select on employee to foobar;
GRANT
edb=# \c edb foobar;
You are now connected to database "edb" as user "foobar".
edb=> SET app.tenant_id to 'tenant1';
SET
edb=> select * from employee ;
 tenant_id | username 
-----------+----------
 tenant1   | tenant1
(1 row)

RLS Policy doesn't applied for table in PostgreSQL while the RLS has been enabled

1 Answers1