组角色管理

组角色管理
查看用户
创建组角色
查看组角色
修改组角色
删除组角色
角色的各种权限
登录权限
超级用户权限
创建角色权限
登录密码
设置连接数
账户管理
创建账号
删除用户
修改用户密码
组角色和用户权限管理
授权
用户授权
回收权限
收回用户权限
组角色和登录角色之间的区别是什么
角色授予
角色收回
权限管理
权限说明
db、schema、table
Database级别权限
connect允许访问
table级别权限
测试效果
某个db下禁用public权限
让用户user_test在数据库aaa下可以创建schema
让用户user_test在可以数据库aaa下指定的schemaa1创建表格
对schema下的table授权
指定schema指定table-查
指定schema下的所有table-查
指定schema下的所有table-增删改查清空
创建未来权限
ALTER DEFAULT PRIVILEGES说明
使用
schema权限
table权限

查看用户

psql# \du
SELECT * FROM pg_user;
SELECT * FROM pg_roles;

pg_roles表字段

pg_roles提供访问数据库角色有关信息的接口。 它只是一个pg_authid 表的公开可读部分的视图，把口令字段用空白填充了。

pg_user表字段

pg_user提供了对数据库用户的相关信息的访问。这个视图只是一个pg_shadow 的公众可读的部分的视图化，它把口令域给刷掉了。
pg_shadow存在是为了向下兼容：它模拟了一个PostgreSQL 版本 8.1 之前的系统表。它显示了所有在pg_authid 中标记了rolcanlogin的角色的属性。

根据用户名查询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]';

创建组角色

默认情况下，新建立的数据库总是包含一个预定义的“超级用户”角色，并且省略时这个角色名叫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];

查看组角色

角色存在pg_roles系统表中，用户可以通过此表来查看系统中的角色。
```
SELECT rolname FROM pg_roles;
```

修改组角色

ALTER ROLE [old_role_name] RENAME TO [new_role_name];

删除组角色

DROP ROLE [role_name];

角色的各种权限

取消授权时，只需要在关键字前面加上NO

登录权限

默认情况下，创建的组角色没有登录权限。
一旦组角色拥有了登录权限，即可当作用户名一样来使用。
```
CREATE ROLE [role_name] LOGIN;
```

超级用户权限

超级用户拥有对数据库操作的最高权限，可以完成对数据库的所有权限检查。
不要轻易创建超级用户，最好使用非超级用户完成用户的大多数工作。
```
CREATE ROLE [role_name] SUPERUSER;
```

创建角色权限

一旦角色具有CREATEROLE权限，就可以更改和删除其他角色，还可以给其他角色赋予或者撤销成员关系。当然，如果想对超级用户进行操作，仅有此权限还不够，必须拥有SUPERUSER权限。
```
CREATE ROLE [role_name] CREATEROLE;
```

登录密码

在客户认证方法要求与数据库建立连接时，需要口令权限。
```
CREATE ROLE [role_name] PASSWORD '[set_you_password]';
```

设置连接数

CREATE ROLE [role_name] CONNECTION LIMIT 1;

账户管理

在PostgreSQL中可以管理用户账号，包括创建用户、删除用户、密码管理等内容。

创建账号

# 这两句的作用是等价的
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;

删除用户

要想删除用户，必须拥有CREATEROLE权限。
DROP USER不能自动关闭任何打开的用户对话。而且，若用户有打开的对话，此时删除用户，则命令不会生效，直到用户对话被关闭后才生效。一旦对话被关闭、用户被取消，此用户再次试图登录时就会失败。
```
CREATE USER [user_name];
```

修改用户密码

ALTER USER [user_name] PASSWORD '[set_you_new_password]';

组角色和用户权限管理

权限管理主要是对登录到PostgreSQL的用户进行权限验证。所有用户的权限都存储在PostgreSQL的权限表中。

授权

对组角色授权

ALTER ROLE [role_name] CREATEDB CREATEROLE ...;

用户授权

ALTER USER [user_name] CREATEDB CREATEROLE ...;

回收权限

收回组角色权限

ALTER ROLE [role_name] NOCREATEDB NOCREATEROLE ...;

收回用户权限

ALTER USER [user_name] NOCREATEDB NOCREATEROLE ...;

组角色和登录角色之间的区别是什么

组角色主要是用于赋予权限，然后将登录角色加入组角色中，这样登录角色就拥有了组角色的权限，不用对每个登录角色重新授权，这样就可以将用户组合起来简化权限管理，是一个常用的便利方法。利用这样的方法，可以将权限赋予整个组，也可以对整个组进行撤销。
一般情况下，组角色是不具有登录权限的，虽然用户可以对组角色赋予登录权限。一旦组角色具有登录权限，就可以实现和登录角色一样的功能。

角色授予

GRANT [role_name] TO [user_name];

角色收回

REVOKE [role_name] FROM [user_name];

权限管理

权限说明

db、schema、table

db的owner可以授权,无权【查看】和【删除】别人的schema和table
schema的owner拥有者具有该schema的所有权限,可以删除别人创建的table(表创建在你的schema下)，但是不能查看
table的owner拥有者具有该table的所有权限

GRANT
权限列举

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 ]

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;

允许访问
注意: 前提需要先限制所有人访问，这才有效果
直接使用该sqlGRANT 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=>

temporary/temp
- 在数据库中创建临时表的权限

限制/允许所有人创建临时表

# 移除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];

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;

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

查看DBA权限

# 方法一
\l [db_name]
# 方法二
select datname,datacl from pg_database;

修改数据库的拥有者

ALTER DATABASE [db_name] OWNER TO [new_user_name];
ALTER DATABASE [db_name] OWNER TO [new_role_name];

schema级别权限
- 设置时候需要进入表所在的database
- schema级权限包含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

Usage
- 要访问schema中的对象，必须先拥有schema的usage权限。

GRANT USAGE ON SCHEMA [schema_name] TO [user_name];
GRANT USAGE ON SCHEMA [schema_name] TO [role_name];

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

ALL
- 对于schema来说，ALL权限就是usage create权限集合，并不是所有权限

GRANT ALL ON SCHEMA [schema_name] TO [user_name];
GRANT ALL ON SCHEMA [schema_name] TO [role_name];

权限查看

# 方法一(查看当前db下的schema权限)
\dn+
# 方法二(查看当前db下的schema权限)
SELECT nspname,nspacl from pg_namespace;

切换schema
- 默认使用的是public

set search_path to [schema_name];

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 ]

读写权限

# 对表增、删、改、查、清空权限
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];

删除表与创建索引
- 除了 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

批量赋权
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

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

查看权限

# 方法一
\dp

# 方法二
SELECT * from information_schema.table_privileges where grantee='[user_name]';

测试效果

某个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 ;

让用户user_test在数据库aaa下可以创建schema

grant CONNECT on DATABASE aaa to user_test;

grant CREATE on DATABASE aaa to user_test;

让用户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;

对schema下的table授权

指定schema指定table-查

# 先赋予SCHEMA的使用权限
grant USAGE on SCHEMA a1 to user_test ;
# 在赋予表的查看权限(指定表)
grant SELECT on TABLE a1.testa1 to user_test ;

指定schema下的所有table-查

# 先赋予SCHEMA的使用权限
grant USAGE on SCHEMA a1 to user_test ;
# 在赋予表的查看权限(所有表)
grant SELECT on ALL tables in schema a1 to user_test ;

指定schema下的所有table-增删改查清空

# 先赋予SCHEMA的使用权限
grant USAGE on SCHEMA a1 to user_test ;
# 设置表的所有查看权限(所有表)
grant ALL on ALL tables in schema a1 to user_test ;

创建未来权限

ALTER DEFAULT PRIVILEGES说明

FUNCTIONS和ROUTINES一样的，推荐使用ROUTINES在较早的版中只允许单词FUNCTIONS。
FOR USER user_name一个现有角色的名称，当前角色是它的一个成员。如果FOR ROLE被忽略，将假定为当前角色。
如果IN SCHEMA被忽略，全局默认特权会被修改。当设置特权给模式时不能使用IN SCHEMA，因为模式不能嵌套。
嵌套错误cannot use IN SCHEMA clause when using GRANT/REVOKE ON SCHEMAS
role_name不过这里是为一整类的对象而不是特别指定的对象设置权限。
需要进入某个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]

使用

在某个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];

文档更新时间: 2023-06-02 15:57 作者：阿力