PostgreSQL教程（6）用户权限与角色管理
一、PostgreSQL 用户管理
用户用于访问和管理PostgreSQL数据库中的各种对象。用户分为管理员用户和普通用户，管理员用户是在实例初始化时自动创建的，这个用户的名称与初始化该数据库的操作系统用户名相同。比如使用的是操作系统中的postgres用户进行的数据库初始化，那么数据库超级用户的名称也为postgres。普通用户则是后期自行创建的。
PostgreSQL在创建用户的时候支持大量的选项，以下为常用选项说明：
· SUPERUSER | NOSUPERUSER
创建的用户是否为超级用户，在初始化数据库时，默认会创建一个超级用户， 这个用户的名称与初始化该数据库的操作系统用户名相同
· CREATEDB | NOCREATEDB：
创建的用户是否具有CREATE DATABASE的权限，默认是没有的
· CREATEROLE | NOCREATEROLE：
创建出来的用户是否具有创建角色的权限，默认是没有的
· INHERIT | NOINHERIT
如果创建的用户拥有某个或某几个角色， 这时若指定INHERIT， 则表示用户自动拥有相应角色的权限， 否则该用户没有相应角色的权限
· LOGIN | NOLOGIN
用户是否具有LOGIN权限
· REPLICATION | NOREPLICATION
用户是否具有复制权限
· CONNECTION LIMIT connlimit
用户最大并发连接数， 默认“-1”表示没有限制
· [ENCRYPTED|UNENCRYPTED]PASSWORD 'password'：
用户密码
· VALID UNTIL 'timestamp'
密码失效时间，该参数不指定的话默认永久有效。有时候账号登录不上的时候不一定是密码错，而是密码超过有效期了
· IN ROLE role_name [,...]
指定用户成为哪些角色的成员
· ROLE role_name [,...]
成为role_name所指定的新角色成员
· ADMIN role_name [,...]
拥有新建角色的WITH ADMIN OPTION权限
#语法格式 CREATE USER name [ [ WITH ] option [ ... ] ] #创建没有密码的用户 create user user0; #创建用户的同时配置密码 create user user1 password 'postgres'; #创建用户的同时配置账号有效时间，写--infinity代表永久有效，也可以忽略VALID UNTIL选项不写 create user user2 password 'postgres' VALID UNTIL '2024-1-1 00:00:00'; create user user2 password 'postgres' VALID UNTIL 'infinity'; #创建具有 创建数据库和管理角色权限 的用户 create user user3 password 'postgres' CREATEDB CREATEROLE; #创建具有超级权限的用户 create user user4 password 'postgres' SUPERUSER; #创建复制账号 create user repl REPLICATION PASSWORD 'repl'; #修改用户密码 alter user user1 password 'xxx'; #移除用户密码 alter user user1 password null; #更改密码失效日期 alter user user4 VALID UNTIL '2024-1-1 00:00:00'; #让密码永久生效 alter user user2 VALID UNTIL 'infinity'; #修改用户系统权限，让其拥有createdb和createrole的权限 alter user user1 createdb; #删除用户 drop user user1;
二、PostgreSQL 权限管理
· 可以在创建用户时指定的对数据库进行管理的权限，包含：SUPERUSER、CREATEDB、CREATEROLE、LOGIN、REPLICATION，这些权限只能通过ALTER USER/ROLE命令进行修改。
· 由GRANT和REVOKE命令进行管理的对数据进行管理的权限，比增删改查、视图、触发器、函数等
· 另每个对象都有一个owner，owner默认拥有该对象的所有权限，无需再进行授权。
· plubic代表所有用户
1、查看PostgreSQL权限
· 使用\dp命令查看用户权限
在PostgreSQL 中，\dp 命令用于显示数据库中的对象（如表、视图、序列）的权限信息。包含每个对象的所有者（Owner）、授权用户（Grantee）、权限类型（SELECT、INSERT、UPDATE、DELETE 等）以及授予的权限级别（例如，是授予了 SELECT 权限还是授予了 ALL 权限）
=> \dp Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+---------+-------+-----------------------+-----------------------+---------- public | t1 | table | user1=arwdDxt/user1 +| name: +| | | | =r/user1 +| user2=r/user1 +| | | | user2=arw/user1 | age: +| | | | | user2=w/user1 | (1 row)
· 通过系统库查看给定表的权限，包括指定表的所有者、授权用户、授予的权限类型和权限级别等信息
postgres=# select * from information_schema.table_privileges where table_name='t1'; grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy ----------+----------+---------------+--------------+------------+----------------+--------------+---------------- postgres | postgres | postgres | public | t1 | INSERT | YES | NO postgres | postgres | postgres | public | t1 | SELECT | YES | YES
· 通过pg_user表查看用户和权限
postgres=# select usename,usecreatedb,usesuper,userepl,usebypassrls,valuntil from pg_user; usename | usecreatedb | usesuper | userepl | usebypassrls | valuntil ----------+-------------+----------+---------+--------------+---------- postgres | t | t | t | t | user1 | f | f | f | f | user2 | t | f | f | f | (3 rows)
2、PostgreSQL授权
#授权user2用户拥有t1的DML权限 GRANT SELECT, UPDATE, INSERT,DELETE ON t1 TO user2; #授权user2对name和age列设置了不同权限 GRANT SELECT (name), UPDATE (age) ON t1 TO user2;
三、PostgreSQL权限插件pg_permissions
PostgreSQL的权限查看相比MySQL来说是不太好用的，权限的可视化不直观，不方便查询，而通过pg_permissions插件可以解决这些问题。pg_permissions插件可以查询用户的所有对象权限，包括库权限、表权限、视图权限、字段权限、函数权限等。
· 安装pg_permissions插件
插件地址：https://github.com/cybertec-postgresql/pg_permissions
git clone https://github.com/cybertec-postgresql/pg_permission.git cd pg_permission export PATH=/usr/local/pgsql/bin:$PATH USE_PGXS=1 make pg_config=/usr/local/pgsql/bin/pg_config USE_PGXS=1 make install #登录数据库创建pg_permissions扩展 postgres=# CREATE EXTENSION pg_permissions; CREATE EXTENSION
· pg_permissions插件安装好以后会创建8个视图，1张表，一个函数
这些视图可以检查当前授予的对象的权限，从名字可以看出来具体对应的权限维度。日常使用较多的就是all_permissions视图。需要注意的是超级用户不会显示在这些视图中，因为默认拥有所有权限。
#pg_permissions视图 postgres=# \dv *permissions List of relations Schema | Name | Type | Owner --------+----------------------+------+---------- public | all_permissions | view | postgres public | column_permissions | view | postgres public | database_permissions | view | postgres public | function_permissions | view | postgres public | schema_permissions | view | postgres public | sequence_permissions | view | postgres public | table_permissions | view | postgres public | view_permissions | view | postgres (8 rows) #pg_permissions表 postgres=# \dt *permission* List of relations Schema | Name | Type | Owner --------+-------------------+-------+---------- public | permission_target | table | postgres (1 row) #查询示例
四、PostgreSQL角色管理
角色是一系列权限的集合，使用角色便于对用户进行统一的权限管理。用户和角色在整个数据库实例中是全局的， 在同一个实例中的不同数据库中， 看到的用户都是相同的。在PostgreSQL中，角色和用户基本等同，唯一不同的是角色默认没有login的权限
#创建的用户和角色都可以直接登录数据库 CREATE USER user1; CREATE ROLE role1 LOGIN; #创建角色role1，并且具有创建数据库和用户\角色的权限 CREATE ROLE role1 login CREATEDB CREATEROLE; #将角色授权给用户 grant all on t1 to role1 GRANT role1 TO user1; GRANT role1 TO user2; #修改角色权限： ALTER ROLE role1 SUPERUSER; #删除角色 drop role role1; #授予预定义角色给用户 GRANT pg_read_all_data TO user1;
评论