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