Access Configuration

 POSTGRESQL allows administrators to control database access. Access can be granted based on the database, user, or TCP/IP network address. By default, POSTGRESQL allows database access only to users logged into the computer running the database server. To enable network access, the postmaster must be started with the -i flag.

Database access is controlled via the data/pg_hba.conf file, which is located in the POSTGRESQL home directory. It contains several types of configuration entries:

  
Local

Local entries control access by users logged into the same computer as the database server. Local connections use Unix domain sockets. The following per-database authentication options are available:

• trust --Trust users connecting to this database.
• password --Require a password of users connecting to this database.
• crypt --Like password, except send the password in an encrypted manner. This method is more secure than password.
• reject --Reject all connection requests for this database. 

   
Host and Hostssl

Host and hostssl entries control TCP/IP network access. They include host and netmask fields. These entries support all of the local options, plus the following:

• ident --Use a remote ident server for authentication.
• krb4 --Use Kerberos  IV authentication.
• krb5 --Use Kerberos V authentication.

These entries are effective only if the postmaster uses the -i option. Hostssl controls access via the Secure Socket Layer (SSL ) if enabled in the server.  

User Mappings

By default, passwords used by password  and crypt  appear in the pg_shadow table. This table is managed by createuser and ALTER USER .

In addition, password  takes an optional argument that specifies a secondary password file which overrides pg_shadow. This file contains user names and passwords of people who are allowed to connect. Using this method, a set of users can be given access to certain databases. See the pg_passwd  manual page for more information on creating secondary password files. Currently, crypt  does not support secondary password files.

The ident  entry also takes an optional argument that specifies a special map name to map ident  user names to database user names. The file data/pg_ident.conf records these mappings.

Examples

Local  entries are configured on per-database hosts. A database entry of all applies to all databases. In data/pg_hba.conf, the lines

 

        local        all                                         trust
        host         all         127.0.0.1     255.255.255.255   trust  

cause all local users to be trusted. The first line affects users connecting via Unix domain sockets; the second line controls local users connecting to the same machine by TCP/IP. The local machine is accessed as TCP/IP address 127.0.0.1 (localhost).

Both host and hostssl entries require the additional specification of host addresses and network masks. The lines

 

        host         all        192.168.34.0   255.255.255.255   crypt
        host         all        192.168.90.0   255.255.255.0     password  

force all users from host 192.168.34.0 and network 192.168.90.0 to provide passwords. Crypt  encrypts the passwords that are sent; password  sends passwords over the network without encryption. The line

 

        host         all        192.168.98.0   255.255.255.255   password finance  

is similar to the previous entries, except that it uses the user names/passwords stored in finance to authenticate users.

The lines

 

        host         sales      192.168.7.12   255.255.255.255   ident 
        host         sales      192.168.7.64   255.255.255.255   ident support  

use ident on the remote machine to verify the users connecting to database sales from the hosts 192.168.7.12 and 192.168.7.64. The second entry uses the support user name mapping in data/pg_ident.conf.

Connections from hosts and networks not appearing in the file are rejected. For more information, see the file data/pg_hba.conf and the Administrator's Guide discussed in Appendix .

For database client applications, the environment variables PGHOST , PGPORT , PGUSER , PGPASSWORD , PGDATESTYLE , PGTZ , PGCLIENTENCODING , and PGDATABASE  are helpful for setting default connection parameters and options. The POSTGRESQL documentation provides more information about them.