PGSQL 创建用户设置访问策略

pg_hba.conf 的匹配规则是从上到下，第一条匹配的生效，但是：

关键规则pg_hba.conf 的匹配是看 四个字段的组合：

连接类型(type) + 数据库(database) + 用户(user) + 客户端地址(address)

四个字段都匹配才算命中，所以：

• 同IP + 不同用户 → 是不同的规则，互不覆盖
• 同IP + 同用户 + 不同数据库 → 也是不同的规则，不覆盖

 

"local" is for Unix domain socket connections only
host all all 10.xxx.4.1xx/32 scram-sha-256
host pms_prod readonly_user 192.xxx.xx.233/32 scram-sha-256
host pms_prod pms_user 10.xx.4.xxx/32 scram-sha-256
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# 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

改完配置后# 在 psql 里执行（不需要重启）SELECT pg_reload_conf();

postgres@Mbj-bpit-pms2 root]$ psql
could not change directory to "/root": Permission denied
psql (15.18)
Type "help" for help.
postgres=#CREATE USER readonly_user WITH PASSWORD '1qxxx';

-- 2. 切换到 pms_prod 数据库

postgres=# \c pms_prod

-- 3. 授权

You are now connected to database "pms_prod" as user "postgres".
pms_prod=# GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT
pms_prod=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
GRANT
pms_prod=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly_user;
GRANT
pms_prod=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;
ALTER DEFAULT PRIVILEGES
pms_prod=#

SELECT type, database, user_name, address, auth_method FROM pg_hba_file_rules;

yam连接信息

datasource:
url: jdbc:postgresql://10.xx.xx:5432/pms_prod?currentSchema=public&stringtype=unspecified
username: readonly_user
password: xxxxx