This page in other versions: 9.0 / 9.1 / 9.2 / 9.3 / 9.4  |  Development versions: devel  |  Unsupported versions: 7.1 / 7.2 / 7.3 / 7.4 / 8.0 / 8.1 / 8.2 / 8.3 / 8.4

Chapter 21. Client Authentication

When a client application connects to the database server, it specifies which PostgreSQL database user name it wants to connect as, much the same way one logs into a Unix computer as a particular user. Within the SQL environment the active database user name determines access privileges to database objects — see Chapter 19 for more information. Therefore, it is essential to restrict which database users can connect.

Note: As explained in Chapter 19, PostgreSQL actually does privilege management in terms of "roles". In this chapter, we consistently use database user to mean "role with the LOGIN privilege".

Authentication is the process by which the database server establishes the identity of the client, and by extension determines whether the client application (or the user who runs the client application) is permitted to connect with the database user name that was requested.

PostgreSQL offers a number of different client authentication methods. The method used to authenticate a particular client connection can be selected on the basis of (client) host address, database, and user.

PostgreSQL database user names are logically separate from user names of the operating system in which the server runs. If all the users of a particular server also have accounts on the server's machine, it makes sense to assign database user names that match their operating system user names. However, a server that accepts remote connections might have many database users who have no local operating system account, and in such cases there need be no connection between database user names and OS user names.

Comments


Sept. 14, 2008, 9:05 a.m.

Anyone that familiar with password based authentication might need to check the Postgres configuration of their OS or distribution. For instance, Debian stored the configurations in /etc/postgresql/[version]/main directory. [version] is the version number of Postgres that you install. Two important files are pg_hba.conf and postgresql.conf.

pg_hba.conf is in charge for the selection of authentication methods as described in details in 21.2. The connection command shall be

psql -h [hostname] -U [username] [database]

[hostname] is your hostname which usually 'localhost', [username] is you postgres login name whereas [database] is your database name. Command without [database] being typed, Postgres assume your database name is the same as your login name, which is compulsory if you select ident-based authentication.

Enabling the use of [hostname] need you to edit postgresql.conf and uncomment "listen_addresses = [hostname]". In the other hand, your pg_hba.conf might require to use md5 for host as describe in 21.2.2

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group