How can I insert records into Postgres with RLS when user has `grant all` perms and a loose policy

I’m new to Postgres’ RLS feature. I believe I’m following the documentation appropriately, but am getting unexpected behavior. Consider the following case:

• i have a table named report_files
• this table has a simple policy, policy <name> for all using (true)
• the user has grant all permissions on the table (grant all on all tables in schema testing to testuser;, along with grant execute on all functions ... and grant usage for the schema as well)
• the user can now read all fields in the table, but cannot insert records, against expectation

Here’s a really short snippet from psql demonstrating the aforementioned: https://gist.github.com/cdaringe/85dd9a7b1278afe4770869dc494216f3

I have set a permissive policy, which clearly evaluates OK as evidenced by the successful select command.

What can I troubleshoot to get the insert to work?

Thanks!

Answer

ERROR: permission denied for sequence report_files_id_seq

It looks to me like you need to grant your user permission to use the id sequence: report_files_id_seq

You should be able to do this with the following

GRANT USAGE, SELECT ON SEQUENCE report_files_id_seq TO testuser;

Or to bulk add all tables:

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA <insert schema name here>

ALL is equivalent to USAGE, SELECT, UPDATE