postgresql用户与权限管理()

pg使用角色的概念管理数据库访问权限,角色是一系列相关权限的集合。为了管理方便,通常把一系列先关的权限赋予给一个角色,如果哪个用户需要这些权限,就把这些角色赋予给响应的用户。 由于用户也拥有一系列的相关权限,为了简化管理,在PG中,角色与用户是没有区别的,一个用户也是一个角色,我们可以把一个用户的权限赋值给另一个用户。

用户和角色在整个数据库实例中是全局的,在同一个实例的不同数据库中,看到的用户都是相同的。

数据库初始化时会创建一个与操作系统同名的超级用户postgres,也可以使用-U:指定超级用户

(一)创建用户和角色

创建用户和角色的语法如下:

-- 创建用户
CREATE USER name [[ WITH ] option [...]]

-- 创建角色
CREATE ROLE name [[ WITH ] option [...]]

备注:在PG中,用户与角色是没有区别的,角色默认没有login权限,无法登陆,如果授予login之后,也可以像用户一样登陆。

option常用选项如下:

  • SUPERUSER | NOSUPERUSER:创建出来的用户是否为超级用户
  • CREATEDB | NOCREATEDB:创建出来的用户是否有create database的权限
  • CREATEROLE | NOCREATEROLE:创建出来的用户是否有创建其它角色的权限
  • CREATEUSER | NOCREATEUSER:创建出来的用户是否有创建其它用户的权限
  • INHERIT | NOINHERIT:确定角色是否继承其它角色的权限
  • LOGIN | NOLOGIN:创建出来的角色是否有登录权限
  • CONNECTION LIMIT n:创建出来的角色并发连接数限制数量,默认值是“-1”,表示没有限制
  • VALID UNTIL ‘timestamp’:密码失效时间

(二)权限的管理

在postgresql数据库中,任何逻辑对象(包括数据库)都是有所有者的,也就是说数据库对象都是属于某个用户的,所以,无需把对象的权限赋予所有者,因为所有者默认就拥有所有的权限,在PG数据库中,删除及其修改对象的权限都不能赋予别的用户,它是所有者的固有权限,不能赋予或撤销,所有者也隐式地拥有把操作该对象的权限授予其他用户的权利。

(2.1)两类权限

用户的权限分两类,一类是在创建用户时就指定的权限,有:

  • 超级用户的权限
  • 创建数据库的权限
  • 是否允许login的权限
  • 更多见\help create role

这些权限是创建用户时指定的,后面可以使用alter role来修改。

另一类是有GRANT和REVOKE命令来管理的,有:

  • 在数据库中创建schema的权限
  • 在指定的数据库中创建临时表的权限
  • 连接某个数据库的权限
  • 在某个数据库中创建数据库对象的权限,如表、视图、函数等
  • 在一些表中做SELECT 、INSERT、UPDATE、DELETE等操作的权限
  • 在一张表的列上做 SELECT 、UPDATE、DELETE等操作的权限
  • 对序列进行查询(执行序列的currval函数)、使用(执行序列的currval和nextval函数)、更新的权限
  • 在表上创建触发器的权限
  • 把表、索引创建到指定表空间的权限

(2.2)GRANT 和REVOKE权限

(2.2.1)角色的授予与回收

-- 将角色授予给另外一个角色
GRANT role_name[,...] TO role_name[,...] WITH ADMIN OPTION;

-- 角色回收
REVOKE role_name[,...] FROM role_name[,...][CASCADE | RESTRICT]

(2.2.2)权限的授予与回收

权限的授予

db1=# \help grant
命令:       GRANT
描述:       定义存取权限
语法:
-- 表相关权限
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] 表名 [, ...]
         | ALL TABLES IN SCHEMA 模式名称 [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
-- 列相关权限
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( 列名称 [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( 列名称 [, ...] ) }
    ON [ TABLE ] 表名 [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
-- 序列相关权限
GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE 序列名称 [, ...]
         | ALL SEQUENCES IN SCHEMA 模式名称 [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
-- 数据库相关权限
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE 数据库名称 [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN 域_名称 [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER 外部数据封装器的名称 [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER 服务器名称 [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
-- 函数、存储过程相关权限
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { { FUNCTION | PROCEDURE | ROUTINE } 程序名称 [ ( [ [ 参数模式 ] [ 参数名称 ] 参数类型 [, ...
] ] ) ] [, ...]
         | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA 模式名称 [, ...] }

(2.2.3)权限授予回收总结

postgresql中的权限是按照以下几个层次进行管理的:

1.首先管理赋予用户的特殊权限,如超级用户的权限,创建数据库的权限、创建用户的权限、login权限等

2.然后是在数据库中创建schema的权限

3.接着是在schema中创建数据库对象(如表、索引)的权限

4.之后是对表进行操作(insert、update、delete、select)的权限

5.最后是操作(update、delete、select)表中某些字段的权限

(2.3)权限操作常用命令

(2.3.1)授予增删查改权限

GRANT update,delete,insert,select ON ALL TABLES IN SCHEMA public TO signalmap_rw;

(2.3.2)回收public权限

在初始化数据库实例后,数据库中默认就会存在一个名称为“public”的schema,任何用户都有在public schema上的create 权限,通常我们需要把这个权限回收回来

revoke create on schema public from public;

(2.3.3)创建只读用户

-- 回收public模式的create权限
revoke create on schema public from public;
-- 创建只读用户,密码为readonly
create user readonly password 'readonly';
-- 授权public模式给readonly
grant usage on schema public to readonly;
-- 授权public模式的所有表权限给readonly用户
grant select on all tables in schema public to readonly;
-- 切换到readonly测试
\c - readonly 
-- 切换到postgres用户
\c - postgres
-- 将public模式的默认表查询权限授予readonly用户
alter default privileges in schema public grant select on tables to readonly;

(2.3.4)查看用户权限

-- 查看某用户的系统权限
SELECT * FROM  pg_roles WHERE rolname='postgres';

-- 查看某用户的表级别权限
select * from information_schema.table_privileges where grantee='postgres';

-- 查看某用户的usage权限
select * from information_schema.usage_privileges where grantee='postgres';

-- 查看某用户在存储过程函数的执行权限
select * from information_schema.routine_privileges where grantee='postgres'; 

-- 查看某用户在某表的列上的权限
select * from information_schema.column_privileges where grantee='postgres'; 

-- 查看当前用户能够访问的数据类型
select * from information_schema.data_type_privileges ; 

-- 查看用户自定义类型上授予的USAGE权限
select * from information_schema.udt_privileges where grantee='postgres';
————————

pg使用角色的概念管理数据库访问权限,角色是一系列相关权限的集合。为了管理方便,通常把一系列先关的权限赋予给一个角色,如果哪个用户需要这些权限,就把这些角色赋予给响应的用户。 由于用户也拥有一系列的相关权限,为了简化管理,在PG中,角色与用户是没有区别的,一个用户也是一个角色,我们可以把一个用户的权限赋值给另一个用户。

用户和角色在整个数据库实例中是全局的,在同一个实例的不同数据库中,看到的用户都是相同的。

数据库初始化时会创建一个与操作系统同名的超级用户postgres,也可以使用-U:指定超级用户

(一)创建用户和角色

创建用户和角色的语法如下:

-- 创建用户
CREATE USER name [[ WITH ] option [...]]

-- 创建角色
CREATE ROLE name [[ WITH ] option [...]]

备注:在PG中,用户与角色是没有区别的,角色默认没有login权限,无法登陆,如果授予login之后,也可以像用户一样登陆。

option常用选项如下:

  • SUPERUSER | NOSUPERUSER:创建出来的用户是否为超级用户
  • CREATEDB | NOCREATEDB:创建出来的用户是否有create database的权限
  • CREATEROLE | NOCREATEROLE:创建出来的用户是否有创建其它角色的权限
  • CREATEUSER | NOCREATEUSER:创建出来的用户是否有创建其它用户的权限
  • INHERIT | NOINHERIT:确定角色是否继承其它角色的权限
  • LOGIN | NOLOGIN:创建出来的角色是否有登录权限
  • CONNECTION LIMIT n:创建出来的角色并发连接数限制数量,默认值是“-1”,表示没有限制
  • VALID UNTIL ‘timestamp’:密码失效时间

(二)权限的管理

在postgresql数据库中,任何逻辑对象(包括数据库)都是有所有者的,也就是说数据库对象都是属于某个用户的,所以,无需把对象的权限赋予所有者,因为所有者默认就拥有所有的权限,在PG数据库中,删除及其修改对象的权限都不能赋予别的用户,它是所有者的固有权限,不能赋予或撤销,所有者也隐式地拥有把操作该对象的权限授予其他用户的权利。

(2.1)两类权限

用户的权限分两类,一类是在创建用户时就指定的权限,有:

  • 超级用户的权限
  • 创建数据库的权限
  • 是否允许login的权限
  • 更多见\help create role

这些权限是创建用户时指定的,后面可以使用alter role来修改。

另一类是有GRANT和REVOKE命令来管理的,有:

  • 在数据库中创建schema的权限
  • 在指定的数据库中创建临时表的权限
  • 连接某个数据库的权限
  • 在某个数据库中创建数据库对象的权限,如表、视图、函数等
  • 在一些表中做SELECT 、INSERT、UPDATE、DELETE等操作的权限
  • 在一张表的列上做 SELECT 、UPDATE、DELETE等操作的权限
  • 对序列进行查询(执行序列的currval函数)、使用(执行序列的currval和nextval函数)、更新的权限
  • 在表上创建触发器的权限
  • 把表、索引创建到指定表空间的权限

(2.2)GRANT 和REVOKE权限

(2.2.1)角色的授予与回收

-- 将角色授予给另外一个角色
GRANT role_name[,...] TO role_name[,...] WITH ADMIN OPTION;

-- 角色回收
REVOKE role_name[,...] FROM role_name[,...][CASCADE | RESTRICT]

(2.2.2)权限的授予与回收

权限的授予

db1=# \help grant
命令:       GRANT
描述:       定义存取权限
语法:
-- 表相关权限
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] 表名 [, ...]
         | ALL TABLES IN SCHEMA 模式名称 [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
-- 列相关权限
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( 列名称 [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( 列名称 [, ...] ) }
    ON [ TABLE ] 表名 [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
-- 序列相关权限
GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE 序列名称 [, ...]
         | ALL SEQUENCES IN SCHEMA 模式名称 [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
-- 数据库相关权限
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE 数据库名称 [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN 域_名称 [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER 外部数据封装器的名称 [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER 服务器名称 [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
-- 函数、存储过程相关权限
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { { FUNCTION | PROCEDURE | ROUTINE } 程序名称 [ ( [ [ 参数模式 ] [ 参数名称 ] 参数类型 [, ...
] ] ) ] [, ...]
         | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA 模式名称 [, ...] }

(2.2.3)权限授予回收总结

postgresql中的权限是按照以下几个层次进行管理的:

1.首先管理赋予用户的特殊权限,如超级用户的权限,创建数据库的权限、创建用户的权限、login权限等

2.然后是在数据库中创建schema的权限

3.接着是在schema中创建数据库对象(如表、索引)的权限

4.之后是对表进行操作(insert、update、delete、select)的权限

5.最后是操作(update、delete、select)表中某些字段的权限

(2.3)权限操作常用命令

(2.3.1)授予增删查改权限

GRANT update,delete,insert,select ON ALL TABLES IN SCHEMA public TO signalmap_rw;

(2.3.2)回收public权限

在初始化数据库实例后,数据库中默认就会存在一个名称为“public”的schema,任何用户都有在public schema上的create 权限,通常我们需要把这个权限回收回来

revoke create on schema public from public;

(2.3.3)创建只读用户

-- 回收public模式的create权限
revoke create on schema public from public;
-- 创建只读用户,密码为readonly
create user readonly password 'readonly';
-- 授权public模式给readonly
grant usage on schema public to readonly;
-- 授权public模式的所有表权限给readonly用户
grant select on all tables in schema public to readonly;
-- 切换到readonly测试
\c - readonly 
-- 切换到postgres用户
\c - postgres
-- 将public模式的默认表查询权限授予readonly用户
alter default privileges in schema public grant select on tables to readonly;

(2.3.4)查看用户权限

-- 查看某用户的系统权限
SELECT * FROM  pg_roles WHERE rolname='postgres';

-- 查看某用户的表级别权限
select * from information_schema.table_privileges where grantee='postgres';

-- 查看某用户的usage权限
select * from information_schema.usage_privileges where grantee='postgres';

-- 查看某用户在存储过程函数的执行权限
select * from information_schema.routine_privileges where grantee='postgres'; 

-- 查看某用户在某表的列上的权限
select * from information_schema.column_privileges where grantee='postgres'; 

-- 查看当前用户能够访问的数据类型
select * from information_schema.data_type_privileges ; 

-- 查看用户自定义类型上授予的USAGE权限
select * from information_schema.udt_privileges where grantee='postgres';