# Auth / HBA > Host-Based Authentication in Pigsty --- PostgreSQL has various [authentication](https://www.postgresql.org/docs/current/client-authentication.html) methods. You can use all of them, while pigsty’s battery-included ACL system focuses on HBA, password, and SSL authentication. ------ ## Client Authentication To connect to a PostgreSQL database, the user has to be authenticated (with a password by default). You can provide the password in the connection string (not secure) or use the `PGPASSWORD` env or `.pgpass` file. Check [`psql`](https://www.postgresql.org/docs/current/app-psql.html#usage) docs and [PostgreSQL connection string](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING) for more details. ```bash psql 'host= port= dbname= user= password=' psql postgres://:@:/ PGPASSWORD=; psql -U -h -p -d ``` The default connection string for the `meta` database: ```bash psql 'host=10.10.10.10 port=5432 dbname=meta user=dbuser_dba password=DBUser.DBA' psql postgres://dbuser_dba:DBUser.DBA@10.10.10.10:5432/meta PGPASSWORD=DBUser.DBA; psql -U dbuser_dba -h 10.10.10.10 -p 5432 -d meta ``` To connect with the SSL certificate, you can use the `PGSSLCERT` and `PGSSLKEY` env or `sslkey` & `sslcert` parameters. ```bash psql 'postgres://dbuser_dba:DBUser.DBA@10.10.10.10:5432/meta?sslkey=/path/to/dbuser_dba.key&sslcert=/path/to/dbuser_dba.crt' ``` While the client certificate (`CN` = username) can be issued with local CA & [cert.yml](https://github.com/pgsty/pigsty/blob/v3.7.0/cert.yml). ------ ## Define HBA There are four parameters for HBA Rules in Pigsty: - [`pg_hba_rules`](/docs/pgsql/param#pg_hba_rules): postgres ad-hoc hba rules - [`pg_default_hba_rules`](/docs/pgsql/param#pg_default_hba_rules): postgres default hba rules - [`pgb_hba_rules`](/docs/pgsql/param#pgb_hba_rules): pgbouncer ad-hoc hba rules - [`pgb_default_hba_rules`](/docs/pgsql/param#pgb_default_hba_rules): pgbouncer default hba rules Which are array of hba rule objects, and each hba rule is one of the following forms: ### 1. Raw Form ```yaml - 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 ``` In the form, the `title` will be rendered as a comment line, followed by the `rules` as hba string one by one. An HBA Rule is installed when the instance’s [`pg_role`](/docs/pgsql/param#pg_role) is the same as the `role`. HBA Rule with `role: common` will be installed on all instances. HBA Rule with `role: offline` will be installed on instances with [`pg_role`](/docs/pgsql/param#pg_role) = `offline` or [`pg_offline_query`](/docs/pgsql/param#pg_offline_query) = `true`. ### 2. Alias Form The alias form, which replace `rules` with `addr`, `auth`, `user`, and `db` fields. ```yaml - addr: 'intra' # world|intra|infra|admin|local|localhost|cluster| auth: 'pwd' # trust|pwd|ssl|cert|deny| user: 'all' # all|${dbsu}|${repl}|${admin}|${monitor}|| db: 'all' # all|replication|.... rules: [] # raw hba string precedence over above all title: allow intranet password access ``` - `addr`: where - `world`: all IP addresses - `intra`: all intranet cidr: `'10.0.0.0/8', '172.16.0.0/12', '192.168.0.0/16'` - `infra`: IP addresses of infra nodes - `admin`: `admin_ip` address - `local`: local unix socket - `localhost`: local unix socket + tcp 127.0.0.1/32 - `cluster`: all IP addresses of pg cluster members - ``: any standard CIDR blocks or IP addresses - `auth`: how - `deny`: reject access - `trust`: trust authentication - `pwd`: use `md5` or `scram-sha-256` password auth according to [`pg_pwd_enc`](/docs/pgsql/param#pg_pwd_enc) - `sha`/`scram-sha-256`: enforce `scram-sha-256` password authentication - `md5`: `md5` password authentication - `ssl`: enforce host ssl in addition to `pwd` auth - `ssl-md5`: enforce host ssl in addition to `md5` password auth - `ssl-sha`: enforce host ssl in addition to `scram-sha-256` password auth - `os`/`ident`: use `ident` os user authentication - `peer`: use `peer` authentication - `cert`: use certificate-based client authentication - `user`: who - `all`: all users - `${dbsu}`: database superuser specified by [`pg_dbsu`](/docs/pgsql/param#pg_dbsu) - `${repl}`: replication user specified by [`pg_replication_username`](/docs/pgsql/param#pg_replication_username) - `${admin}`: admin user specified by [`pg_admin_username`](/docs/pgsql/param#pg_admin_username) - `${monitor}`: monitor user specified by [`pg_monitor_username`](/docs/pgsql/param#pg_monitor_username) - ad hoc users & roles. - `db`: which - `all`: all databases - `replication`: replication database - ad hoc database name ### 3. Where to Define Typically, global HBA is defined in all.vars. If you want to modify the global default HBA rules, you can copy from the full.yml template to all.vars for modification. - [`pg_default_hba_rules`](/docs/pgsql/param#pg_default_hba_rules): postgres global default HBA rules - [`pgb_default_hba_rules`](/docs/pgsql/param#pgb_default_hba_rules): pgbouncer global default HBA rules Cluster-specific HBA rules are defined in the cluster-level configuration of the database: - [`pg_hba_rules`](/docs/pgsql/param#pg_hba_rules): postgres HBA rules for the cluster - [`pgb_hba_rules`](/docs/pgsql/param#pgb_hba_rules): pgbouncer HBA rules for the cluster Here are some examples of cluster HBA rule definitions. ```yaml 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' } ``` ------ ## Reload HBA To reload postgres/pgbouncer hba rules: ```bash bin/pgsql-hba # reload hba rules of cluster `` bin/pgsql-hba ip1 ip2... # reload hba rules of specific instances ``` The underlying command: are: ```bash ./pgsql.yml -l -e pg_reload=true -t pg_hba,pg_reload ./pgsql.yml -l -e pg_reload=true -t pgbouncer_hba,pgbouncer_reload ``` ------ ## Default HBA Pigsty has a default set of HBA rules, which is pretty secure for most cases. The rules are self-explained in alias form. ```yaml pg_default_hba_rules: # postgres default host-based authentication rules - {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 default host-based authentication rules - {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' } ``` ------ ## Security Enhancement For those critical cases, we have a [safe.yml](https://github.com/pgsty/pigsty/blob/v3.7.0/conf/ha/safe.yml) template with the following hba rule set as a reference: ```yaml pg_default_hba_rules: # postgres host-based auth rules by default - {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 host-based authentication rules - {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' } ```