PostgreSQL 教程: GRANT 授权

九月 24, 2023

摘要:在本教程中,您将学习如何使用 PostgreSQL 的GRANT语句,向角色授予数据库对象的权限。

PostgreSQL GRANT 语句简介

通过指定LOGIN属性创建一个角色后,该角色可以登录 PostgreSQL 数据库服务器。但是,它不能对表、视图函数等数据库对象执行任何操作。

例如,用户角色无法从表中查询数据或执行特定函数。

为了让用户角色能够与数据库对象进行交互,需要通过GRANT语句将数据库对象的权限授予用户角色。

下面显示了向角色授予表上的一项或多项特权的GRANT语句的简单形式:

GRANT privilege_list | ALL 
ON  table_name
TO  role_name;

在这个语法中:

  • 首先,指定privilege_list,可以是SELECTINSERTUPDATEDELETETRUNCATE等。您可以使用ALL选项将表上的所有权限授予该角色。
  • 其次,在ON关键字后面指定表的名称。
  • 第三,指定要授予权限的角色的名称。

PostgreSQL GRANT 语句示例

首先,使用postgres用户,使用您选择的任何客户端工具连接到 PostgreSQL 数据库服务器。

其次,创建一个新用户角色,名为joe,可以登录 PostgreSQL 数据库服务器:

create role joe 
login 
password 'Abcd1234';

第三步,创建一个新表,名为candidates

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

第四步,使用角色joe在单独的会话中登录 PostgreSQL 数据库服务器。

第五步,尝试从joe的会话中查询candidates表中的数据:

SELECT * FROM candidates;

PostgreSQL 发出错误:

ERROR:  permission denied for table candidates

要将candidates表的SELECT权限授予角色joe,请在postgres的会话中执行以下GRANT语句:

GRANT SELECT 
ON candidates 
TO joe;

第五步,从joe的会话中执行SELECT语句:

SELECT * FROM candidates;

PostgreSQL 返回空结果集而不是错误。

第七步,执行以下INSERT语句:

INSERT INTO candidates(first_name, last_name, email, phone)
VALUES('Joe','Com','joe.com@example.com','408-111-2222');

PostgreSQL 发出以下错误,因为joe没有在candidates表上的INSERT权限:

ERROR:  permission denied for table candidates

第八步,向角色joe授予candidates表上的INSERTUPDATEDELETE权限:

GRANT INSERT, UPDATE, DELETE
ON candidates 
TO joe;

第九步,从joe的会话中再次执行INSERT语句:

INSERT INTO candidates(first_name, last_name, email, phone)
VALUES('Joe','Com','joe.com@example.com','408-111-2222');

现在,joe可以向candidates表中插入数据了。此外,它还可以更新或删除表中的数据。

更多 PostgreSQL GRANT 示例

让我们再看一些使用GRANT语句的示例。

将表上的所有权限授予角色

以下语句将candidates表上的所有权限授予角色joe

GRANT ALL
ON candidates
TO joe;

将模式中所有表的所有权限授予角色

以下语句将示例数据库dvdrentalpublic模式中所有表的所有权限授予角色joe

GRANT ALL
ON ALL TABLES
IN SCHEMA "public"
TO joe;

授予所有表的 SELECT 权限

有时,您想要创建一个只读角色,该角色只能从指定模式中的所有表中查询数据。

为此,您可以授予public模式中所有表的SELECT权限,如下所示:

GRANT SELECT
ON ALL TABLES
IN SCHEMA "public"
TO reader;

到目前为止,您已经学习了如何授予表权限。要授予其他对象的权限,请查看GRANT语句语法

概括

  • 使用GRANT语句将数据库对象的权限授予角色。