规划
gpmon 监控 (数据库gpperfom权限)
gwtest03 业务访问用户 (业务库增删改查权限)
gwtest02 超级管理员用户 (所有权限)
gwtest01 管理员用户 (所有库的增删改权限)
1、用户创建
创建用户步骤(使用gwtest02用户)：
postgres=# CREATE role gwtest03 with login;
CREATE ROLE
设置密码：
postgres=# alter user gwtest03 password '123456';
ALTER ROLE
赋予用户业务库的使用权限：
postgres=# GRANT all PRIVILEGES on database db1 to gwtest03;
GRANT
gwtest01 管理员用户 (所有库的增删改权限)
创建用户步骤(使用gwtest01用户)：
postgres=# CREATE role gwtest01 with login;
CREATE ROLE
设置密码：
postgres=# alter user gwtest01 password '123456';
ALTER ROLE
赋予用户业务库的使用权限：
GRANT all PRIVILEGES on database database_name1 to gwtest01;
GRANT all PRIVILEGES on database database_name2 to gwtest01;
GRANT all PRIVILEGES on database database_name3 to gwtest01;
2、检查连接权限
通常情况下，我们数据库只允许gwtest02用户连接，加了新用户后，要添加远程连接权限
vi $DATA_DIRECTORY/pg_hba.conf
host all gwtest02 0.0.0.0/0 md5
local all gwtest02 md5
如果没有加，登录会报如下错误：
[gwtest02@gwtest03209 ~]$ psql -h 127.0.0.1 -U gwtest
psql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "gwtest", database "postgres"
加上新建用户的连接权限：
host all gwtest 0.0.0.0/0 md5
local all gwtest md5
pg_ctl reload 生效
3、postgresql密码检查passwordcheck
安装模块：
源码包自带功能，只需要将包配置加入到lib目录里即可，如下
vi postgresql.conf
shared_preload_libraries = '$libdir/passwordcheck'
然后重启pg
pg_ctl restart
示例：
postgres=# CREATE role gwtest03 with login;
CREATE ROLE
postgres=# alter user gwtest03 password '123456';
ERROR: password is too short
postgres=# alter user gwtest03 password '1234567890';
ERROR: password must contain both letters and nonletters
postgres=# alter user gwtest03 password 'dasfasfgghasf';
ERROR: password must contain both letters and nonletters
postgres=# alter user gwtest03 password 'gwtest03#2022';
ERROR: password must not contain user name
postgres=# alter user gwtest03 password 'Root@123';
ALTER ROLE
4、密码尝试失败锁定
使用gwtest03 模拟登录五次失败
[postgres@hadoop01 ~]$ psql -Ugwtest03 -h127.0.0.1 -p123456 -d postgres
Password for user gwtest03:
psql: error: FATAL: password authentication failed for user "gwtest03"
[postgres@hadoop01 ~]$ psql -Ugwtest03 -h127.0.0.1 -p123456 -d postgres
Password for user gwtest03:
psql: error: FATAL: password authentication failed for user "gwtest03"
[postgres@hadoop01 ~]$ psql -Ugwtest03 -h127.0.0.1 -p123456 -d postgres
Password for user gwtest03:
psql: error: FATAL: password authentication failed for user "gwtest03"
[postgres@hadoop01 ~]$ psql -Ugwtest03 -h127.0.0.1 -p123456 -d postgres
Password for user gwtest03:
psql: error: FATAL: password authentication failed for user "gwtest03"
[postgres@hadoop01 ~]$ psql -Ugwtest03 -h127.0.0.1 -p123456 -d postgres
Password for user gwtest03:
用超级用户登录时会有提示：
[postgres@hadoop01 ~]$ psql -Upostgres -h127.0.0.1 -p18126 -d postgres
NOTICE: user: gwtest03!
NOTICE: failed_login_times: 5! failed_login: 5!
NOTICE: Account gwtest03 is locked!
psql (12.8)
Type "help" for help.
postgres=# select rolcanlogin from pg_catalog.pg_roles where rolname='gwtest03';
rolcanlogin
-------------
f
(1 row)
再次去登录发现已经被锁住：
[postgres@hadoop01 ~]$ psql -Ugwtest03 -h127.0.0.1 -p123456 -d postgres
Password for user gwtest03:
psql: error: FATAL: role "gwtest03" is not permitted to log in
解锁：
[postgres@hadoop01 ~]$ psql -Upostgres -h127.0.0.1 -p123456 -d postgres
psql (12.8)
Type "help" for help.
postgres=# alter user gwtest03 login;
ALTER ROLE
