Search code examples
postgresqlsupabaserow-level-securityretool

Some tables give 'new row violates row-level security policy' error but some do not

I have created a new login role and assigned permissions to it:

CREATE ROLE myuser
LOGIN
PASSWORD 'password';

GRANT SELECT, INSERT, UPDATE, DELETE ON customer TO myuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON campaign TO myuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON lead TO myuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON lead_history TO myuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON log TO myuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON stats TO myuser;

When I log in to that role, I can now do any operation with the customer table, but if I try action queries with the other tables I get a 'new row violates row-level security policy for table "campaign"' error. If I do a select, no results are returned.

I checked the information_schema.table_privileges table and all of the permissions are the same for the tables.

Why would doing the exact same thing have different results on different tables?

I am using Supabase on the backend and Retool on the front end.

I cannot find any row level security policies for either table in pg_policy. In fact the whole pg_policy table appears empty. If I head over to Supabase's Policies page, all tables have RLS enabled.

Solution

  • If a table has row level security enabled, but there is no row level security policy, everything is forbidden by default: you can see none of the rows, and inserting rows will cause the error you observe. This applies to all users subject to row level security (exceptions are superusers, the table owner and users defined with BYPASSRLS).

    Disable row level security:

    ALTER TABLE table_name DISABLE ROW LEVEL SECURITY;