CODEWITHSUNDEEP
sqlpostgresqlrow-level-security
cdaringe
cdaringe

Reputation: 1538

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:

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!

Upvotes: 1

Views: 2987

Answers (2)

Anony Mous
Anony Mous

Reputation: 109

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

Upvotes: 5

mimidatabase
mimidatabase

Reputation: 131

As I cannot comment yet, I will add some input as an answer if anyone arrives on this thread like me today.

Yes, you have to grant permissions on sequence(s) separately, in addition to the privileges already granted on the table(s). In fact it is what you have to do.

Even if your table is dependant on the sequence object, they are both different objects from the RDBMS point of view, so they require distinct privileges.

As for your indices example, index in a sub-object of a table, and no additional privilege is required to be able to make use of indices so no further action than the index creation is needed.

Moreover, be careful to prefix the sequence name in case it is stored in a schema which is not the default one (public) and which is not in your search_path. If that is not the case, you may encounter a permission denied error even though you have all necessary privilege on the sequence you aim to work with.

Upvotes: 2

Related Questions