错误:使用Postgres拒绝序列city_id_seq的权限 [英] ERROR: permission denied for sequence cities_id_seq using Postgres

问题描述

我是postgres的新手(以及所有数据库信息系统的新手).我在数据库上运行了以下sql脚本:

I'm new at postgres (and at database info systems all in all). I ran following sql script on my database:

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身份尝试:

When, as the user www, trying to:

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

我收到以下错误:

ERROR: permission denied for sequence cities_id_seq

我知道问题出在串行类型上.这就是为什么我向www授予* _id_seq的选择，插入和删除权限的原因.但这不能解决我的问题.我想念什么?

I get that the problem lies with the serial type. That's why I grant select, insert and delete rights for the *_id_seq to www. Yet this does not fix my problem. What am I missing?

推荐答案

自PostgreSQL 8.2起，您必须使用:

Since PostgreSQL 8.2 you have to use:

GRANT USAGE, SELECT ON SEQUENCE cities_id_seq TO www;

图形使用-对于序列，此特权允许使用currval和nextval函数.

GRANT USAGE - For sequences, this privilege allows the use of the currval and nextval functions.

此外，正如@epic_fil在注释中指出的那样，您还可以使用以下方式向架构中的所有序列授予权限:

Also as pointed out by @epic_fil in the comments you can grant permissions to all the sequences in the schema with:

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

这篇关于错误:使用Postgres拒绝序列city_id_seq的权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！