错误：使用Postgres时，序列cities_id_seq的权限被拒绝

Question

我在我的数据库上运行了以下 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

我明白问题出在序列类型上。这就是为什么我授予www用户对*_id_seq的选择、插入和删除权限。然而，这并没有解决我的问题。我错过了什么？

- Tõnis Ojandu

---

赋予序列的插入/删除权限对我来说毫无意义。我很惊讶它居然能够起作用。 - user330315

Answer 1

自从PostgreSQL 8.2版本起，您必须使用：

GRANT USAGE, SELECT ON SEQUENCE cities_id_seq TO www;

GRANT USAGE - 对于序列来说，该特权允许使用currval和nextval函数。

另外，正如@epic_fil在评论中指出的那样，您可以授予模式中所有序列的权限：

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

注意：在执行权限授予命令之前，不要忘记选择数据库 (\c <database_name>)。

- kupson

---

FYI，“. . . ON ALL SEQUENCES IN SCHEMA schema_name” 语法也被支持。 - epic_fil

有趣。我在包含我的序列的表上执行了GRANT all，但似乎这并没有涵盖序列。这也似乎因操作系统而异。 - Kinnard Hockenhull

这怎么可能是真的？我什么时候想要允许用户向表中插入数据，但又不想让他们使用其中一个列自动递增的事实呢？ - Brett Widmeier

需要使用SELECT吗？不应该使用USAGE覆盖所需的内容吗？ - Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ

@BrettWidmeier 没错。对我来说，这种事情被开发人员容忍是令人惊讶的。就好像人们想要在互联网上漫游并阅读无底线的 StackOverflow 线程来修复本应该在开箱即用时就正常工作的问题。 - milosmns

Answer 2

由于@Phil的评论获得了许多赞，但可能不会被注意到，因此我将使用他的语法添加一个答案，该答案将授予用户对模式中所有序列的权限（假设您的模式是默认的“public”）

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

- Tom Gerken

---

请注意，这仅适用于PostgreSQL 9.0及以上版本。要在8中实现相同的效果，您可以执行以下操作：从pg_statio_all_sequences中选择'GRANT USAGE, SELECT ON ' || quote_ident(schemaname) || '.' || quote_ident(relname) || ' TO www;'，其中schemaname =' public'。- Tom Gerken 2天前 - Tom Gerken

那个版本8的解决方法真是救了我一命。一开始我很难弄清楚如何使用它。我不得不使用那个查询来生成新查询列表，然后再逐个执行。有没有什么技巧可以一次性完成呢？此外，在“ON”之后插入“SEQUENCES”。 - Paul Gardiner

Answer 3

@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;

当然，这仅适用于PostgreSQL 9+。

这将附加到现有的默认权限，而不是覆盖它们，在这方面非常安全。

- Asfand Qazi

Answer 4

由于SEQUENCES权限问题导致的。尝试以下命令来解决此问题，

GRANT USAGE, SELECT ON SEQUENCE sequence_name TO user_name;

例如：

GRANT USAGE, SELECT ON SEQUENCE cities_id_seq TO www;

- Codemaker

Answer 5

在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;

pgowner将是您的数据库用户。

- Shreeram