出现ERROR: permission denied for sequence cities_id_seq using Postgres是为什么?怎么解决?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (903)

在数据库上运行下面的sql脚本:

create table cities (
id serial primary key,
name text not null
);

create table reports (
id serial primary key,
cityid integer not null references cities(id),
reportdate date not null,
reporttext text not null
);

create user www with password 'www';

grant select on cities to www;
grant insert on cities to www;
grant delete on cities to www;

grant select on reports to www;
grant insert on reports to www;
grant delete on reports to www;

grant select on cities_id_seq to www;
grant insert on cities_id_seq to www;
grant delete on cities_id_seq to www;

grant select on reports_id_seq to www;
grant insert on reports_id_seq to www;
grant delete on reports_id_seq to www;

当用户www试图:

insert into cities (name) values ('London');

收到以下错误:

ERROR: permission denied for sequence cities_id_seq

知道问题在于串行类型。所以我授予选择,插入和删除* _id_seq到www的权利。但是并不能解决我的问题。

提问于
用户回答回答于

在PostgreSQL 8.2必须使用:

GRANT USAGE, SELECT ON SEQUENCE cities_id_seq TO www;

授予使用 - 对于序列,此权限允许使用currval和nextval函数。

可以使用以下方式将权限授予模式中的所有序列:

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO www;
用户回答回答于

将权限授予用户在架构中的所有序列(假设您的架构是默认的“公共” )

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public to www;

扫码关注云+社区

领取腾讯云代金券