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

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.

CodePudding user response：

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)