☰

Postgresql：GRANT ALL PRIVILEGES ON DATABASE 起什么作用？

tag-icon • tag-icon permissions sql postgresql
Postgresql：GRANT ALL PRIVILEGES ON DATABASE 起什么作用？

我正在尝试将给定数据库的所有表的所有权限授予新的 postgres 用户（不是所有者）。 似乎GRANT ALL PRIVILEGES ON DATABASE my_db TO new_user;没有这样做。 成功运行上述命令后（以 postgres 用户身份），我以 new_user 身份获得以下内容：

$ psql -d my_db
my_db => SELECT * FROM a_table_in_my_db;
ERROR:  permission denied for relation a_table_in_my_db

两个问题：

1)如果不授予 my_db 上所有表的所有权限，上述命令会做什么？

2）向用户授予所有表的所有权限的正确方法是什么？（包括将来创建的所有表）

答案1

您的问题的答案来自在线 PostgreSQL 8.4 文档。

  1. GRANT ALL PRIVILEGES ON DATABASE将数据库的CREATE、CONNECT和TEMPORARY权限授予角色（用户正确称为角色)。实际上，这些权限都不允许角色从表中读取数据；SELECT要执行该操作，需要具有表上的权限。

  2. 我不确定是否有一种“正确”的方法可以将所有表上的所有权限授予角色。确保给定角色拥有表上所有权限的最佳方法是确保该角色拥有表。默认情况下，每个新创建的对象都归创建它的角色所有，因此如果您希望角色拥有表的所有权限，请使用该角色来创建它。

    PostgreSQL 9.0 引入了以下语法几乎你想要什么：

    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO new_user;

    问题是，如果您在默认“公共”架构之外的架构中创建表，则此规则GRANT不适用于它们。如果您确实使用非公共架构，则您必须GRANT单独拥有这些架构的权限。

答案2

可以设置多个登录名作为数据库所有者：

  • 创建一个“nologin”角色来充当所有者：CREATE ROLE dbowner NOLOGIN
  • 将数据库的所有者更改为：ALTER DATABASE mydb OWNER TO dbowner
  • 授予所有登录名给这个新角色：GRANT dbowner TO user1, user2

现在，如果 user1 或 user2 登录，他们将拥有“mydb”的所有权限，无需任何进一步的授予。

但是，我会仔细考虑这个解决方案。让您的 Web 应用程序使用其中一个登录名来避免在更新架构时创建额外授权的麻烦是诱人的，但这样一来，您就删除了一种非常有用的保护形式。如果您确实需要多个“管理员”，请使用上述解决方案，但对于“正常使用”应用程序的登录名，请坚持使用上述“授予架构中所有表的所有权限...”模式。

答案3

在 PostgreSQL 12 及更高版本中，可以授予数据库中表的所有权限角色 / 用户 / 帐户。

语法是：

GRANT ALL ON table_name TO role_name;

如果你想将其授予数据库中的所有表那么语法将是：

GRANT ALL ON ALL TABLES TO role_name;

如果你想将其授予数据库中某个架构的所有表那么语法将是：

GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO role_name;

我是这样做的：

首先，我登录到 Postgres 数据库服务器：

psql -U postgres

输出

psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
Type "help" for help.

接下来，我创建了一个数据库用户/角色/帐户：

CREATE ROLE alice4 WITH LOGIN PASSWORD 'securePass1';

输出

CREATE ROLE

接下来，我在 postgres 数据库中创建了一个表：

create table candidates (
    candidate_id int generated always as identity,
    first_name varchar(100) not null,
    last_name varchar(100) not null,
    email varchar(255) not null unique,
    phone varchar(25) not null,
    primary key(candidate_id)
);

输出

CREATE TABLE

接下来，我将candidates表上的所有权限授予帐户/用户/角色alice4

GRANT ALL ON candidates TO alice4;

输出

GRANT

接下来，退出 postgres 数据库和postgres帐户/用户/角色：

exit

alice4接下来，我使用帐户/用户/角色登录 Postgres 数据库：

psql -U alice4 -W postgres

输出

Password: 
psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
Type "help" for help.

接下来，我将数据插入到candidates之前创建的表中：

INSERT INTO candidates(first_name, last_name, email, phone)
VALUES('Joe','Com','[email protected]','408-111-2222');

输出

INSERT 0 1

接下来，我选择了candidates之前创建的表中的所有数据：

SELECT * FROM candidates;

输出

 candidate_id | first_name | last_name |        email        |    phone     
--------------+------------+-----------+---------------------+--------------
            1 | Joe        | Com       | [email protected] | 408-111-2222
(1 row)

资源：PostgreSQL 授予

就这样

我希望这有帮助

答案4

从 PostgreSQL 11 开始，Chris Cogdon 的答案有所改进：

  1. 创建一个拥有具有 NOLOGIN 的数据库对象的角色（在他的示例中为：dbowner）
  2. 将其授予所需用户
  3. 要更改架构，请在每个会话开始时切换到应该拥有的角色： 设置角色 dbowner；

然后，所有创建的数据库对象都将属于 dbowner（也包括序列，...），因此也属于其他管理员，而无需明确更改所有权。

如果 dbrole 的权限低于您当前用户的权限，您将必须重新连接才能重新获得权限。

资源：PostgreSQL：设置角色

相关内容