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!

Solution

ERROR: permission denied for sequence report_files_id_seq

You need to grant permission to use the report_files_id_seq sequence. Granting permission to a table does not grant permission to a sequence. They are completely separate. A sequence is often used by the table as part of the DEFAULT value, but it's not part of the table.

You can grant permission to the sequence with:

GRANT USAGE, SELECT ON SEQUENCE report_files_id_seq TO testuser;

Or to grant permission to all sequences** in a particular schema:

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

ALL is equivalent to USAGE, SELECT, UPDATE

** this does not give permission to sequences created after the command is run.

Do you need to use a sequence?

If you are using Postgres version >=10, and only use the sequence to have a primary key value generated, my preference is to avoid sequences altogether. You can do that by using an IDENTITY column. For example:

CREATE TABLE t (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    description TEXT NOT NULL
);

See the docs