PostgreSQL基础教程之：各种权限

文章来源：更新时间:2020/5/6 15:26:12

在线老师点击咨询：

最新学讯：近期OCP认证正在报名中，因考试人员较多请尽快报名获取最近考试时间，报名费用请联系在线老师，甲骨文官方认证，报名从速！

我要咨询

1. PostgreSQL权限概要

每个数据库对象都有一个所有者，默认情况下，所有者拥有该对象的所有权限

在数据库中所有的权限都和角色挂钩

权限分为两部分：

系统权限或者数据库用户的属性

数据库对象上的操作权限(内置权限)

对超级用户不做权限检查，其它用户走ACL(Access ControL List)

对于数据库对象，开始只有所有者和超级用户可以做任何操作，其它走ACL

2. 实例权限

由pg_hba.conf来控制

示例：

# TYPE DATABASE USER ADDRESS METHOD

# IPv4 local connections:

host all all 127.0.0.1/32 trust

host all postgres 0.0.0.0/0 reject

host all all 0.0.0.0/0 md5

以下内容主要由指令grant\revoke完成用户角色权限授予与取消

在线帮助：\h grant

3. 表空间权限

语法：

GRANT { CREATE | ALL [ PRIVILEGES ] } ON TABLESPACE tablespace_name [, ...] TO role_specification [, ...] [ WITH GRANT OPTION ]

示例：

表空间路径：/home/postgres/data/user_t

创建表空间：CREATE TABLESPACE tbls_t OWNER user_t LOCATION '/home/postgres/data/user_t';

表空间授权：grant create on tablespace tbls_t to u3;

查看权限：\db+

4. 数据库权限

包括允许连接数据库，允许在数据库中创建schema。

默认情况下，数据库在创建后

允许public角色连接，即允许任何人连接。

不允许除了超级用户和owner之外的任何人在数据库中创建schema。

会自动创建名为public 的schema，这个schema的all权限已经赋予给public角色，即允许任何人在里面创建对象。

语法：

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }

ON DATABASE database_name [, ...]

TO role_specification [, ...] [ WITH GRANT OPTION ]

备注：WITH GRANT OPTION表示被赋予权限的用户，拿到对应的权限后，还能将对应的权限赋予给其他人，否则只能自己有这个权限，但是不能再赋予给其他人

示例：

create database testdb tablespace tbls_t;

grant connect on database testdb to u1;

#连接验证

\c testdb u1

#创建对象验证

create table t1(id int);

#注意：这是创建表指定表空间后，还会存放在默认表空间中，这里是涉及到表空间的授权。

create table t1(id int) tablespace tbls_t;

#可以创建表对象，存放到默认表空间中(reltablespace字段值为0)

select relname ,reltablespace,relowner from pg_class where relname='t1';

5. schema权限

包括允许查看schema中的对象，允许在schema中创建对象;

默认情况下新建的schema的权限不会赋予给public角色;

因此除了超级用户和owner，任何人都没有权限查看schema中的对象或者在schema中新建对象。

语法：

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }

ON SCHEMA schema_name [, ...]

TO role_specification [, ...] [ WITH GRANT OPTION ]

6. 对象级别的权限

每种类型的对象权限属性都不一样，以表对象为例：

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }

[, ...] | ALL [ PRIVILEGES ] }

ON { [ TABLE ] table_name [, ...]

| ALL TABLES IN SCHEMA schema_name [, ...] }

TO role_specification [, ...] [ WITH GRANT OPTION ]

7. 取消授权

REVOKE

语法格式如下:

REVOKE permission_type ON table_name FROM user_name;

其中permission_type和table_name含义与GRANT指令中相同

\h revoke

8. 查看理解权限

显示角色属性(包含系统权限)

\du

或

\du+ [username]

查看系统表

select * from pg_roles[pg_user];

查看某用户或角色的权限

select * from information_schema.table_privileges where grantee='u1';

显示对象的访问权限列表

或

\dp [tablename]

#得到权限说明如下

--------+-----------------+----------+--------------------------------+-------------------+----------

解释一下 Access privileges

rolename=xxx 其中rolename就是被赋予权限的用户名，即权限被赋予给谁了?

=xxx 表示这个权限赋予给了public角色，即所有人

/yyyy 表示是谁赋予的这个权限

权限的含义如下：

rolename=xxxx -- privileges granted to a role

=xxxx -- privileges granted to PUBLIC

r -- SELECT ("read")

w -- UPDATE ("write")

a -- INSERT ("append")

d -- DELETE

D -- TRUNCATE

x -- REFERENCES

t -- TRIGGER

X -- EXECUTE

U -- USAGE

C -- CREATE

c -- CONNECT

T -- TEMPORARY

arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)

* -- grant option for preceding privilege

/yyyy -- role that granted this privilege

示例：用户权限授予与取消

\c postgres postgres

create database testdb

\c testdb postgres

create table t1(id int) ;

select * from information_schema.table_privileges where grantee='u1';

\dp t1

create user u1;

grant all on table t1 to u1;

select * from information_schema.table_privileges where grantee='u1';

\dp t1

revoke select on table t1 from u2;