1
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);
Tim
  • 4,471
  • 5
  • 36
  • 42

2 Answers2

2

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.

klin
  • 112,967
  • 15
  • 204
  • 232
  • I think I'm doing this already, have updated question, also I think this is what is being displayed in the pgadmin screen shot? – Tim Oct 05 '16 at 02:20
  • 1
    Yes, you were mising `using()`. The picture do not show security policies. – klin Oct 05 '16 at 02:54
1

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

Tim
  • 4,471
  • 5
  • 36
  • 42