Access control
Connection and authentication
© Postgres Professional, 2015–2022
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov
Translated by Alexander Meleshko
Use of course materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Please send your feedback, comments and suggestions to:
Postgres Professional assumes no responsibility for any damages and
losses, including loss of income, caused by direct or indirect, intentional or
accidental use of course materials. Postgres Professional company
specifically disclaims any warranties on course materials. Course materials
are provided “as is,” and Postgres Professional company has no obligations
to provide maintenance, support, updates, enhancements, or modifications.
Configuration files
Simple authentication methods
Password-based authentication
External authentication and name mapping
Steps during connection
identify the database user name
the name may differ from the one specified (for external authentication)
is the user really who they claim they are?
some sort of confirmation is usually required (e.g., a password)
is this user allowed to connect to the server?
partially overlaps with privileges
When a client initiates a connection, the server must perform several tasks.
Firstly, the server must identify the user, that is, determine their user name.
To do this, the server asks the user to provide their user name. The
specified name may differ from the name of the database user (for example,
if the user is registered under their OS user name).
Next, the server authenticates the user, or verifies that they are who they
claim to be. A simple way to achieve that is by requesting a password.
Lastly, the server authorizes the user, that is, determines whether they are
allowed to connect to the server (this task is partially overlapped with
All three tasks are often referred to as “authentication”. PostgreSQL offers
significant flexibility when it comes to configuring the authentication process.
So far, we've been connecting to the server without any sort of
authentication. In Ubuntu, the default settings allow users to connect to a
local server without authorization, if the database user name matches the
OS user name. Additionally, in the course VM, PostgreSQL is additionally
configured to allow any local connections.
General configuration
the configuration file, has to be reloaded for any changes to apply
lines are composed of fields, space or tab-separated
empty lines and any text after a comment sign (#) are ignored
connection type
database name
user name
host address
authentication method
optional parameters in the key=value format
connection parameters
Authentication settings are stored in a configuration file. The file functions in
a similar way to postgresql.conf, but has a different format. The file is called
pg_hba.conf (from “host-based authentication”). Its location is determined by
the hba_file parameter. For any changes made to the configuration to apply,
the file must be reloaded (by using pg_ctl reload or calling the
pg_reload_conf function).
The pg_hba.conf file contains a number or lines, each constituting a
separate record. Empty lines and comments (anything after a # sign) are
ignored. A line contains a number of fields separated by tabs or spaces.
The number of fields may vary depending on the type of content. See the
slide for details.
Processing the file
Records are read from top to bottom
The first record that corresponds to the attempted connection
(type, database, user, and address) is applied
authentication and verification of the CONNECT privilege are performed
if the result is negative, access is denied
if none of the records correspond to the connection parameters, access is
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all trust
# IPv6 local connections:
host all all ::1/128 trust
The configuration file is processed from top to bottom. Each record is
matched against the parameters of the connection requested by the client
(by checking the connection type, database name, user name and IP
address). If a corresponding record is found, the authentication method
specified in the record is performed. Upon successful authentication, the
connection is permitted, otherwise it is denied (no other records are checked
after this point).
If no records correspond to the connection parameters, access is also
Thus, the records in the file should go from top to bottom from more specific
to more general.
At the bottom of the slide is a fragment of the default file you end up with
when building from source (may be different when installing from a
package). In this example, there are three records. The first one refers to
local non-TCP connections (local) for all databases (all) and users (all). The
second one is for remote connections (host) from the address
(localhost), and the third is the same, but for IPv6.
So, by default PostgreSQL allows only local connections (including local
network connections).
Some of the possible field values are discussed more closely later in this
Connection parameters
Connection type
Database name
Host address
Role name
Connection type
local connection via a Unix domain socket
TCP/IP connection
(usually the listen_addresses parameter has to be changed)
encrypted SSL connection over TCP/IP
(the server must be compiled with SSL support, and the ssl parameter must
be set)
unencrypted TCP/IP connection
The connection type field contains one of the values listed below.
“local” allows a local connection via a Unix domain socket (without using a
network connection).
“host” allows any TCP/IP connection. Since by default PostgreSQL listens to
connections only from the local address (localhost), you will most likely need
to set a different address using the listen_address server parameter.
“hostssl” allows only an encrypted SSL connection over TCP/IP.
Such connections require that the server is complied with SSL support.
In addition, you need to set the ssl = on parameter.
“hostnossl” allows only unencrypted TCP/IP connections.
Database name
connecting to any database
a database which name matches the user role name
a database which name matches the user role name or a group name that the
user is a member of
a special permission for the replication protocol
a specific database name (may be in quotes)
several names from the list
In the database field, you can specify one of the values listed below, or
several such values separated by commas.
The word “all” corresponds to any database.
The word “sameuser” corresponds to a database that matches the user
The word “samerole” corresponds to a database that matches the name of
any role that the user is a member of (including the user's own, since the
user is also a role).
Any specific database name can be listed here, too.
A list of database names can be stored in an external file and linked to using
the @ sign. The external file can store names separated by commas,
spaces, tabs or line breaks. Nested file links (@) and comments (#) are
Host address
any IP address
IP address/mask_length
specified IP address range (i.e.
or an alternative form with two fields (
server IP address
any IP address from any subnet to which the server is connected
the IP address matching the specified name (i.e.
any part of name can be specified, starting with a dot (.com)
The address field may contain one of the following values.
“all” corresponds to any client IP address.
IP address with a subnet mask length (CIDR) defines the range of valid IP
addresses. Alternatively, the IP address can be specified in one field and the
subnet mask in the next. IP addresses in the IPv6 notation are also
“samehost” corresponds to the IP address of the server (this is an
alternative of for systems where such an address is not allowed).
“samenet” corresponds to any IP address from any subnet to which the
server is connected.
Lastly, the address can be specified as a domain name (or a part of it,
starting with a dot). PostgreSQL will determine whether the client’s IP
address belongs to the domain. To do this, the domain name is first looked
up using the IP address (reverse lookup), and then PostgreSQL checks if
the source IP address really corresponds to such a domain (forward
lookup). This matches the network owner with the domain name owner, thus
blocking out compromised addresses:
Role name
any role
a role with a specific name (possibly in quotes)
a role that is a member of the specified role
multiple names in the formats given above
In the user name field, you can specify one of the values listed below, or
several such values separated by commas.
“all” corresponds to any client IP address.
Role name corresponds to the user (or role, which is the same) with the
specified name. If the role name is preceded by a + sign, then the name
corresponds to any user who is a member of the specified role.
A list of database names can be stored in an external file and linked to using
the @ sign. The external file can store names separated by commas,
spaces, tabs or line breaks. Nested file links (@) and comments (#) are
Simple authentication
Doesn’t check anything
Simple authentication
allow without authentication
refuse without authentication
Various methods can be specified in the authentication method field.
To begin with, let’s look at the two simplest ones.
The “trust” method unconditionally trusts the user and does not perform
verification. In real life, it should never be used for anything but local
The “reject” method unconditionally denies access. It can be used to cut off
any connections of a certain type or from certain addresses (for example, to
prohibit unencrypted connections).
What does the configuration below mean?
hostnossl all all all reject
host sameuser all samenet trust
host pub +reader all trust
1. Unencrypted connections are prohibited.
2. Users are allowed to access databases that match their user names from
server's subnet.
3. Users who are members of the reader role are allowed access
to the pub database.
Note that the first record cannot be moved down, or the configuration result
will change.
Password-based authentication
The server requests a password from the client
Passwords inside DBMS
transmitted unencrypted
an MD5 hash is transmitted
the SCRAM protocol is used
During password authentication, the PostgreSQL server requests a
password from the user and checks it matches the password stored either in
the database itself or in an external service.
For passwords stored in the database, three methods are supported.
The md5 method compares the MD5 hash of the password with the MD5
hash stored in the database. Upon request, the server sends the so-called
"salt" to the client, the client calculates the MD5 hash of the password, adds
the salt, calculates the MD5 hash again and sends it to the server, where it
is compared with the stored hash. Thanks to the salt, the same password
can result in different hash values. However, the MD5 algorithm is currently
considered insufficiently cryptographically secure.
The most secure method scram-sha-256 uses the SCRAM protocol for
authentication and employs the SHA-256 algorithm. The method implements
the SASL framework that separates the authentication mechanism from the
application protocol.
The password method transmits the password in plain text. It should not be
used if the client-server connection is not encrypted.
Passwords inside DBMS
Set a user password
PASSWORD 'password'
[ VALID UNTIL date_time ];
a user with an empty password will be denied access during password
Passwords are stored in the system catalog
the encryption method is determined by the password_encryption parameter
the authentication method must match the encryption method
(md5 automatically switches to scram-sha-256)
So far, we’ve been creating roles without specifying any passwords. If the
password authentication method is set, such users will be denied access.
Passwords are stored in the database in the pg_authid table.
To set a password, you must specify it either immediately when creating a
role with the CREATE ROLE command, or later with the ALTER ROLE
command. Passwords are stored in encrypted form. The encryption
algorithm (MD5 or SCRAM-SHA-256) is determined by the
password_encryption parameter.
You can optionally specify a password expiration time.
If a stored password is encrypted with the SCRAM-SHA-256 algorithm and
the authentication method is set to use MD5, the more reliable SCRAM-
SHA-256 method will be used during communication instead.
Entering a password
Set the PGPASSWORD variable
inconvenient when connecting to different databases
not recommended for security reasons
The passwords file
~/.pgpass at the client host
lines in the format host:port:database:username:password
may use the * sign (any value)
records are checked from top to bottom, the first match is used
the file must have permission 600 (rw-------)
The password can be entered manually every time, or the input can be
automated. There are two ways to do it.
First, the password can be set in the PGPASSWORD environment variable
(on the client). However, this is inconvenient if you frequently connect to
multiple databases. It also poses some security risks.
Otherwise, you can store passwords in the ~/.pgpass file (its location is
defined by the PGPASSFILE environment variable). Access to the file must
be restricted to the owner alone, or PostgreSQL will ignore it.