PostgreSQL（六）权限管理

【pg_hba.conf】

数据库及用户的授权是由文件 pg_hba.conf 控制的，格式如下：（/usr/local/pgsql/data/pg_hba.conf）

TYPE       DATABASE  USER  ADDRESS              auth-method  OPTIONS
---------  --------  ----  ----------- -------  -----------  -------------
local      database  user  auth-method                       [auth-options]
host       database  user  address              auth-method  [auth-options]
hostssl    database  user  address              auth-method  [auth-options]
hostnossl  database  user  address              auth-method  [auth-options]
host       database  user  IP-address  IP-mask  auth-method  [auth-options]
hostssl    database  user  IP-address  IP-mask  auth-method  [auth-options]
hostnossl  database  user  IP-address  IP-mask  auth-method  [auth-options]


说明：
local: 尝试使用本地 Unix 套接字连接。
host：尝试使用 TCP/IP 连接，包括SSL和非SSL（hostssl和hostnossl）。
hostssl：尝试使用 TCP/IP 连接，仅当连接使用 SSL 加密时。
hostnossl：尝试使用 TCP/IP 连接，仅当连接没有使用 SSL 加密时。与hostssl相反。

database：允许访问的数据库，多个数据库可用逗号隔开
	all ：表示可访问所有数据库
	sameuser ：与数据库同名的用户可访问。
	samerole ：与数据库同名的角色（成员用户）可访问。
	samegroup ：（不推荐使用）
	replication ：只允许复制连接请求

user ：指定可连接的数据库用户，多个用户可用逗号隔开
	all ：表示所有用户
	<username>：数据库用户
	<+username>：用户组（"+"符号表示组）
	<@filename>：引用记录用户名的外部文件

address : 允许访问的客户端地址，可以服务器名称、IP地址等。（写法如：IP/NETMASK）
	192.168.1.10/32	--示例
	0.0.0.0/0		--表示所有IPv4客户端
	::0/0			--表示所有IPv6客户端
	all				--表示所有客户端
	samehost 		--表示所有服务器对应的IP地址
	samenet 		--服务器能连接到的任意子网
	.example.com	--".xxx.xx" 匹配指定域名可连接
	
auth-method ：授权方法。常用以下两种
	trust ：信任
	reject ：拒绝

auth-options ：其他选项，格式为[name=value]。
	

修改完配置文件 pg_hba.conf 后，若使其生效，使用命令(pg_ctl reload)重新读取文件。在 Windows 系统中修改文件 pg_hba.conf 则立即生效，新的连接可以马上应用。
pg_ctl reload -D /usr/local/pgsql/data

	
# SQL 查看 pg_hba.conf 配置信息，在确认使用命令 pg_ctl reload 之前可以查看文件信息。
SELECT * FROM pg_hba_file_rules;

更多信息：https://www.postgresql.org/docs/10/static/auth-pg-hba-conf.html

【pg_ident.conf】

 

操作系统用户与数据库用户的对应关系表，一般要求同名。通常我们不以操作系统用户登录数据库，而是在数据库中创建独立的用户，不与操作系统关联。

1. 现在测试关联，先创建一个操作系统用户 pguserkk。
# adduser pguserkk
# passwd pguserkk

2. 用于权限的数据库用户创建一个数据库用户和角色 pguserkk
# create user pguserkk password '123456';

# 该用户此时可正常登录访问该数据库
# psql -d testdb -U pguserkk -W

3. 编辑文件添加映射关系：/usr/local/pgsql/data/pg_ident.conf
# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
maptest         pguserkk                pguserkk


4. 编辑授权文件：/usr/local/pgsql/data/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     ident  map=maptest

5. 重载配置文件
#pg_ctl reload -D /usr/local/pgsql/data

6. 登录访问
# su - pguserkk
# psql -d testdb


更多信息：https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

【user & role】

 

================================================================================
【user】
================================================================================
只有超级用户 superusers 和 拥有角色 CREATEROL 的用户可以创建新的用户。
使用操作系统命令 createuser 创建用户，需要操作系统账户环境

#su - postgres

这里使用操作系统命令 createuser 创建用户和同名角色。创建后在本地访问，或配置pg_hba.conf访问。
# createuser userkk01 --createrole --superuser --password
# psql -h 127.0.0.1 -p 5432 -d testdb -U userkk01 -W

创建另二个用户，指定存在的角色 userkk01 ，默认是继承(--inherit)角色权限的。()
# createuser userkk02 --role=userkk01 --password
# psql -h 127.0.0.1 -p 5432 -d testdb -U userkk02 -W

创建第三个用户，同时创建一个数据库，不指定或创建角色.(可创建删除数据库)
# createuser userkk03 --no-createrole --createdb --password
# psql -h 127.0.0.1 -p 5432 -d testdb -U userkk03 -W


删除用户和角色：
dropuser userkk03 -e
dropuser userkk02 -i
dropuser userkk01 -i -e

#查看用户或角色
\du
select usesysid,usename from pg_user;
select oid,rolname from pg_roles;
select * from pg_auth_members;

select b.rolname,c.usename
from pg_auth_members a
inner join pg_roles b on a.roleid=b.oid
inner join pg_user c on a.member=c.usesysid
order by b.rolname,c.usename;

操作系统命令 createuser 可以创建数据库用户，连接到数据库中，则可以用 "CREATE USER"创建用户。

CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:
      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid
	

createuser：
https://www.postgresql.org/docs/current/static/app-createuser.html
	
CREATE USER：
https://www.postgresql.org/docs/current/static/sql-createuser.html

================================================================================
【role】
================================================================================
角色的概念更容易理解了，各个数据库差不多。

CREATE ROLE ：https://www.postgresql.org/docs/current/static/sql-createrole.html
ALTER ROLE  ：https://www.postgresql.org/docs/current/static/sql-alterrole.html
DROP ROLE   ：https://www.postgresql.org/docs/current/static/sql-droprole.html
SET ROLE    ：https://www.postgresql.org/docs/current/static/sql-set-role.html
	

grant 、revoke 哪里语法都一样，都是国际标准，不多写了，学习中，先大概了解。

 

示例：

# 配置客户端监听、端口，重启服务
# vim /var/lib/pgsql/11/data/postgresql.conf
port = 5432
listen_addresses = '*'

# 配置用户连接权限
# vim /var/lib/pgsql/11/data/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             192.168.1.0/24          md5

# 管理员创建用户、数据库，授权
create user pguser password 'pguser';
create database testdb owner pguser;
grant all privileges on database testdb to pguser;

# 用户登录
# psql -h192.168.1.171 -p5432 -d testdb -U pguser -W