CODEWITHSUNDEEP
postgresql
Tim
Tim

Reputation: 4661

PostgreSQL, unable to update row ( with row level security )

set role qstoolbox;
update contract set contract_reference = 'ok ok ok' where id = 2

The above works Query returned successfully: one row affected, 41 msec execution time.

set role user_role;
update contract set contract_reference = ':( :( :(' where id = 2

This one doesn't Query returned successfully: 0 rows affected, 31 msec execution time.

enter image description here

Permissions seem ok, at least its not clear what I'm doing wrong here.

Row level security has been added to this table with

alter table contract enable row level security;

Along with these policies

create policy view_contract on contract for select using(true);
create policy add_contract on contract for insert to user_role with check(true);
create policy delete_contract on contract for delete to user_role using(true);
create policy change_contract on contract for update to user_role with check(true);

Upvotes: 1

Views: 5801

Answers (2)

Tim
Tim

Reputation: 4661

Ok I was missing something in this line

create policy change_contract on contract for update to user_role with check(true);

Needs to be

create policy change_contract on contract for update to user_role using(true) with check(true);

Note I was missing using(true)

Thanks to @klin for making me study the docs in more detail

Upvotes: 1

klin
klin

Reputation: 121754

Define a row security policy or disable row security on the table. Per the documentation:

When row security is enabled on a table (with ALTER TABLE ... ENABLE ROW LEVEL SECURITY), all normal access to the table for selecting rows or modifying rows must be allowed by a row security policy. (However, the table's owner is typically not subject to row security policies.) If no policy exists for the table, a default-deny policy is used, meaning that no rows are visible or can be modified.

Upvotes: 2

Related Questions