首先,您必須能夠連接到數(shù)據(jù)庫(kù)才能運(yùn)行查詢.這可以通過(guò)以下方式實(shí)現(xiàn)

REVOKE CONNECT ON DATABASE your_database FROM PUBLIC;

GRANT CONNECT

ON DATABASE database_name

TO user_name;

REVOKE是必要的because

The key word PUBLIC indicates that the privileges are to be granted to

all roles,including those that might be created later. PUBLIC can be

thought of as an implicitly defined group that always includes all

roles. Any particular role will have the sum of privileges granted

directly to it,privileges granted to any role it is presently a

member of,and privileges granted to PUBLIC.

如果您真的想將用戶限制為DML語(yǔ)句,那么您還需要做更多的事情：

REVOKE ALL

ON ALL TABLES IN SCHEMA public

FROM PUBLIC;

GRANT SELECT,INSERT,DELETE

ON ALL TABLES IN SCHEMA public

TO user_name;

這些假設(shè)您將只有一個(gè)模式(默認(rèn)情況下名為“public”).

正如杰克道格拉斯指出的那樣,上面只給出了現(xiàn)有表的特權(quán).要在將來(lái)的表中實(shí)現(xiàn)相同的目標(biāo),您必須使用define default privileges：

ALTER DEFAULT PRIVILEGES

FOR ROLE some_role -- Alternatively "FOR USER"

IN SCHEMA public

GRANT SELECT,DELETE ON TABLES TO user_name;

這里,some_role是創(chuàng)建表的角色,而user_name是獲取權(quán)限的角色.定義此項(xiàng),您必須以some_role或其成員身份登錄.

最后,你必須對(duì)序列做同樣的事情(感謝PlaidFan指出它) – 這里是你需要的USAGE特權(quán).

總結(jié)

以上是生活随笔為你收集整理的postgre管理员 无法访问表_postgresql – 授予用户对所有表的访问权限的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。