Unsupported versions: 7.0 / 6.5
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.

Chapter 22. Security

Database security is addressed at several levels:

  • Data base file protection. All files stored within the database are protected from reading by any account other than the Postgres superuser account.

  • Connections from a client to the database server are, by default, allowed only via a local Unix socket, not via TCP/IP sockets. The backend must be started with the -i option to allow non-local clients to connect.

  • Client connections can be restricted by IP address and/or user name via the pg_hba.conf file in PG_DATA.

  • Client connections may be authenticated vi other external packages.

  • Each user in Postgres is assigned a username and (optionally) a password. By default, users do not have write access to databases they did not create.

  • Users may be assigned to groups, and table access may be restricted based on group privileges.

User Authentication

Authentication is the process by which the backend server and postmaster ensure that the user requesting access to data is in fact who he/she claims to be. All users who invoke Postgres are checked against the contents of the pg_user class to ensure that they are authorized to do so. However, verification of the user's actual identity is performed in a variety of ways:

From the user shell

A backend server started from a user shell notes the user's (effective) user-id before performing a setuid to the user-id of user postgres. The effective user-id is used as the basis for access control checks. No other authentication is conducted.

From the network

If the Postgres system is built as distributed, access to the Internet TCP port of the postmaster process is available to anyone. The DBA configures the pg_hba.conf file in the PGDATA directory to specify what authentication system is to be used according to the host making the connection and which database it is connecting to. See pg_hba.conf(5) for a description of the authentication systems available. Of course, host-based authentication is not fool-proof in Unix, either. It is possible for determined intruders to also masquerade the origination host. Those security issues are beyond the scope of Postgres.