3.2. Authentication and Encryption
Client authentication is a central feature to PostgreSQL. Without it, you would either have to sacrifice remote connectivity, or blindly allow anyone to connect to your database and retrieve, or even modify your data. PostgreSQL has several different types of client authentication at its disposal. As the site administrator, you need to decide which one is best for your system.
Host-based client access is specified in PostgreSQL's pg_hba.conf file. The rights and restrictions described in this file should not be confused with a PostgreSQL user's rights to objects within the database. The pg_hba.conf file allows you to set the type of host-based authentication to be used. This authentication is performed before PostgreSQL establishes a connection to the intended database, where user rights would be relevant.
 | The pg_hba.conf is located in the PostgreSQL data directory by default (e.g., /usr/local/pgsql/data ), and is installed automatically upon the execution of the initdb command when PostgreSQL is installed. |
PostgreSQL's host-based authentication is flexible, featuring a wide variety of configurable options. You may restrict database access to specific hosts, as well as allow access to a range of IP addresses by using netmasks. Each configured host has its own host record, which is a single line in the pg_hba.conf file.
With these host records, you may specify access either to a particular database or all databases. Furthermore, you may require a user from a specified host to authenticate via the PostgreSQL users table after qualifying for a connection.
Put simply, the pg_hba.conf file allows you to determine who is allowed to connect to which databases from what machines, and to what degree they must prove their authenticity to gain access.
 | Through remote password-based authentication, passwords may be transmitted in clear text (although it may be hashed) depending on whether or not you are using encrypted sessions. Be sure that you understand how your application is communicating with PostgreSQL before allowing users to remotely connect to a PostgreSQL database. |
3.2.1. Password Authentication
Passwords allow PostgreSQL users a way to identify themselves and prevent unauthorized individuals from connecting with a user that is not theirs. In PostgreSQL 8.1dev, user passwords are stored as a md5 hash in the pg_shadow system table. The structure of this table is illustrated in Table 3-2.
Table 3-2. The pg_shadow table
Column | Type |
|---|---|
usename | name |
usesysid | integer |
usecreatedb | boolean |
usesuper | boolean |
usecatupd | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
The pg_shadow table is a system table, and thus is accessible from any database. It follows, therefore, that users are not assigned to a specific database.
Users typically set passwords in PostgreSQL when the user is created (with the CREATE USER command) or after the user has been created (using the ALTER USER command). Alternatively, you may manually modify a user's password by using an UPDATE statement. (For a more detailed explanation about defining passwords for users, see Chapter 10.)
If a password is not set, a user's password defaults to NULL. If a password-based authentication is enabled in the pg_hba.conf file, connection attempts will always fail for such a user. Conversely, if the host that establishes the connection is a trusted host (such as localhost, by default), anyone from the trusted host may connect as a user with a NULL password. In fact, passwords are ignored entirely for trusted hosts.
| The GRANT command allows you to restrict or allow a variety of access types to tables within a database. See Chapter 10 for more on this topic. |
Unless your needs for security are very minimal, you will not want to rely on password-only authentication with your PostgreSQL server. Using a password-only method to authenticate users will allow any verified user access to any database on the system. If you are likely to have your database connected to the Internet in some fashion, we strongly suggest that you read the following sections. These cover the use of the pg_hba.conf file and session encryption.
3.2.2. The pg_hba.conf file
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.
| 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 3-17 is a simple example of the pg_hba.conf file.
Example 3-17. A simple pg_hba.conf file
# PostgreSQL HOST ACCESS CONTROL FILE # local all trust host all 127.0.0.1 255.255.255.255 md5 host booktown 192.168.1.3/32 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 3-18 to see the same information provided by the \z slash command.
Example 3-18. 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)3.2.2.1. Structure of the pg_hba.conf file
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 3-19 shows two host records, the first with the fields separated by spaces, and the second with the file separated by tabs.
Example 3-19. 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 3-20 demonstrates valid commented lines.
Example 3-20. 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 four 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 used 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 as well.
- hostnossl
A hostnossl entry is used to specify hosts that may not connect to the PostgreSQL database using SSL. One reason for this option would be to ensure that you are not allowing encrypted connections via local connections as they could effect performance.
| See Chapter 4 for more on how to start the postmaster process with the appropriate run-time options. |
Example 3-21 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 3-21. Host entry syntax
# A "local" record. local DATABASE USER METHOD [OPTION] # A "host" record. host DATABASE USER CIDR-ADDRESS METHOD [OPTION] # A "hostssl" record. hostssl DATABASE USER CIDR-ADDRESS METHOD [OPTION] # A "hostnossl" record. hostnossl DATABASE USER CIDR-ADDRESS METHOD [OPTION]
| 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. You may use any combination by utlizing a comma separated list of the keywords. The database keyword has four 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.
- samegroup
The samegroup keyword specifies that the user must be a member of the group with the same name as the requested database.
- name
A specific name may be specified, so that the client can only connect to the database as specified by name. You may also specify an external text file that contains a list of databases that a user may connect to by prepending a @ sign followed by the path to the file.
- USER
A comma separated list of users or groups. To use a group name instead of a user name, prepend the entry with a + symbol. You may also specify an external text file that contains a list of users or groups that a user may connect to by prepending a @ sign followed by the path to the file.
- CIDR-ADDRESS / ip_addr, netmask
The older format of ip_addr and netmask fields can still be used to specify either a specific IP address, or range of IP addresses, that are allowed to connect to the PostgreSQL server. However for manageability you may want to move to the CIDR-ADDRESS format. Such a range can by specified by describing an IP network with an associated netmask. For a single IP address using the older format, the netmask field should be set to 255.255.255.255.
The CIDR_ADDRESS format can be used in place of the older format of ip_addr and netmask. Using this format you do not need to specify netmask.
- 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.
- md5
The md5 method specifes that a md5 hash of the users password must exist for the user. This method is preferred over trust, password, or crypt by the author.
- 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.
- krb5
The krb5 methods are used to specify 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 Section 3.3."
- pam
The pam method specifies that a client will authenticate using Pluggable Authentication Modules. Only the username and associated password may be authenticated at this time so the user must exist in PostgreSQL as well as your authentication source. The default service for pam is named postgresql.
- auth_option
The auth_option field may or may not be required, based on the type of authentication method that is used. Typically this option is only used with the ident method of authentication.
We do not suggest the use of either password or crypt without the use of an external encryption mechanism. See Section 3.4" in this chapter for information on installing a central encryption mechanism for all of your PostgreSQL traffic.
3.2.2.2. Example pg_hba.conf entries
This section contains a series of examples that can be used within the pg_hba.conf. To begin, the host record within Example 3-22 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 3-23 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.
The host record in Example 3-24 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 3-25. Single host, single database entry with CIDR style
host template1 192.168.1.10/32 crypt
The host record in Example 3-26 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.
Expanding on the use of subnets, the host record in Example 3-28 allows any machine on the 192.168.1 block to connect to the booktown database, without the use of a password.
Example 3-30. Using an external file for user lists
host all @/usr/local/pgsql/etc/dbusers 127.0.0.1/32 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.