1.postgres用户和角色的权限
1.组角色管理
1-1 查看用户
psql# \du
SELECT * FROM pg_user;
SELECT * FROM pg_roles;
pg_roles表字段
pg_roles提供访问数据库角色有关信息的接口。 它只是一个pg_authid表的公开可读部分的视图，把口令字段用空白填充了。
| 名字 | 类型 | 描述 |
|---|---|---|
rolname |
name |
角色名 |
rolsuper |
bool |
有超级用户权限的角色 |
rolinherit |
bool |
自动继承属主角色权限的角色 |
rolcreaterole |
bool |
可以创建更多角色的角色 |
rolcreatedb |
bool |
可以创建数据库的角色 |
rolcatupdate |
bool |
可以直接更新系统表的角色。除非这个字段为真，否则超级用户也不能干这个事情。 |
rolcanlogin |
bool |
可以登录的角色，也就是说，这个角色可以给予初始化会话认证的标识符。 |
rolreplication |
bool |
复制的角色。也就是说，这个角色可以初始化流复制(参阅第 25.2.5 节) 和使用pg_start_backup和pg_stop_backup设置/重设系统备份模式。 |
rolconnlimit |
int4 |
对于可以登录的角色，这儿限制了该角色允许发起的最大并发连接数。 -1 表示无限制。 |
rolpassword |
text |
不是口令(总是 ********) |
rolvaliduntil |
timestamptz |
口令失效日期(只用于口令认证)；如果没有失效期，为 NULL |
rolconfig |
text[] |
运行时配置变量的用户指定的缺省 |
oid |
oid |
角色的 ID引用pg_authid.oid |
pg_user表字段
pg_user提供了对数据库用户的相关信息的访问。 这个视图只是一个pg_shadow的公众可读的部分的视图化，它把口令域给刷掉了。
pg_shadow存在是为了向下兼容：它模拟了一个PostgreSQL 版本 8.1 之前的系统表。它显示了所有在pg_authid中标记了rolcanlogin的角色的属性。
| 名字 | 类型 | 描述 |
|---|---|---|
usename |
name |
用户名 |
usesysid |
oid |
用户 ID |
usecreatedb |
bool |
用户可以创建数据库 |
usesuper |
bool |
用户是一个超级用户 |
usecatupd |
bool |
用户可以更新系统表。即使超级用户也不能这么干，除非这个字段为真。 |
userepl |
bool |
用户可以初始化流复制并且使系统处于或离开备份模式。 |
passwd |
text |
不是口令(总是为 ********) |
valuntil |
abstime |
口令失效的时间(只用于口令认证) |
useconfig |
text[] |
运行时配置参数的会话缺省 |
根据用户名查询database权限
select a.datname,b.rolname,string_agg(a.pri_t,',') from (select datname,(aclexplode(COALESCE(datacl, acldefault('d'::"char",datdba)))).grantee as grantee,(aclexplode(COALESCE(datacl, acldefault('d'::"char", datdba)))).privilege_type as pri_t from pg_database where datname not like 'template%') a,pg_roles b where (a.grantee=b.oid or a.grantee=0) and b.rolname='[user_name]' group by a.datname,b.rolname;
根据用户名查询schema权限
select a.nspname,b.rolname,string_agg(a.pri_t,',') from (select nspname,(aclexplode(COALESCE(nspacl, acldefault('n'::"char",nspowner)))).grantee as grantee,(aclexplode(COALESCE(nspacl, acldefault('n'::"char",nspowner)))).privilege_type as pri_t from pg_namespace where nspname not like 'pg%' and nspname <> 'information_schema') a,pg_authid b where (a.grantee=b.oid or a.grantee=0) and b.rolname='[user_name]' group by a.nspname,b.rolname;
根据用户名查询table权限
# 方法一
select table_name,table_schema,grantee,string_agg(privilege_type,',') from information_schema.table_privileges where grantee='[user_name]' group by table_name,table_schema,grantee;
# 方法二
select * from information_schema.table_privileges where grantee='[user_name]';
查看usage权限
select * from information_schema.usage_privileges where grantee='[user_name]';
查看用户自定义类型上授予的USAGE权限
select * from information_schema.udt_privileges where grantee='[user_name]';
1-2 创建组角色
默认情况下，新建立的数据库总是包含一个预定义的“超级用户”角色，并且省略时这个角色名叫postgres。
创建的就是默认在public可以
操作自己的表，其它的表没有权限
角色属性
一个数据库角色可以有一些属性，这些属性只能在create role/user的时候指定，或者通过alter role/user的方式修改，且不可以继承，包括SUPERUSER/NOSUPERUSER、CREATEDB/NOCREATEDB、CREATEROLE/NOCREATEROLE、INHERIT/NOINHERIT、LOGIN/NOLOGIN、REPLICATION/NOREPLICATION、BYPASSRLS/NOBYPASSRLS、CONNECTION LIMIT、PASSWORD、VALID UNTIL，这些属性可以在pg_authid中查看。
在日常维护中，建议创建一个具有CREATEDB和CREATEROLE权限的角色来替换超级管理员角色。我们可以为一些角色设置与角色相关默认参数值，这样在后续的链接中会生效。
# 创建角色
CREATE ROLE [role_name];
# 删除角色
DROP ROLE [role_name];
1-3 查看组角色
角色存在pg_roles系统表中，用户可以通过此表来查看系统中的角色。
SELECT rolname FROM pg_roles;
1-4 修改组角色
ALTER ROLE [old_role_name] RENAME TO [new_role_name];
1-5 删除组角色
DROP ROLE [role_name];
2.角色的各种权限
取消授权时，只需要在关键字前面加上NO
2-1 登录权限
默认情况下，创建的组角色没有登录权限。
一旦组角色拥有了登录权限，即可当作用户名一样来使用。
CREATE ROLE [role_name] LOGIN;
2-2 超级用户权限
超级用户拥有对数据库操作的最高权限，可以完成对数据库的所有权限检查。
不要轻易创建超级用户，最好使用非超级用户完成用户的大多数工作。
CREATE ROLE [role_name] SUPERUSER;
2-3 创建数据库权限
角色要想创建数据库，必须明确给出该权限。
CREATE ROLE [role_name] CREATEDB;
2-4 创建角色权限
一旦角色具有CREATEROLE权限，就可以更改和删除其他角色，还可以给其他角色赋予或者撤销成员关系。当然，如果想对超级用户进行操作，仅有此权限还不够，必须拥有SUPERUSER权限。
CREATE ROLE [role_name] CREATEROLE;
2-5 登录密码
在客户认证方法要求与数据库建立连接时，需要口令权限。
CREATE ROLE [role_name] PASSWORD '[set_you_password]';
2-6 设置连接数
默认-1
CREATE ROLE [role_name] CONNECTION LIMIT 1;
3.账户管理
在PostgreSQL中可以管理用户账号，包括创建用户、删除用户、密码管理等内容。
3-1 创建用户
# 这两句的作用是等价的
CREATE USER [user_name];
CREATE ROLE [role_name] LOGIN;
创建用户名称为user_test，并具有创建数据库和创建角色的权限，同时登录密码为“123456”
# 这两句的作用是等价的
CREATE USER user_test PASSWORD '123456' CREATEDB CREATEROLE;
CREATE ROLE user_test PASSWORD '123456' CREATEDB CREATEROLE LOGIN;
3-2 删除用户
要想删除用户，必须拥有CREATEROLE权限。
DROP USER不能自动关闭任何打开的用户对话。而且，若用户有打开的对话，此时删除用户，则命令不会生效，直到用户对话被关闭后才生效。一旦对话被关闭、用户被取消，此用户再次试图登录时就会失败。
CREATE USER [user_name];
3-3 修改用户密码
ALTER USER [user_name] PASSWORD '[set_you_new_password]';
4.组角色和用户权限管理
权限管理主要是对登录到PostgreSQL的用户进行权限验证。所有用户的权限都存储在PostgreSQL的权限表中。
4-1 授权
对组角色授权
指向
2.角色的各种权限
ALTER ROLE [role_name] CREATEDB CREATEROLE ...;
对用户授权
ALTER USER [user_name] CREATEDB CREATEROLE ...;
4-2 回收权限
收回组角色权限
ALTER ROLE [role_name] NOCREATEDB NOCREATEROLE ...;
收回用户权限
ALTER USER [user_name] NOCREATEDB NOCREATEROLE ...;
4-3 组角色和登录角色之间的区别是什么？
组角色主要是用于赋予权限，然后将登录角色加入组角色中，这样登录角色就拥有了组角色的权限，不用对每个登录角色重新授权，这样就可以将用户组合起来简化权限管理，是一个常用的便利方法。利用这样的方法，可以将权限赋予整个组，也可以对整个组进行撤销。
一般情况下，组角色是不具有登录权限的，虽然用户可以对组角色赋予登录权限。一旦组角色具有登录权限，就可以实现和登录角色一样的功能。
4-4 角色授予
GRANT [role_name] TO [user_name];
4-5 角色收回
REVOKE [role_name] FROM [user_name];
5.权限管理
5-1 权限说明
5-1-1 db、schema、table
db的owner可以授权,无权【查看】和【删除】别人的schema和table
schema的owner拥有者具有该schema的所有权限,可以删除别人创建的table(表创建在你的schema下)，但是不能查看
table的owner拥有者具有该table的所有权限
5-1-2 GRANT
| 关键字 | 解释 |
|---|---|
GRANT |
将某对象（表，视图，序列，函数，过程语言，模式或者表空间） 上的特定权限给予一个用户或者多个用户或者一组用户 |
PUBLIC |
要赋予所有用户, 包括那些以后可能创建的用户。 |
WITH GRANT OPTION |
如果加了这个选项, 权限的受予者也可以赋予别人 |
5-1-3 权限列举
| 权限 | 授权目标 | 说明 |
|---|---|---|
SELECT |
表和视图的所有列、指定的列；序列 | 读取 |
INSERT |
表和视图的所有列、指定的列 | 插入，指定列时，插入语句只能出现指定的列 |
UPDATE |
表和视图的所有列、指定的列；序列 | 更新，指定列时，更新语句只能出现指定的列依赖SELECT权限定位 |
DELETE |
表和视图的所有列，即一整行 | 删除依赖SELECT权限定位 |
TRUNCATE |
表 | 清空整张表 |
REFERENCES |
表的所有列、指定的列 | 创建外键 |
TRIGGER |
表、视图 | 在表或视图上创建触发器 |
CREATE |
database schema tablespace |
database: 允许创建schema、publications、安装插件 schema: 创建新的对象；如果要修改已有对象，你必须是该对象的owner，并且拥有schema的CREATE权限 tablespace: 允许创建表、索引、临时文件；允许创建默认表空间为该表空间子的database |
CONNECT |
database | 允许连接指定的database，这是pg_hba.conf之后，额外的检查 |
TEMPORARY |
临时表 | 允许创建临时表 |
EXECUTE |
函数、存储过程 | 允许执行函数或存储过程 |
USAGE |
schema 序列 类型 |
schema：允许使用该schema内的对象 序列：允许使用 currval和nextval函数类型：允许使用该类型 |
5-2 database级别权限
默认所有人都可以访问db中的public
数据库级的权限有
create、connect、temporary/temp三种
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE dbname [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
5-2-1 connect允许访问
默认PUBLIC角色是有数据库的connect权限的
这个比较简单，用户拥有了数据库的connect权限，才能登录数据库
限制访问
回收PUBLIC角色的connect权限
# 创建db
CREATE DATABASE [db_name];
# 移除所有人访问(所有人访问不了,只有超级用户和database的owner能访问)
REVOKE CONNECT ON DATABASE [db_name] FROM PUBLIC;
# 限制所有人访问public数据库
REVOKE CONNECT ON DATABASE public FROM public;
允许访问
注意: 前提需要先限制所有人访问，这才有效果
直接使用该sql
GRANT CONNECT ON DATABASE [db_name] to [role_name];无效 ,用户还能访问
# 允许所有人访问某个db(默认就有)
grant connect ON DATABASE [db_name] TO public;
# 设置某个用户访问该db(前提该database已经设置了所有人不能访问)
GRANT CONNECT ON DATABASE [db_name] TO [role_name];
GRANT CONNECT ON DATABASE [db_name] TO [user_name];
演示
# 1.创建bbb数据库
CREATE DATABASE bbb;
# 2.移除所有人访问权限
REVOKE CONNECT ON DATABASE bbb FROM PUBLIC;
# 3.在设置都有人不能访问后
test=> \c bbb
connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: permission denied for database "bbb"
DETAIL: User does not have CONNECT privilege.
Previous connection kept
# 4.设置user_t1可以访问bbb数据库
GRANT CONNECT ON DATABASE bbb TO user_t1;
# 5.单独设置user_t1用户访问
test=> \c bbb
psql (14.1, server 10.18)
You are now connected to database "bbb" as user "user_t1".
bbb=>
5-2-2 temporary/temp
默认PUBLIC角色有创建临时表的权限
在数据库中创建临时表的权限
限制/允许所有人创建临时表
# 移除PUBLIC角色的权限(出超级用户和owner都无权创建临时表)
revoke TEMPORARY on DATABASE [db_name] from PUBLIC ;
# 允许所有人创建临时表(默认)
GRANT TEMPORARY on DATABASE [db_name] to PUBLIC ;
限制/允许某个角色或用户
# 添加某个用户、角色创建临时表权限(前提移除PUBLIC角色的权限)
GRANT TEMPORARY ON DATABASE [db_name] TO [role_name];
# 移除某个用户、角色的建临时表权限
# 前提条件(PUBLIC角色无权限，自己有权限时),否则这条sql不起效果★★★★★★
REVOKE TEMPORARY ON DATABASE [db_name] FROM [role_name];
5-2-3 create
默认public无权限创建
赋予数据库create权限，只是允许用户在数据库下创建schema，并不包含创建其他对象的权限。
限制/允许某个角色或用户创建schema
# 允许所某个角色创建schema
GRANT CREATE ON DATABASE [db_name] TO [role_name];
# 移除某个角色创建schema
REVOKE CREATE ON DATABASE [db_name] TO [role_name];
限制/允许所有人创建schema
# 移除所PUBLIC角色创建schema(默认)
REVOKE CREATE ON DATABASE [db_name] TO PUBLIC;
# 允许所PUBLIC角色创建schema
GRANT CREATE ON DATABASE [db_name] TO PUBLIC;
5-2-4 ALL
对于数据库来说，ALL权限就是create、connect、temporary/temp这三个权限集合，并不是所有权限
# PUBLIC角色的权限设置
GRANT ALL ON DATABASE [db_name] TO PUBLIC;
REVOKE ALL ON DATABASE [db_name] from PUBLIC;
# 某个角色的权限设置
GRANT ALL ON DATABASE [db_name] TO [role_name];
REVOKE ALL ON DATABASE [db_name] from [role_name];
5-2-5 查看db权限
# 方法一
\l [db_name]
# 方法二
select datname,datacl from pg_database;
5-2-6 修改数据库的拥有者
ALTER DATABASE [db_name] OWNER TO [new_user_name];
ALTER DATABASE [db_name] OWNER TO [new_role_name];
5-3 schema级别权限
设置时候需要进入表所在的
databaseschema级权限包含
usage和create两个如果该账号有
创建schema的权限，那么只能操作自己创建的schema(owner)，如果要操作他人的schema必须要授权
GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schemaname [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ];
[ GRANTED BY role_specification ]
# 查看当前数据库有多少schema
\dn
5-3-1 usage
要
访问schema中的对象，必须先拥有schema的usage权限。
GRANT USAGE ON SCHEMA [schema_name] TO [user_name];
GRANT USAGE ON SCHEMA [schema_name] TO [role_name];
5-3-2 create
schema的create权限，允许用户在指定的schema中
创建表、sequence、函数、存储过程、视图如果授权了,可以在该
schema下创建表，对表有所有的操作权限
schema的owner不能增、删、改、查、清空该表，但是可以删除该表
GRANT CREATE ON SCHEMA [schema_name] TO [user_name];
GRANT CREATE ON SCHEMA [schema_name] TO [role_name];
5-3-3 ALL
对于schema来说，ALL权限就是
usagecreate权限集合，并不是所有权限
GRANT ALL ON SCHEMA [schema_name] TO [user_name];
GRANT ALL ON SCHEMA [schema_name] TO [role_name];
5-3-4权限查看
# 方法一(查看当前db下的schema权限)
\dn+
# 方法二(查看当前db下的schema权限)
SELECT nspname,nspacl from pg_namespace;
5-3-5 切换schema
默认使用的是public
set search_path to [schema_name];
5-4 table级别权限
设置时候需要进入表所在的
database表级权限包含
SELECT,INSERT,DELTE,UPDATE,TRUNCATE,REFERENCES,TRIGGER
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] tablename [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
5-4-1 读写权限
# 对表增、删、改、查、清空权限
GRANT [select,insert,update,delete,truncate] ON [schema_name].[table_name] TO [new_user_name];
GRANT [select,insert,update,delete,truncate] ON [schema_name].[table_name] TO [new_role_name];
5-4-2 删除表与创建索引
除了
REFERENCES|TRIGGER这两个权限没有测试，我们会发现，缺少了drop 表和创建索引的权限在postgresql数据库中，只有
表的owner或schema的owner才能drop表，和在其上创建索引
user_test=> DROP TABLE public.t1
ERROR: must be owner of table t1
user_test=> CREATE INDEX idx_t1_id on public.t1(id);
ERROR: must be owner of table t1
5-4-3 批量赋权
on all tables in schema只对现有对象生效。
注意：如果在
schema下新增一个表，权限不会自动赋予user_name
GRANT [SELECT,INSERT,DELETE,UPDATE,TRUNCATE] on all tables in schema [schema_name] to [user_name];
postgres=# GRANT SELECT,INSERT,DELETE,UPDATE,TRUNCATE on all tables in schema public to user1;
GRANT
5-4-4 ALL
对于表来说，ALL权限就是所有表权限集合，并不是所有权限
# 针对schema下的单个表
GRANT ALL ON [schema_name].[table_name] TO [new_user_name];
GRANT ALL ON [schema_name].[table_name] TO [new_role_name];
# 针对schema下的所有表
GRANT ALL on all tables in schema [schema_name] TO [new_role_name];
5-4-5 查看权限
# 方法一
\dp
# 方法二
SELECT * from information_schema.table_privileges where grantee='[user_name]';
6.测试效果
6-1 某个db下禁用public权限
# 1.进入要限制访问的db
\c [db_name]
# 2.移除public所有权限
# 移除后所有人都看不到(除超级用户和实例owner)
revoke ALL on SCHEMA public from PUBLIC;
# 3.为login1增加访问public权限
grant USAGE on SCHEMA public to login1 ;
6-2 让用户user_test在数据库aaa下可以创建schema
grant CONNECT on DATABASE aaa to user_test;
grant CREATE on DATABASE aaa to user_test;
6-3 让用户user_test在可以数据库aaa下指定的schemaa1创建表格
注意：现在只能创建表格，但不能使用
增、删、查、改、清空、删表提示ERROR: permission denied for schema a1
# 如果不能连接该数据库的话，授权一下
grant CONNECT on DATABASE aaa to user_test;
# user_test可以在schema[a1]中只能创建表(其他无权限)
grant CREATE on SCHEMA a1 to user_test;
# 可以在schema[a1]中操作自己的表(+增删改查)(+表删除)
# 别的表还是没权限操作(-增删改查)(-表删除)
grant USAGE on SCHEMA a1 to user_test;
6-4 对schema下的table授权
6-4-1 指定schema指定table-查
# 先赋予SCHEMA的使用权限
grant USAGE on SCHEMA a1 to user_test ;
# 在赋予表的查看权限(指定表)
grant SELECT on TABLE a1.testa1 to user_test ;
6-4-2 指定schema下的所有table-查
# 先赋予SCHEMA的使用权限
grant USAGE on SCHEMA a1 to user_test ;
# 在赋予表的查看权限(所有表)
grant SELECT on ALL tables in schema a1 to user_test ;
6-4-3 指定schema下的所有table-增删改查清空
# 先赋予SCHEMA的使用权限
grant USAGE on SCHEMA a1 to user_test ;
# 设置表的所有查看权限(所有表)
grant ALL on ALL tables in schema a1 to user_test ;
7.创建未来权限
7-1 ALTER DEFAULT PRIVILEGES说明
1.
FUNCTIONS和ROUTINES一样的，推荐使用ROUTINES在较早的版中只允许单词FUNCTIONS。2.
FOR USER user_name一个现有角色的名称，当前角色是它的一个成员。如果FOR ROLE被忽略，将假定为当前角色。3.如果
IN SCHEMA被忽略，全局默认特权会被修改。 当设置特权给模式时不能使用IN SCHEMA，因为模式不能嵌套。4.嵌套错误
cannot use IN SCHEMA clause when using GRANT/REVOKE ON SCHEMAS5.
role_name不过这里是为一整类的对象而不是特别指定的对象设置权限。6.需要进入某个db下，并且设置的用户具有该db的访问权限
ALTER DEFAULT PRIVILEGES
[ FOR { ROLE | USER } [user_name] ]
[ IN SCHEMA [schema_name] ]
grant_or_revoke_sql
where grant_or_revoke_sql is one of:
# 设置权限
GRANT { USAGE | CREATE | ALL } ON SCHEMAS TO [role_name]
GRANT { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | ALL } ON TABLES TO [role_name]
GRANT { USAGE | SELECT | UPDATE | ALL } ON SEQUENCES TO [role_name]
GRANT { EXECUTE | ALL } ON { FUNCTIONS | ROUTINES } TO [role_name]
GRANT { USAGE | ALL } ON TYPES TO [role_name]
# 回收权限
REVOKE { USAGE | CREATE | ALL } ON SCHEMAS FROM [role_name]
REVOKE { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | ALL } ON TABLES FROM [role_name]
REVOKE { USAGE | SELECT | UPDATE | ALL } ON SEQUENCES FROM [role_name]
REVOKE { EXECUTE | ALL } ON { FUNCTIONS | ROUTINES } FROM [role_name]
REVOKE { USAGE | ALL } ON TYPES FROM [role_name]
7-2 使用
在某个
schema下,FOR ROLE [role_name]创建的表to [user_name]都可以使用但是换是没有删除表的权限
schema权限
# test2有test1创建的schema权限(不能删除)
alter default privileges for role test1 grant all on schemas to test2;
# test2可以在test1的schema下可以增删改查(test1表),增删改查删表(自己的表)
alter default privileges for role test1 in schema test11 grant all on tables to test2;
table权限
ALTER DEFAULT PRIVILEGES FOR ROLE [role_name] IN SCHEMA [schema_name] grant all on tables to [user_name];
ALTER DEFAULT PRIVILEGES FOR USER [user_name] IN SCHEMA [schema_name] grant all on tables to [user_name];
# 允许test2使用test1在public创建的表(不能删除)
alter default privileges for role test1 in schema public grant all on tables to test2;
# 未写for role，默认当前用户
ALTER DEFAULT PRIVILEGES IN SCHEMA [schema_name] grant all on tables to [user_name];
浙公网安备 33010602011771号