SQL 错误：使用 Postgres 拒绝序列 city_id_seq 的权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9325017/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ERROR: permission denied for sequence cities_id_seq using Postgres
提问by Vampnik
I'm new at postgres (and at database info systems all in all). I ran following sql script on my database:
我是 postgres 的新手（以及数据库信息系统）。我在我的数据库上运行了以下 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;
When, as the user www, trying to:
当用户 www 试图：
insert into cities (name) values ('London');
I get the following error:
我收到以下错误：
ERROR: permission denied for sequence cities_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?
我知道问题出在串行类型上。这就是为什么我将 *_id_seq 的选择、插入和删除权限授予 www。但这并不能解决我的问题。我错过了什么？
回答by kupson
Since PostgreSQL 8.2 you have to use:
从 PostgreSQL 8.2 开始，你必须使用：
GRANT USAGE, SELECT ON SEQUENCE cities_id_seq TO www;
GRANT USAGE - For sequences, this privilege allows the use of the currval and nextval functions.
GRANT USAGE - 对于序列，此权限允许使用 currval 和 nextval 函数。
Also as pointed out by @epic_fil in the comments you can grant permissions to all the sequences in the schema with:
同样正如@epic_fil 在评论中指出的那样，您可以使用以下命令授予对架构中所有序列的权限：
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO www;
回答by Tom Gerken
Since @Phil has a comment getting a lot of upvotes which might not get noticed, I'm using his syntax to add an answer that will grant permissions to a user for all sequences in a schema (assuming your schema is the default 'public')
由于@Phil 的评论获得了很多可能不会引起注意的赞成票，因此我使用他的语法添加了一个答案，该答案将授予用户对架构中所有序列的权限（假设您的架构是默认的“公共” )
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public to www;
回答by Asfand Qazi
@Tom_Gerken, @epic_fil and @kupson are quite correct with their statements to give permissions to work with existing sequences. However, the user will NOT get access rights to sequences created in the future. To do that, you have to combine the GRANT statement with an ALTER DEFAULT PRIVILEGES statement, like so:
@Tom_Gerken、@epic_fil 和 @kupson 的声明非常正确，可以授予使用现有序列的权限。但是，用户将不会获得对将来创建的序列的访问权限。为此，您必须将 GRANT 语句与 ALTER DEFAULT PRIVILEGES 语句结合起来，如下所示：
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO www;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO www;
This only works on PostgreSQL 9+, of course.
当然，这仅适用于 PostgreSQL 9+。
This will append to existing default privileges, not overwrite them, so is quite safe in that regard.
这将附加到现有的默认权限，而不是覆盖它们，因此在这方面非常安全。
回答by Shreeram
Execute the following command in postgres.
在 postgres 中执行以下命令。
login to postgres:
登录postgres：
sudo su postgres;
psql dbname;
CREATE SEQUENCE public.cities_id_seq INCREMENT 1
MINVALUE 0
MAXVALUE 1
START 1 CACHE 1; ALTER TABLE public.cities_id_seq OWNER TO pgowner;
须藤 su postgres;
psql 数据库名；
CREATE SEQUENCE public.cities_id_seq INCREMENT 1
MINVALUE 0
MAXVALUE 1
START 1 CACHE 1; 更改表 public.cities_id_seq 所有者到 pgowner；
pgowner will be your database user.
pgowner 将是您的数据库用户。
