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 |
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?