Row security policy issue when inserting rows

Question

I have created a table to store client records on it:

CREATE TABLE parking.client (
    k_client integer NOT NULL,
    id varchar(12) NOT NULL,
    name varchar(50) NOT NULL,
    last_name varchar(50) NOT NULL,
    email varchar(200) NOT NULL,
    CONSTRAINT client_pk PRIMARY KEY (k_client)
);

Then I defined a policy for this table:

ALTER TABLE parking.client ENABLE ROW LEVEL SECURITY;

CREATE POLICY client_pl ON parking.client
    AS PERMISSIVE
    FOR ALL
    TO user_role
    USING (email = CURRENT_USER);

I am trying to insert records on this table using a different role:

CREATE ROLE manage_account_user WITH 
    CREATEROLE
    LOGIN
     PASSWORD 'MyPassword';

GRANT SELECT,INSERT
   ON TABLE parking.client
   TO manage_account_user;

But whenever I try an INSERT I get:

new row violates row-level security policy for table "client"

Why am I getting a row-level security restriction if I have only defined the policy for the role user_role?

Answer 1

With row level security enabled, everything is forbidden unless it is explicitly allowed by a policy. So you'd have to add a policy that allows the role to INSERT data.

If you want a policy for the role inserter that allows it to insert anything, you could use

CREATE POLICY inserter_can_insert ON parking.client
   FOR INSERT TO inserter
   WITH CHECK (TRUE);