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.