In PostgreSQL, there
are a number of configuration files, some of the files needs to be managed by
postgres DBA, out of which pg_hba.conf is one of those. This
file mainly includes connection details, based on this it is decided who will
be able to connect to the database, we will discuss it in detail
The pg_hba.conf i.e.
host based authentication file is used to control client authentication and the
file stored in the data directory
- The pg_hba.conf file
consist of a set of records and contains one record per line, blanks lines
are ignored and text after # character are comments.
- Record is made up of a
number of fields that are separated by spaces and or tabs
- Each record specifies
connection type, a client IP address range, a database name, a username
and an authentication method to be used for connection matching these
parameters.
- It will be access denied if a
matching record is not found in pg_hba.conf file while
connecting.
A record in a file can
have one of the seven formats :
local database
user auth-method [auth-options]
host database
user address auth-method [auth-options]
hostssl database user
address auth-method [auth-options]
hostnossl database user address
auth-method [auth-options]
host database user
IP-Address IP-Mask auth-method [auth-options]
hostssl database user
IP-Address IP-Mask auth-method [auth-options]
hostnossl database user IP-Address
IP-Mask auth-method[auth-options]
The meaning of each
entity is described below
local
- This record matches
connection attempts using UNIX domain sockets, without a record of this
type UNIX domain socket connections are disallowed
host
- This record matches
connection attempts made using TCP/IP, host records match either SSL or
non_SSL connection attempts
hostssl
- This record matches connection attempt made using TCP/IP, but only when the connection is
made with an SSL encryption
hostnossl
- This behaves just opposite
to hostssl
Database
- Matches with database name
mentioned, options are described below
- all- matches all databases
- sameuser- matches if requested
database has same name requested user
- samerole- specifies requested user
must be member of role with same name as requested database
- replication- matches if a physical
replication connection is requested
- or one can give specific
database name, multiple database name can be specified by separating comma
- File name with database name
list can be specified in this field using @
User
- Specifies database user
matching record
- all- specifies it matches all
users or name of specific user or a group preceded by +
- File name with user name
list can be specified in this field using @
Address
- Specifies client machine
address(es) that matches this record
- This can contain either a
hostname, an IP address range, or one of the special keywords as described
below
- IP address range is
specified using standard numeric notation for the range starting
address, the slash (/), and a CIDR mask length
- This mask length indicates
the number of high-order bits of the client IP address that must match.
Bits to the right side should be zero in the given IP address, must not be
while spaces between IP address, the slash (/) and CIDR mask length
For example
IPV4 address
specified this way are
- 172.20.143.89/32 for a single
host or
- 172.20.143.0/24 for a small
network or
- 10.6.0.0/16 for larger one
IPV6-
- : : 1/128 for single
host
- Feo8:7a31:ciff:0000:0000/96
for small network
To specify single
host use mask length 32 for IPV4 and 128 for IPV6
all- can be used to
match all IP address
samehost- match any of the
servers own IP address
samenet- any address in any
subnet
IP-address, IP-Mask
- These can be an alternative
to the IP address/mask length notation. Instead of specifying
the mask length, the actual mask is specified in a separate column
- Example-
255.0.0.0 -> IPV4 mask length
8
255.255.255.255
-> IPV4 mask length 32
auth-method
- Specifies the authentication
method to use when connection matches this record
|
trust |
Allows connection unconditionally |
|
reject |
Reject connections
unconditionally |
|
scram-sha-256 |
Perform SCRAM-SHA-256 authentication |
|
md5 |
Perform
SCRAM-SHA-256 or MD5 authentication |
|
password |
Requires to supply password |
|
gss |
Uses GSSAPI to authenticate
user |
|
sspi |
Uses SSPI to authenticate user |
|
ident |
Use of ident server |
|
peer |
Local connection only |
|
ldap |
Authenticate using LDAP server |
|
radius |
Authenticate using RADIUS
server |
|
cert |
Authenticate using SSL client
certificate |
|
pam |
Pluggable Authentication Modules(PAM) |
|
bsd |
BSD authentication service
provided by OS |
auth-options
After the auth-method
field, there can be fields of the forma name=value that specify options for
authentication method
Below is example
of pg_hba.conf file
# PostgreSQL Client Authentication
Configuration File
#
===================================================
#
# Refer to the "Client
Authentication" section in the PostgreSQL
# documentation for a complete
description of this file. A short
# synopsis follows.
#
# This file controls: which hosts are
allowed to connect, how clients
# are authenticated, which PostgreSQL
user names they can use, which
# databases they can access.
Records take one of these forms:
#
# local
DATABASE USER METHOD [OPTIONS]
# host
DATABASE USER ADDRESS METHOD [OPTIONS]
# hostssl DATABASE
USER ADDRESS METHOD [OPTIONS]
# hostnossl DATABASE
USER ADDRESS METHOD [OPTIONS]
#
# (The uppercase items must be
replaced by actual values.)
#
# The first field is the connection
type: "local" is a Unix-domain
# socket, "host" is either
a plain or SSL-encrypted TCP/IP socket,
# "hostssl" is an
SSL-encrypted TCP/IP socket, and "hostnossl" is a
# plain TCP/IP socket.
#
# DATABASE can be "all",
"sameuser", "samerole", "replication", a
# database name, or a comma-separated
list thereof. The "all"
# keyword does not match
"replication". Access to replication
# must be enabled in a separate record
(see example below).
#
# USER can be "all", a user
name, a group name prefixed with "+", or a
# comma-separated list thereof.
In both the DATABASE and USER fields
# you can also write a file name
prefixed with "@" to include names
# from a separate file.
#
# ADDRESS specifies the set of hosts
the record matches. It can be a
#host name,or it is made up of an IP
address and a CIDR mask that is
# an integer (between 0 and 32 (IPv4)
or 128 (IPv6) inclusive) that
# specifies the number of significant
bits in the mask. A host name
# that starts with a dot (.) matches
a suffix of the actual host #name.
# Alternatively, you can write an IP
address and netmask in separate
# columns to specify the set of
hosts. Instead of a CIDR-address, #you
# can write "samehost" to
match any of the server's own IP #addresses,
# or "samenet" to match any
address in any subnet that the server is
# directly connected to.
#
# METHOD can be "trust",
"reject", "md5", "password",
"scram-sha-#256",
# "gss", "sspi",
"ident", "peer", "pam", "ldap",
"radius" or "cert".
# Note that "password"
sends passwords in clear text; "md5" or
# "scram-sha-256" are
preferred since they send encrypted passwords.
#
# OPTIONS are a set of options for
the authentication in the format
# NAME=VALUE. The available
options depend on the different
# authentication methods -- refer to
the "Client Authentication"
# section in the documentation for a
list of which options are
# available for which authentication
methods.
#
# Database and user names containing
spaces, commas, quotes and #other
# special characters must be
quoted. Quoting one of the keywords
# "all",
"sameuser", "samerole" or "replication" makes the
name lose
# its special character, and just
match a database or username with
# that name.
#
# This file is read on server startup
and when the server receives a
# SIGHUP signal. If you edit
the file on a running system, you have #to SIGHUP the server for the
changes to take effect, run "pg_ctl #reload", or execute "SELECT
pg_reload_conf()".
#
# Put your actual configuration here
# ----------------------------------
#
# If you want to allow non-local
connections, you need to add more
# "host" records. In
that case you will also need to make PostgreSQL
# listen on a non-local interface via
the listen_addresses
# configuration parameter, or via the
-i or -h command line switches.
# TYPE DATABASE
USER ADDRESS
METHOD
# IPv4 local connections:
host all
all
127.0.0.1/32 md5
# IPv6 local connections:
host all
all
::1/128 md5
# Allow replication connections from
localhost, by a user with the
# replication privilege.
host replication
all
127.0.0.1/32 md5
host replication
all ::1/128
md5
0 comments:
Post a Comment