用户角色管理
专栏内容:
- postgresql使用入门基础
- 手写数据库toadb
- 并发编程
个人主页:我的主页
管理社区:开源数据库
座右铭:天行健,君子以自强不息;地势坤,君子以厚德载物.
文章目录
- 用户角色管理
- 概述
- 信息查看
- 查看用户信息
- 查看角色信息
- 创建删除
- 创建角色
- 删除角色
- 创建和删除用户
- 案例
- 场景分析
- 管理员用户
- 创建管理员用户
- 创建数据库
- 创建业务表
- 创建角色
- 创建用户
- 数据导入业务
- 数据分析业务
- 总结
- 结尾
概述
在数据库中有一套严格的访问控制策略,它是基于数据库用户,也就是说一个数据库对象,如database,可以给不同用户没的访问权限,如用户user1,有只读访问权限,用户user2有创建表的权限等等,通过这一套访问权限来保证数据的安全。
在postgresql中,访问数据的前提是以用户的形式登陆数据库,不同的用户被赋予不同的权限。拥有相同权限的一组用户,可以被标记为同一种角色,这与现实社会是类似的,如保管员角色,工人角色,它们访问生产资料的权限和使用方式也是不同的。
本节就来分享一下postgresql中用户(user),角色(role)的用法,以及它们的特点。
信息查看
在postgresql数据库中,如何查看已经存在的用户和角色呢,以及不同用户和角色有什么样的权限,下面我们分别来看一下。
查看用户信息
用户信息存储在pg_user这张系统视图中,用户都可以查看。
postgres=> select * from pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+------------------------
postgres | 10 | t | t | t | t | ******** | |
senllang | 16682 | f | f | f | f | ******** | | {search_path=senlleng}
(2 rows)
可以看到有两个用户,一个是当前登陆的senllang用户,另一个是postgres是数据库的管理员账户,是在集簇创建时创建的,是默认的超级管理员;它们在数据库内部使用的是usersysid来标识。
可以看到用户级信息有:
- usecreatedb, 创建database权限,该用户是否可以创建database;
- usesuper,超级用户权限,该用户是否有超级用户权限;
- userepl,流复制的权限;
- usebypassrls,是否对行级安全策略旁路的权限;
- valuntil,密码过期时间;
- useconfig,其它运行时配置 ;
当拥有上述权限时,显示为 t
, 否则显示为f
, passwd此处只显示为*,并不是实际的密码。
查看角色信息
在postgresql数据库创建后,系统默认创建了很多角色,可以通过pg_roles这个系统视图来查看。
postgres=> select * from pg_roles;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+------------------------+-------
postgres | t | t | t | t | t | t | -1 | ******** | | t | | 10
pg_database_owner | f | t | f | f | f | f | -1 | ******** | | f | | 6171
pg_read_all_data | f | t | f | f | f | f | -1 | ******** | | f | | 6181
pg_write_all_data | f | t | f | f | f | f | -1 | ******** | | f | | 6182
pg_monitor | f | t | f | f | f | f | -1 | ******** | | f | | 3373
pg_read_all_settings | f | t | f | f | f | f | -1 | ******** | | f | | 3374
pg_read_all_stats | f | t | f | f | f | f | -1 | ******** | | f | | 3375
pg_stat_scan_tables | f | t | f | f | f | f | -1 | ******** | | f | | 3377
pg_read_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4569
pg_write_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4570
pg_execute_server_program | f | t | f | f | f | f | -1 | ******** | | f | | 4571
pg_signal_backend | f | t | f | f | f | f | -1 | ******** | | f | | 4200
pg_checkpoint | f | t | f | f | f | f | -1 | ******** | | f | | 4544
pg_use_reserved_connections | f | t | f | f | f | f | -1 | ******** | | f | | 4550
pg_create_subscription | f | t | f | f | f | f | -1 | ******** | | f | | 6304
senllang | f | t | f | f | t | f | -1 | ******** | | f | {search_path=senlleng} | 16682
(16 rows)
可以看到数据库中已经默认创建了很多角色,特别是刚才的看到的两个用户也列在其中,这是为什么呢?
在postgresql 中用户和角色在内部实现是一样的,只是在创建时,用户默认有登陆权限,而角色默认没有。
这是postgresql 实现时的一样简化处理,实际使用中,我们还是按SQL标准中对它们的定义来使用,即用户用来实际登陆数据,而角色是权限的集合,用来赋予用户。
角色是权限的集合,那么它又会延伸出几项权限:
- rolinherit,权限是否可以由成员角色继承;
- rolcreaterole,创建更多角色的权限;
- rolcanlogin,是否可以登陆;如果可以登陆时,就和用户是一样了,建议不要混合使用;
- rolconnlimit,最大连接数的限制;-1 表示没有限制,此时由配置文件中的max_connections控制;
创建删除
在数据库设计时,对业务表设计完成后,需要规划有那几类访问的角色,确定维护和管理用户。
特别要注意,实际使用中千万不能用默认的超级管理员用户postgres,它拥有所有权限。
下面我们来看一下创建和删除用户的SQL语法。
创建角色
创建角色的SQL语法如下:
CREATE ROLE role_name WITH option;
这里的option可以有以下单一或者组合:
- SUPERUSER
- CREATEDB
- CREATEROLE
- INHERIT
- LOGIN
- REPLICATION
- BYPASSRLS
- CONNECTION LIMIT connlimit
- PASSWORD ‘password’
- VALID UNTIL ‘timestamp’
- IN ROLE role_name [, …], 新创建的角色成为 role_name的成员,这里可以写多个;
- ROLE role_name [, …], 同上面相反,将指定的已存在的role_name加入新建角色的组中,也就是新建了一个组;
- ADMIN role_name [, …],将role_name加入新建角色的组中,同时将role_name设置为分组中的管理角色,它可以将分组授于其它角色;
角色的权限除了在创建时指定的权限外,数据库对象的操作权限可以使用grant
赋予对应的角色。
角色使用中关于权限的继承,在postgresql16之中grant语句也增加了WITH INHERIT
对承继的控制,所以优先是grant子句,如果没有指定时使用角色的inherit控制;在postgresql16版本以前,只能通过角色的inherit来控制,但是当中间有不需要继承的处理时,就非常麻烦。
删除角色
删除角色的SQL语法如下:
DROP ROLE [IF EXISTS] role_name;
当然,这是一个非常危险的操作,尤其该角色已经被赋予了其它角色,或者已经创建了数据库对象时,此时先要转移所有者,再删除。
创建和删除用户
用户的创建与删除,与角色是一样的SQL语法,只是用户创建后默认会带有登陆权限,所以这里不再赘述。
案例
下面我们通过一个案例,来使用角色和用户保障数据库的用户数据安全。
场景分析
在大数据分析业务应用中,一般会分为三种角色:
- dba管理员
创建其它角色,将权限分配到不同角色中;
根据业务数据创建不同的数据库以及数据表,分配权限;
同时根据业务接入,创建和删除用户,以角色形式分配权限;
- 数据导入角色
将收集到的数据源源不断的导入指定数据的表中;
此类角色只有对应表的查询,插入,更新权限,不能对表drop 和 数据的delete操作;
- 数据分析角色
数据分析业务接入时的用户拥有的权限;
它们只有数据表的select权限,没有其它任务权限
管理员用户
系统中只设定一个管理员用户,所以以user的形式直接创建。
管理员用户主要做以下几件事情:
- 创建database;
- 创建数据表;
- 创建角色,以及赋予它们权限;
- 创建业务应用使用的user用户;
创建管理员用户
先要切换到超级管理员用户postgres,由它来创建其它角色,因为当前用户不具有创建角色的权限;
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=#
通过\c database user
这个命令进行切换登陆的数据库和用户;
之后可以看到命令行提示符变为=#
,这表示在超级管理员用户下了。
postgres=# CREATE USER dba WITH PASSWORD '123456' CREATEDB CREATEROLE;
CREATE ROLE
这里可以看到,用户创建成功,具有createdb
和createrole
权限。
下面由此dba用户开始工作。
创建数据库
以dba用户登陆postgres数据库,再创建自己的数据为mydb;
postgres=# \c postgres dba
You are now connected to database "postgres" as user "dba".
postgres=> create database mydb;
CREATE DATABASE
注意,此时还在postgres数据库中,还没有权限操作,需要切换到自己的数据库mydb中,才能行使dba权限。
创建业务表
下面在mydb中创建两张业务表。
需要先用dba用户登陆到mydb数据库中。
postgres=> \c mydb dba
You are now connected to database "mydb" as user "dba".
mydb=>
可以看到提示符已经变更为mydb=>
,说明当前已经在mydb数据中了。
mydb=> CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
price NUMERIC
);
CREATE TABLE
mydb=> CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100)
);
CREATE TABLE
mydb=>
创建了两张业务表来模拟数据分析业务。
创建角色
下面创建业务应用对应的角色,一是数据导入角色;另一个是数据分析的只读角色;
mydb=> CREATE ROLE data_maintainer_role ;
CREATE ROLE
mydb=> CREATE ROLE read_only_role ;
CREATE ROLE
给角色分配数据对象的权限,角色其实是权限的集合;
mydb=> GRANT SELECT, INSERT, UPDATE ON TABLE users, products TO data_maintainer_role;
GRANT
mydb=> GRANT SELECT ON TABLE users, products TO read_only_role;
GRANT
- 数据导入data_maintainer_role,拥有两张表的select,update,insert权限,但不能delete数据,一般也不会直接delete,只是在数据上打个删除标记。
- 数据分析read_only_role,只有select权限,它只有读数据分析即可。
- 当有新的业务表时,将权限加到角色上即可,对应的用户也会自动具有权限,节省了大量工作;
创建用户
权限架构分配结束后,准备应用的接入了。
给不同的应用准备登陆用户,先准备两个用户,当然也可以根据应用的多少,准备多个用户。
mydb=> CREATE USER data_maintainer WITH PASSWORD '123456';
CREATE ROLE
mydb=> CREATE USER analyst WITH PASSWORD '123456';
CREATE ROLE
给用户赋予对应的角色,也就是权限的集合,类似于给一张门禁卡,那些门可以打开,那些不能打开。
mydb=> GRANT data_maintainer_role TO data_maintainer;
GRANT ROLE
mydb=> GRANT read_only_role TO analyst;
GRANT ROLE
当然后续有更多应用的用户创建时,也是根据类型赋予不同角色的权限集就可以。
数据导入业务
数据导入业务以data_maintainer登陆,可以数据导入动作。
mydb=> \c mydb data_maintainer
You are now connected to database "mydb" as user "data_maintainer".
mydb=> \d
List of relations
Schema | Name | Type | Owner
--------+-----------------+----------+-------
public | products | table | dba
public | products_id_seq | sequence | dba
public | users | table | dba
public | users_id_seq | sequence | dba
(4 rows)
mydb=> select * from products;
id | name | price
----+------+-------
(0 rows)
mydb=> insert into products values(1,'pen',10);
INSERT 0 1
mydb=> insert into users values(1, 'senllang','study@senllang.onaliyun.com');
INSERT 0 1
mydb=> select * from products;
id | name | price
----+------+-------
1 | pen | 10
(1 row)
mydb=> select * from users;
id | username | email
----+----------+-----------------------------
1 | senllang | study@senllang.onaliyun.com
(1 row)
每张表中插入了一条数据。
数据分析业务
数据分析业务以analyst用户登陆,只能有数据查看的权限。
mydb=> \c mydb analyst
You are now connected to database "mydb" as user "analyst".
mydb=> select * from products;
id | name | price
----+------+-------
1 | pen | 10
(1 row)
mydb=> insert into products values(2,'rule',10);
ERROR: permission denied for table products
对数据的修改不会被拒绝,保障数据的安全。
总结
postgresql 数据库中通过角色和用户来维护权限集的分配,以及权限的传递继承,使得DBA可以轻松的维护权限赋予,变更。
结尾
非常感谢大家的支持,在浏览的同时别忘了留下您宝贵的评论,如果觉得值得鼓励,请点赞,收藏,我会更加努力!
作者邮箱:study@senllang.onaliyun.com
如有错误或者疏漏欢迎指出,互相学习。
注:未经同意,不得转载!