Chapter 6. Authentication and Encryption
- Table of Contents
- Client Authentication
- Encrypting sessions
The following chapter is designed to give you the basic information needed to correctly configure the pg_hba.conf file and its related authentication mechanisms. We will also talk about three methods of encrypting sessions between your PostgreSQL client application and the PostgreSQL server.
Client Authentication
Client authentication is a central feature to using PostgreSQL, without it, anyone could connect to your database and retrieve your data. PostgreSQL has the ability to use several different types of client authentication. As the site administrator, you will need to decide which one is best for your type of system.
The current method of specifying a clients rights to a database is through the pg_hba.conf file. This should not be confused with a PostgreSQL users rights to a database. The pg_hba.conf only allows you to set the type of host based authentication to be used. The host based authentication is flexible in that you can require that the user also authenticate against the PostgreSQL user table as well. In other words, just because you can connect to PostgreSQL from one machine, doesn't mean you can from a different machine.
 | Host and User access |
|---|---|
It is possible to allow anybody to access a PostgreSQL database that has a correct username and password but passwords may be transmitted in clear text depending on the environment. Make sure you understand how your application is communicating with PostgreSQL before allowing any user to remotely connect to a PostgreSQL database. |
Password Authentication
PostgreSQL user passwords are stored in the pg_shadow table. The use of a password allows defined users a way to identify themselves and access a database that they have been granted rights to. Managing passwords in PostgreSQL is typically done by using the CREATE USER command or modified using the ALTER USER command. If a password is not configured for a user, then the password will default to NULL and password authentication (if specified in the pg_hba.conf file) will always fail for that user. We have included basic information on setting PostgreSQL passwords in the following paragraph. For a more detailed explanation about defining passwords for users, see the User Management chapter.
The pg_shadow table is a PostgreSQL system wide table. This means that you do not have the ability to assign users to a specific database. If a user exists in the pg_shadow table that user will be able to connect to any database on the server machine. Thus, you will want to make sure that you make appropriate use of the GRANT command. The GRANT command will allow you to restrict access to tables within a given database. The pg_shadow table can be accessed from any PostgreSQL database. Outside of the methods mentioned previous you can also modify a user password by using an UPDATE query on the password information stored in the pg_shadow table.
Example 6-1. Using UPDATE to modify a users PostgreSQL password
booktown=# UPDATE pg_shadow SET passwd='js5429' WHERE usename='jd';
UPDATE 1
booktown=#
You will not want to use password only authentication with your PostgreSQL server unless your needs for security are very minimal. If you are using PostgreSQL you are more than likely going to have your database connected to the Internet in some fashion. As this is the case, we strongly suggest that you read the following sections on using the pg_hba.conf file. As mentioned previously, Using only the password method to authenticate users will allow any user who has been verified to have access to any database on the system.
 | Operating System users versus PostgreSQL users |
|---|---|
Keep in mind that PostgreSQL has its own user and password tables. It is not a requirement that your PostgreSQL users match the users that are available to the Operating System. |
The pg_hba.conf File
We mentioned previously that the pg_hba.conf file enables client authentication to occur between the PostgreSQL server and the client application. Upon the application initiating a connection, the application will specify PostgreSQL user name it wants to connect to PostgreSQL with. PostgreSQL, being a client-server architecture will check the pg_hba.conf on each connection made to the server and verify that the machine hosting the application has rights to connect to the database. If the machine requesting access has the correct permissions to connect, PostgreSQL will then check the conditions that the application must meet in order to successfully authenticate. This includes connections that are initiated locally.
PostgreSQL will check the authentication method via the pg_hba.conf for each connection created. As this check is perfomed everytime a new connection is made to the PostgreSQL server, there is no need to restart PostgreSQL if you add an entry into the pg_hba.conf file. When a connection is initalized, PostgreSQL will read through the pg_hba.conf one entry at a time. As soon as PostgreSQL finds a matching record, PostgreSQL will stop searching and allow or reject the connection based on the entry. If PostgreSQL does not find a matching entry in the pg_hba.conf the connection fails.
The pg_hba.conf will be located in the $PGDATA directory (/usr/local/pgsql/data/) and is installed by default upon the execution of initlocation.
The table level permissions still apply to a database even if a user has permissions to connect to the database. If you can connect but can not select data from a table, you may want to verify that a user has permissions to that table. If you are using the psql application you can check the permissions of the tables within a database by using the \z option. If you are not using psql, you can use the following query to check the permissions within a database.
Example 6-2. Checking User Permissions on Tables
SELECT relname as "Table",
relacl as "User Permissions"
FROM pg_class
WHERE relkind in ('r', 'v', 'S') AND
relname !~ '^pg_'
ORDER BY relname;
Structure of pg_hba.conf
The pg_hba.conf file contains records that defines the authentication methods and options PostgreSQL should use during client authentication process. The authentication process is designed to be customized to your system needs.
With the pg_hba.conf file you can specify that a user connecting from a specific TCP/IP address is authorized to access a certain database. You can also give access to PostgreSQL for those who are on your local network access. You can define any combination of these methods through the adding of a record to the pg_hba.conf file. The pg_hba.conf file maintains the following syntax structure.
You may only place on host record per line in the file.
Host records are not allowed to wrap to multiple lines.
You may comment the file. Commenting is done by placing a hash mark (#) at the beginning of each line being commented.
Each record will contain multiple fields. The quantity of fields is directly related to the type of host entry. The fields may be separated by tabs or spaces.
There are three types of entries available in the pg_hba.conf:
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 pg_hba.conf entry is used for client connections that are initated from the same machine that the PostgreSQL server is operating on.
host -- A host entry is used to specify remote hosts that are allowed to connect to the PostgreSQL server. PostgreSQL must be running with the -i option (TCP/IP) in order for a host entry to work correctly.
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 PostgreSQL server must also be running with the -l (SSL) and -i (TCP/IP) options.
| Single line entries only |
|---|---|
Remember that each entry in the pg_hba.conf must be a single line. You can not word wrap or use line breaks. |
The following is a description of the keywords for the pg_hba.conf entries mentioned previously.
- database
The is the database name that the client is allowed to connect to. The database keyword has three options associated with it.
- 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 the database that matches the clients authenticated user name.
- name
The name keyword specifies that the client can only connect to the database as specified by name .
- IP address, IP mask
The two fields IP Address and IP mask specify either a specific IP or range of IP that are allowed to connect to the PostgreSQL server. The range is used by specifying an IP network with an associated mask.
- authentication 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 avaialble:
- trust
The trust condition allows a user to connect to the PostgreSQL database without the use of a password. You are trusting the host based authentication with the use of this method.
- reject
The reject condition automatically denies access to PostgreSQL for that host or user.
- password
The password condition specifies that a password should exist for this user. The use of this method will require the PostgreSQL user to supply a password that matches the password found in the pg_shadow table. If you use the password method, the password will be sent in clear text.
- crypt
The crypt condition is similar to the password method. However, when using crypt the password is not sent in clear text. The use of this method is not very secure but is better than using the clear text password method.
Password and Crypt We do not suggest the use of either of these methods without the use of an external encryption mechanism. There are sections later in this chapter that cover installing a central encryption mechansim for all of the PostgreSQL traffic.
- krb4
The krb4 condition is used to specify version 4 of the Kerberos authentication system.
- krb5
The krb5 condition is used to specify version 5 of the Kerberos authentication system.
Kerberos The installation and configuration of Kerberos is beyond the scope of this book.
- ident
The ident condition 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. For more information on the use of ident please see the ident section.
The ident condition has one option. This option specifies the name of the ident map that will be used in conjunction with the ident condition. The ident map is a text file. For more information on defining an ident map, see the section on the pg_ident.conf file.
- authentication option
An optional field based on the type of authentication that is used. Please refer to the specific type of authentication you are using to determine whether or not this is required.
Example pg_hba.conf entries.
The following is a series of examples that can be used within the pg_hba.conf. The first allows a single IP to connect to all databases, without the use of a password.
This example will reject all users from host 192.168.1.10.
This example 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.
This example allows a small subnet of computers to access the booktown database.
The pg_ident.conf file
Uses the pg_ident.conf file to match the connecting user with the PostgreSQL database user name. These users are on the local network (your network structure may be different). It uses the connecting user name to evaluate if that user has permission to connect or not. If you want to allow a user to connect as a different user name from the connecting user name, add a record into the pg_ident.conf file. For more information on this file, refer to the next section.
host booktown 183.190.0.0 255.255.255.255 ident sameuser
For more examples, take a look at the pg_hba.conf file under the $PGDATA directory (/usr/local/pgsql/data/). It contains a comment section with several different examples.
| Commenting |
|---|---|
To add a single line comment into this file, use the hash mark (#) to start the beginning of a comment. |
The ident keyword
This key word is used in the pg_hba.conf file for users who have an ident map defined. An ident map is a record that identifies the operating system users with their corresponding PostgreSQL database user names. The ident map is defined in the pg_ident.conf file. This file is located beneath the same directory path as the pg_hba.conf file, which is the $PGDATA directory (/usr/local/pgsql/data/).
Users with an ident map defined can connect to the database server via TCP/IP as another database user name. Usually, the system user name is used to connect to PostgreSQL. Some rules you should keep in mind when defining and using an ident map are:
The pg_ident.conf file can contain multiple ident maps.
The pg_hba.conf file determines the types of connections that relate to users in this file.
Each ident map is defined by a one line record.
A record to define an ident map consist of 3 tokens. The syntax to define an ident map in the pg_ident.conf file is:
map ident_username Postgres_username
These tokens are defined as:
- map name
the name used in the pg_hba.conf file to refer to ident user map.
- ident username
this is usually the name of the system user connecting into the database.
- Postgres username
the database user name equivalent to the ident user name.
Using the booktown database, there are employees william and julene. Julene can have access to the julene user and julie user on the booktown database. The next records defines for the system user name wjulene two other database user names for her to log onto. While the system user hwill can only connect using the william database user name.
#MAP IDENT POSTGRES_USERNAME
#----------------------------------------------------
sales hwill william
temp wjulene julene
temp wjulene julie
For this to work requires adding a record into the pg_hba.conf file for each ident map. For example, the following records could be added to the pg_hba.conf file to correspond with the users you defined ident map for:
host all 183.190.35.1 255.255.255.255 ident sales
host template1 183.190.53.0 255.255.255.255 ident temp
The first record defined allows system user hwill to connect to all databases as the PostgreSQL user william. The second record defines system user wjulene to only have access to the template1 database, but she can connect as either the julene or julie PostgreSQL user.
| Mapping to Several Users |
|---|---|
It is possible for an operating system user to map to multiple PostgreSQL user names. This can be seen with the previous example. A user can specify the user name to log in as when prompted during connection. |
If you are planning to have one-to-one correspondence between system user names and PostgreSQL user names, then you do not need to use the pg_ident.conf file. Instead, you can use the special map name "sameuser" in the pg_hba.conf file. Again, using our previous example, we can specify all of those on the same network (the TCP/IP address will depend on your network structure) to have permissions to connect with the same PostgreSQL database name as their system user name:
host all 183.190.0.0 255.255.255.255 ident sameuser
| Commenting |
|---|---|
To add a single line comment into this file, use the hash mark (#). |
Authentication Failures
When authentication failures and related problems occur, similar messages like the following examples will appear. Here are some common error messages that you may encounter and their explanations:
- FATAL 1: user "testuser" does not exist
The specified user name was not found in the pg_shadow table. You can create this user using the CREATE USER sequel command. The user will then automatically be added to the pg_shadow table.
- FATAL 1: Database "testdb" does not exist in the system catalog
This database cannot be found because it does not exist. If you don't specify a database name, it will default to the username. This might not be the database you want to connect with. The best way is to specify the exact database name. Refer to the pg_hba.conf file for more information on how to specify that a user should connect to a certain database.
- No pg_hba.conf entry for host 123.123.123.123, user testuser, database testdb
You have succeeded in contacting the server, but the server is not accepting your connection. The server refused the connection because it cannot find an entry for this testuser at the the IP address in the pg_hba.conf file.
- Password authentication failed for user 'testuser'
You have succeeded in contacting the server and it is replying back, but you failed the password authorization. Check the password you are supplying to the server and make sure it is correct. Also, you can check the Kerberos or IDENT software programs if you are using them for password authentication.
You can check to see if this user even has a password. If this user does not have a password, and the pg_hba.conf file is set to check for passwords, it will still check every user for their password. For all users without a defined password, a NULL password is assigned to that user. When the user tries to log in and does not specify a password, it will compare the NULL password to the NULL input, and it will return a FALSE.
On the other hand, if the user tries to supply a password, it will compare that input value with the NULL password and return a FALSE. Therefore, if you are using password authentication, you must assign a password to all users. If a password is not assigned to users, then password authentication will always fail for those users and they will not be able to log in.