PostgreSQL登陆配置文件pg_hba.conf解析

译者:anxin
日期:2017-09-19

PostgreSQL安装后,通过登陆配置文件pg_hba.conf文件配置客户端如何连接登陆PostgreSQL数据库。

Debian系统pg_hba.conf文件的位置是:/etc/postgresql/*/main/pg_hba.conf*表示PostgreSQL版本号)。

CentOS系统pg_hba.conf文件的位置是:/var/lib/pgsql/data/pg_hba.conf(CentOS自带PostgreSQL),/var/lib/pgsql/*/data/pg_hba.conf其中*为版本号,官方PostgreSQL安装包)

CentOS的pg_hba.conf的默认配置如下:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident

Debian的pg_hba.conf的默认配置如下:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

pg_hba.conf基本的配置形式如下:

#类型      数据库    用户  IP地址   认证方式
local      database  user           auth-method
host       database  user  address  auth-method
hostssl    database  user  address  auth-method
hostnossl  database  user  address  auth-method

pg_hba.conf配置信息的详细解释

连接类型

配置客户端连接PostgreSQL数据库的类型

  • local配置试图使用Unix域套接字(Unix-domain socket)访问PostgreSQL的连接。

    如果没有此记录,禁止使用Unix域套接字访问PostgreSQL数据库,PostgreSQL的套接字文件为/run/postgresql/.s.PGSQL.nnnn,其中nnnn为PostgreSQL服务器端口号,默认为5432。在Shell中运行PostgreSQL命令工具时,匹配的此记录。

  • host配置试图使用TCP/IP访问PostgreSQL的连接,它匹配SSL和non-SSL连接。

    Python,PHP等语言应用程序连接PostgreSQL数据库时配置此记录。

  • hostssl配置试图使用TCP/IP访问PostgreSQL的连接,它仅匹配SSL连接。
  • hostnossl配置试图使用TCP/IP访问PostgreSQL的连接,它仅匹配没有使用SSL连接。

数据库

设置允许访问的PostgreSQL数据库名称。

  • 指定所有数据库:all
  • 指定多个数据库:db1,db2,数据库名称之间用,分开
  • 指定文件中定义的数据库:@dbnames,读取$PGDATA/dbnames文件中配置的所有数据库名称,Debian系统$PGDATA指向的目录是:/var/lib/postgresql/*/main(其中*为版本号);CentOS系统$PGDATA指向的目录是:/var/lib/pgsql/data(CentOS自带PostgreSQL),/var/lib/pgsql/*/data其中*为版本号,官方PostgreSQL安装包)

用户

设置允许访问的PostgreSQL的用户名。

  • 指定所有用户:all
  • 指定多个用户:user1,user2,用户名之间用,分开
  • 指定用户组:+support,允许具有support角色(组)的所有用户访问
  • 指定文件中定义的用户:@usernames,读取$PGDATA/usernames文件中配置的所有用户名,Debian系统$PGDATA指向的目录是:/var/lib/postgresql/*/main(其中*为版本号);CentOS系统$PGDATA指向的目录是:/var/lib/pgsql/data(CentOS自带PostgreSQL),/var/lib/pgsql/*/data其中*为版本号,官方PostgreSQL安装包)

IP地址

配置允许来自那些IP地址的客户端访问PostgreSQL数据库

  • 指定一个本地IPv4地址:127.0.0.1/32(这里使用的是CIDR地址形式)
  • 指定一组IPv4地址:196.168.1.0/24/24表示前面24位不变地址,后面8为可变地址,相当于子网掩码255.255.255.0,(/*表示,前面*为不变地址,后面32-*为可变地址)。
  • 指定一个本地IPv6地址:::1/128
  • 指定一组IPv6地址:fe80::7a31:c1ff:0000:0000/96/96表示前面96位不变地址,后面32为可变地址,(/*表示,前面*为不变地址,后面128-*为可变地址)。
  • 指定一个域名:example.com
  • 指定域名下的所有子域名:.example.com

认证方式

设置连接数据库时使用的验证用户的方式,详细查看:PostgreSQL认证方式

pg_hba.conf配置实例

# Allow any user on the local system to connect to any database with
# any database user name using Unix-domain sockets (the default for local
# connections).
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust

# The same using local loopback TCP/IP connections.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            trust

# The same as the previous line, but using a separate netmask column
#
# TYPE  DATABASE        USER            IP-ADDRESS      IP-MASK             METHOD
host    all             all             127.0.0.1       255.255.255.255     trust

# The same over IPv6.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             ::1/128                 trust

# The same using a host name (would typically cover both IPv4 and IPv6).
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             localhost               trust

# Allow any user from any host with IP address 192.168.93.x to connect
# to database "postgres" as the same user name that ident reports for
# the connection (typically the operating system user name).
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    postgres        all             192.168.93.0/24         ident

# Allow any user from host 192.168.12.10 to connect to database
# "postgres" if the user's password is correctly supplied.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    postgres        all             192.168.12.10/32        md5

# Allow any user from hosts in the example.com domain to connect to
# any database if the user's password is correctly supplied.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             .example.com            md5

# In the absence of preceding "host" lines, these two lines will
# reject all connections from 192.168.54.1 (since that entry will be
# matched first), but allow Kerberos 5 connections from anywhere else
# on the Internet.  The zero mask causes no bits of the host IP
# address to be considered, so it matches any host.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             192.168.54.1/32         reject
host    all             all             0.0.0.0/0               krb5

# Allow users from 192.168.x.x hosts to connect to any database, if
# they pass the ident check.  If, for example, ident says the user is
# "bryanh" and he requests to connect as PostgreSQL user "guest1", the
# connection is allowed if there is an entry in pg_ident.conf for map
# "omicron" that says "bryanh" is allowed to connect as "guest1".
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             192.168.0.0/16          ident map=omicron

# If these are the only three lines for local connections, they will
# allow local users to connect only to their own databases (databases
# with the same name as their database user name) except for administrators
# and members of role "support", who can connect to all databases.  The file
# $PGDATA/admins contains a list of names of administrators.  Passwords
# are required in all cases.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   sameuser        all                                     md5
local   all             @admins                                 md5
local   all             +support                                md5

# The last two lines above can be combined into a single line:
local   all             @admins,+support                        md5

# The database column can also use lists and file names:
local   db1,db2,@demodbs  all                                   md5
本文链接:/tutorial/postgresql/postgresql-peizhi-pg-hba

本文版权归知站所有,未经站长同意不得转载,谢谢尊重作者劳动成果!