2

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?

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.