PostgreSQL 用户权限与安全管理

1 系统默认角色

postgres=# select rolname from pg_roles;
           rolname           
-----------------------------
 postgres
 pg_database_owner
 pg_read_all_data
 pg_write_all_data
 pg_monitor
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
 pg_read_server_files
 pg_write_server_files
 pg_execute_server_program
 pg_signal_backend
 pg_checkpoint
 pg_maintain
 pg_use_reserved_connections
 pg_create_subscription
(16 rows)

2 用户(角色)管理

#创建用户

postgres=# create user zyb with password 'zyb123';
 

#查看用户

postgres=# \du
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 zyb       | 

#删除用户

postgres=# drop user zyb;

#授权

添加table授权（SELECT,INSERT,UPDATE,DELETE）

 grant SELECT,INSERT,UPDATE,DELETE on test to zyb;

删除table授权

revoke SELECT,INSERT,UPDATE,DELETE on test from zyb;

添加database授权(CREATE,CONNECT,EMPORARY,TEMP )

GRANT CREATE,CONNECT,EMPORARY,TEMP ON DATABASE mydb TO zyb;

删除

revoke CREATE,CONNECT,EMPORARY,TEMP ON DATABASE mydb from zyb

更多帮助信息查看命令: mydb=# \h grant 

3 pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust      

#本地登录不需要密码，如psql -U postgres
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust

#本地登录不需要密码，如psql -h 127.0.0.1 -U postgres
# IPv6 local connections:
host    all             all             ::1/128                 trust

# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

#TYPE  

local   本地

host   远程

#ADDRESS 

192.168.254.110   单个ip

192.168.254.0/24  ip段

0.0.0.0/0                所有ip都可以登录

#METHOD 常用加密方法

md5，scram-sha-256