用户角色管理

​专栏内容：

postgresql使用入门基础手写数据库toadb并发编程

个人主页：我的主页

管理社区：开源数据库

座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.

文章目录

用户角色管理概述 信息查看 查看用户信息查看角色信息

创建删除 创建角色删除角色创建和删除用户

案例 场景分析管理员用户创建管理员用户创建数据库创建业务表创建角色创建用户

数据导入业务数据分析业务

总结 结尾

概述

在数据库中有一套严格的访问控制策略，它是基于数据库用户，也就是说一个数据库对象，如database，可以给不同用户没的访问权限，如用户user1，有只读访问权限，用户user2有创建表的权限等等，通过这一套访问权限来保证数据的安全。

在postgresql中，访问数据的前提是以用户的形式登陆数据库，不同的用户被赋予不同的权限。拥有相同权限的一组用户，可以被标记为同一种角色，这与现实社会是类似的，如保管员角色，工人角色，它们访问生产资料的权限和使用方式也是不同的。

本节就来分享一下postgresql中用户(user)，角色(role)的用法，以及它们的特点。

信息查看

在postgresql数据库中，如何查看已经存在的用户和角色呢，以及不同用户和角色有什么样的权限，下面我们分别来看一下。

查看用户信息

用户信息存储在pg_user这张系统视图中，用户都可以查看。

<code>postgres=> select * from pg_user;

usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig

----------+----------+-------------+----------+---------+--------------+----------+----------+------------------------code>

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此处只显示为*，并不是实际的密码。

查看角色信息

<code>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

--------+-----------------+----------+-------code>

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)

每张表中插入了一条数据。

数据分析业务