restrict data from select with Row-Level Security when updating

Question

I want to create an ordered list of labeltext, so I wrote the following initial script: Schema (PostgreSQL v10.0)

create table tbl1 (
 uid text primary key /* user id */
);
create table tbl2 (
 uid text not null default current_setting('custom.setting.uid', true)::text,
 order_id int not null,
 labeltext text,
 foreign key (uid) references tbl1 (uid) on delete cascade
);
INSERT INTO tbl1 (uid) VALUES ('boom');
INSERT INTO tbl1 (uid) VALUES ('bang');
INSERT INTO tbl1 (uid) VALUES ('bash');
alter table tbl2 enable row level security;
 create policy all_order on tbl2 for all
 using (uid = current_setting('custom.setting.uid', true)::text)
 with check (uid = current_setting('custom.setting.uid', true)::text);
create or replace function tfn(text) returns int as $$
declare
tmp int;
begin
insert into tbl2(order_id, labeltext)
 select coalesce(
 (select order_id
 from tbl2
 order by order_id desc limit 1
 ),
 0
 ) + 1, 1ドル
 returning order_id into tmp;
return tmp;
end;
$$ language plpgsql;
set custom.setting.uid to 'boom';
show custom.setting.uid;
select tfn('foo');
select tfn('bar');
set custom.setting.uid to 'bang';
select tfn('baz');

tfn expected to get the greatest order_id for current user and insert some new labeltext with the increased order_id. but however, the results look different:

**Query #1**
 select * from tbl2;
| uid | order_id | labeltext |
| ---- | -------- | --------- |
| boom | 1 | foo |
| boom | 2 | bar |
| bang | 3 | baz |

View on DB Fiddle

expect row created by user bang to have order_id = 1, but actually... it's still able to select other user's order_id without restrictions

How to use RLS to limit the selection of order_id when updating? If this isn't doable with RLS, any alternative?

Answer 1

By default, row level security does not apply to the owner of the table.

If you want that, you got to

ALTER TABLE tbl2 FORCE ROW LEVEL SECURITY;

There are some other settings that may cause RLS not to be used:

• If the configuration parameter row_security is off.

• If the user is a superuser.

• If the user was created with BYPASSRLS.