主页 / user-190774

Zazck's questions

Martin Hope
Zazck
Asked: 2019-09-13 03:33:18 +0800 CST
  • 2

我想创建一个标签文本的有序列表，所以我编写了以下初始脚本： 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期望为当前用户获得最大的 order_id并插入一些带有增加的 order_id 的新标签文本。但是，结果看起来不同：

**Query #1**

    select * from tbl2;

| uid  | order_id | labeltext |
| ---- | -------- | --------- |
| boom | 1        | foo       |
| boom | 2        | bar       |
| bang | 3        | baz       |

在 DB Fiddle 上查看

期望用户bang创建的行具有order_id = 1，但实际上...... 它仍然能够无限制地选择其他用户的order_id

更新时如何使用RLS限制order_id的选择？如果这不适用于 RLS，还有其他选择吗？

postgresql row-level-security