We mentioned earlier in this section that the
pg_hba.conf
file enables client authentication between the PostgreSQL server and the client application. This file consists of a series of
entries
,
which define a host and its associated permissions (e.g., the database it is allowed to connect to, the authentication method
to use, and so on).
When an application requests a connection, the request will specify a PostgreSQL username and database with which it
intends to connect to PostgreSQL. Optionally, a password may be provided, depending on the expected configuration for the
connecting host.
Note: PostgreSQL has its own user and password tables, which are separate from system accounts. It is not
required that your PostgreSQL users match users available to the operating system.
When PostgreSQL receives a connection request it will check the
pg_hba.conf
file to verify
that the machine from which the application is requesting a connection has rights to connect to the specified database. If
the machine requesting access has permission to connect, PostgreSQL will check the conditions that the application must
meet in order to successfully authenticate. This affects connections that are initiated locally as well as remotely.
PostgreSQL will check the authentication method via the
pg_hba.conf
for every connection
request. This check is performed every time a new connection is requested from the PostgreSQL server, so there is no need
to re-start PostgreSQL after you add, modify or remove an entry in the
pg_hba.conf
file. Example 8-1 is a simple example of the
pg_hba.conf
file.
Example 8-1. A simple pg_hba.conf file
# PostgreSQL HOST ACCESS CONTROL FILE
#
local all trust
host all 127.0.0.1 255.255.255.255 trust
host booktown 192.168.1.3 255.255.255.255 ident sales
host all 192.168.1.4 255.255.255.255 ident audit
When a connection is initialized, PostgreSQL will read through the
pg_hba.conf
one entry at a
time, from the top down. As soon a matching record is found, PostgreSQL will stop searching and allow or reject the
connection, based on the found entry. If PostgreSQL does not find a matching entry in the
pg_hba.conf
file, the connection fails completely.
Table-level permissions still apply to a database, even if a user has permissions to connect to the database. If
you can connect, but cannot select data from a table, you may want to verify that your connected user has permission to use
SELECT on that table. Using the
psql
command-line application, you
can check the permissions of the tables within a database by using the \z slash command.
From any other interface to PostgreSQL, use the query demonstrated in Example 8-2 to
see the same information provided by the \z slash command.
Example 8-2. Checking user permissions
testdb=#
SELECT relname as "Relation", relacl as "Access permissions"
testdb-#
FROM pg_class
testdb-#
WHERE relkind IN ('r', 'v', 'S')
testdb-#
AND relname !~ '^pg_'
testdb-#
ORDER BY relname;
Relation | Access permissions
----------+----------------------------------
foo | {"=arwR","jdrake=arwR"}
my_list | {"=","jdrake=arwR","jworsley=r"}
(2 rows)
The
pg_hba.conf
file contains sequential entries that define the settings PostgreSQL should
use during the client authentication process for a specified host. This file is designed to be easily customizable to
your system needs.
Within this file, you may associate a TCP/IP host address (or a range of addresses) with a particular database (or
all
databases), and one of several available authentication methods. You may also specify access for
local connections using the term localhost, or
127.0.0.1, rather than using the system's external IP address. Several syntax rules
apply to the
pg_hba.conf
.
First, you may only place one host record per line in the file. Subsequently, host records are not allowed to wrap
across multiple lines. Second, each host record must contain multiple fields, which must be separated by either tabs or
spaces. The number of fields in a host record is directly related to the type of host entry being defined. Example 8-3 shows two host records, the first with the fields separated by spaces, and the
second with the file separated by tabs.
Example 8-3. A valid pg_hba.conf entry with spaces and tabs
host all 127.0.0.1 255.255.255.255 trust
host all 127.0.0.1 255.255.255.255 trust
Commenting is allowed within the
pg_hba.conf
by placing a hash mark
(#) at the beginning of each line being commented. Example 8-4
demonstrates valid commented lines.
Example 8-4. Valid pg_hba.conf comments
# Book Town host entries
#
#
host all 127.0.0.1 255.255.255.255 trust
Regarding the actual form of each host record, there are three general
types
available in the
pg_hba.conf
(the
type
keyword is always the
first word in the host record):
-
host
-
A host entry is used to specify remote hosts that are allowed to connect to
the PostgreSQL server. PostgreSQL's
postmaster
backend must be running with the
-i
option (TCP/IP) in order for a host entry to work
correctly.
-
local
-
A local entry is semantically the same as a
host entry. However, you do not need to specify a host that is allowed to connect. The local
entry is used for client connections that are initiated from the same machine that the PostgreSQL server is operating on.
-
hostssl
-
A hostssl entry is user to specify hosts (remote or local)
that are allowed to connect to the PostgreSQL server using SSL. The use of SSL insures that all
communication between the client and the server is encrypted. In order for this to work, both
the client and the server must support SSL. The
postmaster
backend must be running with the
-l
(SSL) and
-i
(TCP/IP) options.
Note: See Chapter 9 for more on how to start the
postmaster
process with
the appropriate run-time options.
Example 8-5 illustrates the general syntax for each type of host record available within
the
pg_hba.conf
file. Notice that the format is essentially identical for each record, with the
exception that a
local
record does not require an IP address or netmask to be specified, as
the connection is assumed to be from the same machine on which PostgreSQL is running.
Example 8-5. Host entry syntax
# A "local" record.
local
database auth_method [ auth_option ]
# A "host" record.
host
database ip_addr netmask auth_method [ auth_option ]
# A "hostssl" record.
hostssl
database ip_addr netmask auth_method [ auth_option ]
Note: Remember that each entry in the
pg_hba.conf
must be a single line. You cannot
word wrap or use line breaks.
The following list is a description of the keywords for the
pg_hba.conf
entries mentioned previously:
-
database
-
This is the database name that the specified host is allowed to connect to.
The
database
keyword has three possible values:
-
all
-
The all keyword specifies that the client
connecting can connect to any database the PostgreSQL server is hosting.
-
sameuser
-
The sameuser keyword specifies that the client
can only connect to a database that matches the clients authenticated user name.
-
name
-
A specific
name
may be specified, so that the client
can only connect to the database as specified by
name
.
-
ip_addr
,
netmask
-
The
ip_addr
and
netmask
fields specify
either a specific IP address, or range of IP addresses, that are allowed to connect to the PostgreSQL server. Such
a range can by specified by describing an IP network with an associated netmask. Otherwise, for a single IP
address, the
netmask
field should be set to
255.255.255.255.
If you are unsure of how to specify a netmask, view the online Linux Networking HOWTO, at
https://www.thelinuxreview.com/howto/networking
, or consult your system administrator.
-
auth_method
-
The authentication method specifies the type of authentication the server should
use for a user trying to connect to PostgreSQL. The following is a list of options
available for
auth_method
:
-
trust
-
The trust method allows any user from the defined host to connect to a
PostgreSQL database without the use of a password, as any PostgreSQL user. You are
trusting
the host-based
authentication with the use of this method, and any user on the specified host. This is a dangerous condition
if the specified host is not a secure machine, or provides access to users unknown to you.
-
reject
-
The reject method automatically denies access to PostgreSQL
for that host or user. This can be a prudent setting for sites that you know are
never
allowed to connect to your database server.
-
password
-
The password method specifies that a password must exist for a
connecting user. The use of this method will require the connecting user to supply a password that matches
the password found in the global pg_shadow system table for their username.
If you use the password method, the password will be sent in clear
text.
-
crypt
-
The crypt method is similar to the
password method. When using crypt,
the password is not sent in clear text, but through a simple form of encryption. The use of this
method is not very secure, but is better than using the clear text password
method.
-
krb4, krb5
-
The krb4 and krb5 methods are used to
specify Version 4 or 5 of the Kerberos authentication system. The installation and configuration of Kerberos
is beyond the scope of this book, but if you wish to authenticate via Kerberos, these methods are
available.
-
ident
-
The ident method specifies that an
ident map
should be used when a host is requesting connections from a valid IP address listed in the
pg_hba.conf
file. This method requires one option.
The required option may be either the special term sameuser, or a named
map that is defined within the
pg_ident.conf
file. For more information on defining an
ident map, see the Section called The pg_ident.conf file
."
-
auth_option
-
The auth_option field may or may not be required, based on the type of
authentication method that is used; as of PostgreSQL 7.1.x, only the ident method
requires an option.
|
Warning
|
|
We do not suggest the use of either password or
crypt without the use of an external encryption mechanism. See the Section called Encrypting sessions
" in this chapter for information on installing a central encryption mechanism for all of
your PostgreSQL traffic.
|
This section contains a series of examples that can be used within the
pg_hba.conf
. To begin,
the host record within Example 8-6 allows a single machine with the IP address
192.168.1.10 to connect to any database as any user, without the use of a password. This is because
it is configured with the all and trust terms,
respectively.
Example 8-6. Single host entry
host all 192.168.1.10 255.255.255.255 trust
Example 8-7 shows a host record which will reject all users from host
192.168.1.10, for any requested database. This is set by the use of the terms
all and reject as the database target and
authentication method, respectively.
Example 8-7. Rejection entry
host all 192.168.1.10 255.255.255.255 reject
The host record in Example 8-8 will allow any user with the IP of
192.168.1.10, and a valid password, to connect to the database template1. The
password will be encrypted during authentication because of the use of the term
crypt.
Example 8-8. Single host, single database entry
host template1 192.168.1.10 255.255.255.255 crypt
The host record in Example 8-9 allows a small subnet of computers to access any
database, without the need of a password. This subnet describes any IP from 192.168.1.1 to
192.168.1.15. Again, if you are unsure of how to configure your netmask, consult your network administrator, or
view the Linux Networking HOWTO at
https://www.thelinuxreview.com/howto/networking
.
Example 8-9. Small network connection entry
host all 192.168.1.0 255.255.255.240 trust
Expanding on the use of subnets, the host record in Example 8-10 allows any
machine on the 192.168.1 block to connect to the booktown database,
without the use of a password.
Example 8-10. Larger network connection entry
host booktown 192.168.1.0 255.255.255.0 trust
Remember, as stated earlier in this section, each host record line is read in succession from the top of the file
to the bottom. The first record which matches the host attempting to connect is used. If no matching record is found,
connection is completely disallowed.
When specifying the ident term as a host record's authentication method,
PostgreSQL uses the
pg_ident.conf
file to map the
identifying username
to a
PostgreSQL username. The identifying username is the name provided by the connecting client's
identd
service (RFC 1413), which is required to identify the name of the system account initiating
the connection. This method is similar to the trust method, but restricts access based
on the identifying username.
As stated in the specification for the
ident
protocol, "The Identification Protocol is not
intended as an authorization or access control protocol." This is only a useful method of identification for secure,
controlled machines, and is
not
intended as a means for secure control from a wide array of external
machines. This is because an
identd
daemon merely returns an arbitrary username describing the
current system user. For example, allowing the username jworsley from an entire subnet
of IP addresses would create a serious security hole, because anyone with a machine in that subnet could create a user named
jworsley and become "authenticated" as a result.
The
pg_ident.conf
file should be located in the same path as the
pg_hba.conf
file. This should be the path defined by the PGDATA environment
variable (e.g.,
/usr/local/pgsql/data
). Like the
pg_hba.conf
, changes to the
pg_ident.conf
file do not require PostgreSQL to be re-started.
The content of the
pg_ident.conf
associates identifying usernames with PostgreSQL usernames
via definitions called ident
maps
. This is useful for users whose system usernames do not match
their PostgreSQL usernames. Some rules you should keep in mind when defining and using an ident map are:
-
Each ident map
member
is defined on a single line, which associates a map name with an identifying username, and a translated PostgreSQL username.
-
The
pg_ident.conf
file can contain multiple map names. Each group of single lines with the same associative map name are considered a single map.
-
The
pg_hba.conf
file determines the types of connections that relate to users in this file.
A single line record to define an ident map consist of 3 tokens: the name of the map, the identifying username, and the
translated PostgreSQL username. This syntax is entered as follows, where each token is separated by spaces, or tabs:
mapname
identname
postgresqlname
-
mapname
-
The map name used in the
pg_hba.conf
file to refer to the ident map.
-
identname
-
The identifying username, which is generally the name of the system user attempting to establish a connection to the database.
This is the name provided by the
identd
daemon, which must be running on the system attempting to connect.
-
postgresqlname
-
The database username which is allowed for the preceding identifying username. You may specify several
lines with the same
identname
, but with different
postgresqlname
values,
in order to allow a single system user access to several accounts, which do not all need to be on the same database.
As an example, suppose that the Book Town server has a set of system accounts named
jdrake, jworsley, and
auditor, used for two salespeople and an internal auditor, respectively.
You may wish to create a pair of ident maps for these two groups of users. Suppose that the sales department's
workstation has an IP address of 192.168.1.3, and only needs access to the
booktown database, while the audit department's workstation has an IP address of
192.168.1.4, and requires access to all databases. This scenario might result in a
pga_hba.conf
,
such as the one displayed in Example 8-11.
Example 8-11. An ident configuration in pg_hba.conf
host booktown 192.168.1.3 255.255.255.255 ident sales
host all 192.168.1.4 255.255.255.255 ident audit
This host access configuration states that the sales machine may connect to the
booktown database using an ident map named
sales
, and the audit
workstation may connect to
any
database using an ident map named
audit
. Each of
these maps must then be configured within the
pg_ident.conf
file. Example 8-12
demonstrates such a configuration.
Example 8-12. A pg_ident.conf configuration
# MAP IDENT POSTGRESQL_USERNAME
sales jdrake sales
sales jworsley sales
audit auditor sales
audit auditor postgres
The file shown in Example 8-12 allows either of the system users
jdrake or jworsley to connect as the PostgreSQL
sales user, and allows the system user named auditor to
connect to PostgreSQL as either sales, or postgres.
Note: It is possible for an identifying username to be mapped to multiple PostgreSQL usernames. This is illustrated in
Example 8-12 with the auditor user.
If you wish only to use ident as a means of automatically identifying your remote
username, you do not need to use the
pg_ident.conf
file. You can instead use the special term
sameuser in the
pg_hba.conf
file, in place of a map name.
Again, this is similar to the trusted method, however
ident sameuser restricts connections based on the username provided by
identd
. Providing a PostgreSQL username to connect with (e.g., with the
-U
flag
to
psql
) that is different from the name sent by
identd
will result in a
failure to connect.
Use of the sameuser map is demonstrated in Example 8-13.
Example 8-13. A sameuser configuration
host booktown 192.168.1.0 255.255.255.0 ident sameuser
The host record in Example 8-13 allows any machine on the
192.168.1 network block to connect to the booktown database, using
the PostgreSQL username that matches the username provided by
identd
. The
sameuser term causes PostgreSQL to implicitly compare the requested PostgreSQL username
against the name provided by
identd
.
