postgresql - 授予了我的 PostGres 表的所有权限，但在尝试插入/选择时仍然出现 "Permission denied"错误

Question

我正在使用 PostGres 9.5。我在将数据插入到刚创建的表中时遇到问题。尽管向数据库用户授予了所有权限，但我仍收到“权限被拒绝”错误。见下文...

localhost:myapp davea$ psql -Upostgres
Password for user postgres:
psql (9.5.0, server 9.5.1)
Type "help" for help.

postgres=# GRANT ALL ON schema public TO myapp;
GRANT

localhost:myapp davea$ psql -Upostgres
Password for user postgres:
psql (9.5.0, server 9.5.1)
Type "help" for help.

postgres=# GRANT USAGE ON schema public TO myapp;
GRANT
postgres=# \q
localhost:myapp davea$ myapp
psql (9.5.0, server 9.5.1)
Type "help" for help.

myapp=> insert into search_codes (id, code, address_id) values (1, 'atlanta', 'GA');
ERROR:  permission denied for relation search_codes
myapp=> select * FROM search_codes;
ERROR:  permission denied for relation search_codes
myapp=> \d search_codes;
                                Table "public.search_codes"
   Column   |       Type        |                         Modifiers
------------+-------------------+-----------------------------------------------------------
 id         | integer           | not null default nextval('search_codes_id_seq'::regclass)
 code       | character varying |
 address_id | character varying |
Indexes:
    "search_codes_pkey" PRIMARY KEY, btree (id)
    "index_search_codes_on_code" UNIQUE, btree (code)
    "index_search_codes_on_address_id" btree (address_id)

授予权限以便我的用户可以在表中插入和选择的正确方法是什么？

Answer 1

您的第一个命令使您能够列出表(您可以只知道存在)

GRANT USAGE ON SCHEMA public TO myapp

然后您必须将 SELECT、INSERT 等...授予模式 public 中的所有表

GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA public TO myapp

我建议不要将所有权限授予特定应用。

如果你有序列:

 GRANT SELECT, UPDATE, USAGE ON ALL SEQUENCES IN SCHEMA public to myapp

如果你有函数:

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO myapp

那么您的示例将起作用。

但是如果你想让 future 创建的表能够被访问，你仍然需要应用一些命令:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, UPDATE, INSERT, DELETE, TRIGGER ON TABLES TO myapp

Postgresql 有一个非常奇怪的机制，我花了一段时间才理解它!