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;

允许访问

注意: 前提需要先限制所有人访问，这才有效果

直接使用该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=>

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级别权限

设置时候需要进入表所在的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

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权限就是usage create权限集合，并不是所有权限

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`下指定的schema`a1`创建表格

注意：现在只能创建表格，但不能使用

增、删、查、改、清空、删表提示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 SCHEMAS

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

posted @ 2022-07-15 18:12 lxd670 阅读(2929) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

lxd670