跳转到主要内容

文档 v3.7.0 版本已不再积极维护，你当前浏览的是一份归档快照。 最新文档请参阅 最新版本。

认证

Pigsty 中基于主机的身份验证

PostgreSQL 有各种 身份验证 方法。您可以使用所有这些方法，而 Pigsty 的开箱即用 ACL 系统专注于 HBA、密码和 SSL 身份验证。

客户端身份验证

要连接到 PostgreSQL 数据库，用户必须经过身份验证（默认使用密码）。

您可以在连接字符串中提供密码（不安全）或使用 PGPASSWORD 环境变量或 .pgpass 文件。查看 psql 文档和 PostgreSQL 连接字符串 获取更多详细信息。

psql 'host=<host> port=<port> dbname=<dbname> user=<username> password=<password>'
psql postgres://<username>:<password>@<host>:<port>/<dbname>
PGPASSWORD=<password>; psql -U <username> -h <host> -p <port> -d <dbname>

meta 数据库的默认连接字符串：

psql 'host=10.10.10.10 port=5432 dbname=meta user=dbuser_dba password=DBUser.DBA'
psql postgres://dbuser_dba:[email protected]:5432/meta
PGPASSWORD=DBUser.DBA; psql -U dbuser_dba -h 10.10.10.10 -p 5432 -d meta

要使用 SSL 证书连接，您可以使用 PGSSLCERT 和 PGSSLKEY 环境变量或 sslkey 和 sslcert 参数。

psql 'postgres://dbuser_dba:[email protected]:5432/meta?sslkey=/path/to/dbuser_dba.key&sslcert=/path/to/dbuser_dba.crt'

客户端证书（CN = 用户名）可以使用本地 CA 和 cert.yml 颁发。

定义 HBA

Pigsty 中有四个 HBA 规则参数：

它们是 HBA 规则对象的数组，每个 HBA 规则是以下形式之一：

1. 原始形式 

- title: allow intranet password access
  role: common
  rules:
    - host   all  all  10.0.0.0/8      md5
    - host   all  all  172.16.0.0/12   md5
    - host   all  all  192.168.0.0/16  md5

在这种形式中，title 将被渲染为注释行，然后是 rules 作为 HBA 字符串逐一显示。

当实例的 pg_role 与 role 相同时，HBA 规则被安装。

带有 role: common 的 HBA 规则将在所有实例上安装。

带有 role: offline 的 HBA 规则将在 pg_role = offline 或 pg_offline_query = true 的实例上安装。

2. 别名形式

别名形式，用 addr、auth、user 和 db 字段替换 rules。

- addr: 'intra'    # world|intra|infra|admin|local|localhost|cluster|<cidr>
  auth: 'pwd'      # trust|pwd|ssl|cert|deny|<official auth method>
  user: 'all'      # all|${dbsu}|${repl}|${admin}|${monitor}|<user>|<group>
  db: 'all'        # all|replication|....
  rules: []        # 原始 HBA 字符串优先于以上所有
  title: allow intranet password access

  • addr：哪里

    • world：所有 IP 地址
    • intra：所有内网 CIDR：'10.0.0.0/8'、'172.16.0.0/12'、'192.168.0.0/16'
    • infra：基础设施节点的 IP 地址
    • admin：admin_ip 地址
    • local：本地 unix 套接字
    • localhost：本地 unix 套接字 + tcp 127.0.0.1/32
    • cluster：PostgreSQL 集群成员的所有 IP 地址
    • <cidr>：任何标准 CIDR 块或 IP 地址

  • auth：如何

    • deny：拒绝访问
    • trust：信任身份验证
    • pwd：根据 pg_pwd_enc 使用 md5 或 scram-sha-256 密码认证
    • sha/scram-sha-256：强制 scram-sha-256 密码身份验证
    • md5：md5 密码身份验证
    • ssl：在 pwd 认证基础上强制主机 SSL
    • ssl-md5：在 md5 密码认证基础上强制主机 SSL
    • ssl-sha：在 scram-sha-256 密码认证基础上强制主机 SSL
    • os/ident：使用 ident 操作系统用户身份验证
    • peer：使用 peer 身份验证
    • cert：使用基于证书的客户端身份验证

  • user：谁

  • db：哪个

    • all：所有数据库
    • replication：复制数据库
    • 临时数据库名称

3. 在哪里定义

通常，全局 HBA 在 all.vars 中定义。如果您想修改全局默认 HBA 规则，可以从 full.yml 模板复制到 all.vars 进行修改。

集群特定的 HBA 规则在数据库的集群级配置中定义：

以下是集群 HBA 规则定义的一些示例。

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_hba_rules:
      - { user: dbuser_view ,db: all    ,addr: infra        ,auth: pwd  ,title: 'allow grafana dashboard access cmdb from infra nodes'}
      - { user: all         ,db: all    ,addr: 100.0.0.0/8  ,auth: pwd  ,title: 'all user access all db from kubernetes cluster' }
      - { user: '${admin}'  ,db: world  ,addr: 0.0.0.0/0    ,auth: cert ,title: 'all admin world access with client cert'        }

重载 HBA

要重载 postgres/pgbouncer HBA 规则：

bin/pgsql-hba <cls>                 # 重载集群 `<cls>` 的 HBA 规则
bin/pgsql-hba <cls> ip1 ip2...      # 重载特定实例的 HBA 规则

底层命令是：

./pgsql.yml -l <cls> -e pg_reload=true -t pg_hba,pg_reload
./pgsql.yml -l <cls> -e pg_reload=true -t pgbouncer_hba,pgbouncer_reload

默认 HBA

Pigsty 有一套默认的 HBA 规则，对大多数情况来说都相当安全。

这些规则以别名形式自解释。

pg_default_hba_rules:             # PostgreSQL 默认基于主机的身份验证规则
  - {user: '${dbsu}'    ,db: all         ,addr: local     ,auth: ident ,title: 'dbsu access via local os user ident'  }
  - {user: '${dbsu}'    ,db: replication ,addr: local     ,auth: ident ,title: 'dbsu replication from local os ident' }
  - {user: '${repl}'    ,db: replication ,addr: localhost ,auth: pwd   ,title: 'replicator replication from localhost'}
  - {user: '${repl}'    ,db: replication ,addr: intra     ,auth: pwd   ,title: 'replicator replication from intranet' }
  - {user: '${repl}'    ,db: postgres    ,addr: intra     ,auth: pwd   ,title: 'replicator postgres db from intranet' }
  - {user: '${monitor}' ,db: all         ,addr: localhost ,auth: pwd   ,title: 'monitor from localhost with password' }
  - {user: '${monitor}' ,db: all         ,addr: infra     ,auth: pwd   ,title: 'monitor from infra host with password'}
  - {user: '${admin}'   ,db: all         ,addr: infra     ,auth: ssl   ,title: 'admin @ infra nodes with pwd & ssl'   }
  - {user: '${admin}'   ,db: all         ,addr: world     ,auth: ssl   ,title: 'admin @ everywhere with ssl & pwd'   }
  - {user: '+dbrole_readonly',db: all    ,addr: localhost ,auth: pwd   ,title: 'pgbouncer read/write via local socket'}
  - {user: '+dbrole_readonly',db: all    ,addr: intra     ,auth: pwd   ,title: 'read/write biz user via password'     }
  - {user: '+dbrole_offline' ,db: all    ,addr: intra     ,auth: pwd   ,title: 'allow etl offline tasks from intranet'}
pgb_default_hba_rules:            # pgbouncer 默认基于主机的身份验证规则
  - {user: '${dbsu}'    ,db: pgbouncer   ,addr: local     ,auth: peer  ,title: 'dbsu local admin access with os ident'}
  - {user: 'all'        ,db: all         ,addr: localhost ,auth: pwd   ,title: 'allow all user local access with pwd' }
  - {user: '${monitor}' ,db: pgbouncer   ,addr: intra     ,auth: pwd   ,title: 'monitor access via intranet with pwd' }
  - {user: '${monitor}' ,db: all         ,addr: world     ,auth: deny  ,title: 'reject all other monitor access addr' }
  - {user: '${admin}'   ,db: all         ,addr: intra     ,auth: pwd   ,title: 'admin access via intranet with pwd'   }
  - {user: '${admin}'   ,db: all         ,addr: world     ,auth: deny  ,title: 'reject all other admin access addr'   }
  - {user: 'all'        ,db: all         ,addr: intra     ,auth: pwd   ,title: 'allow all user intra access with pwd' }

安全增强

对于那些关键情况，我们有一个 safe.yml 模板，以下 HBA 规则集作为参考：

pg_default_hba_rules:             # PostgreSQL 默认基于主机的认证规则
  - {user: '${dbsu}'    ,db: all         ,addr: local     ,auth: ident ,title: 'dbsu access via local os user ident'  }
  - {user: '${dbsu}'    ,db: replication ,addr: local     ,auth: ident ,title: 'dbsu replication from local os ident' }
  - {user: '${repl}'    ,db: replication ,addr: localhost ,auth: ssl   ,title: 'replicator replication from localhost'}
  - {user: '${repl}'    ,db: replication ,addr: intra     ,auth: ssl   ,title: 'replicator replication from intranet' }
  - {user: '${repl}'    ,db: postgres    ,addr: intra     ,auth: ssl   ,title: 'replicator postgres db from intranet' }
  - {user: '${monitor}' ,db: all         ,addr: localhost ,auth: pwd   ,title: 'monitor from localhost with password' }
  - {user: '${monitor}' ,db: all         ,addr: infra     ,auth: ssl   ,title: 'monitor from infra host with password'}
  - {user: '${admin}'   ,db: all         ,addr: infra     ,auth: ssl   ,title: 'admin @ infra nodes with pwd & ssl'   }
  - {user: '${admin}'   ,db: all         ,addr: world     ,auth: cert  ,title: 'admin @ everywhere with ssl & cert'   }
  - {user: '+dbrole_readonly',db: all    ,addr: localhost ,auth: ssl   ,title: 'pgbouncer read/write via local socket'}
  - {user: '+dbrole_readonly',db: all    ,addr: intra     ,auth: ssl   ,title: 'read/write biz user via password'     }
  - {user: '+dbrole_offline' ,db: all    ,addr: intra     ,auth: ssl   ,title: 'allow etl offline tasks from intranet'}
pgb_default_hba_rules:            # pgbouncer 基于主机的身份验证规则
  - {user: '${dbsu}'    ,db: pgbouncer   ,addr: local     ,auth: peer  ,title: 'dbsu local admin access with os ident'}
  - {user: 'all'        ,db: all         ,addr: localhost ,auth: pwd   ,title: 'allow all user local access with pwd' }
  - {user: '${monitor}' ,db: pgbouncer   ,addr: intra     ,auth: ssl   ,title: 'monitor access via intranet with pwd' }
  - {user: '${monitor}' ,db: all         ,addr: world     ,auth: deny  ,title: 'reject all other monitor access addr' }
  - {user: '${admin}'   ,db: all         ,addr: intra     ,auth: ssl   ,title: 'admin access via intranet with pwd'   }
  - {user: '${admin}'   ,db: all         ,addr: world     ,auth: deny  ,title: 'reject all other admin access addr'   }
  - {user: 'all'        ,db: all         ,addr: intra     ,auth: ssl   ,title: 'allow all user intra access with pwd' }