1 <!-- $Header: /cvsroot/pgsql/doc/src/sgml/client-auth.sgml,v 1.29 2001/11/21 05:53:40 thomas Exp $ -->
3 <chapter id="client-authentication">
4 <title>Client Authentication</title>
6 <indexterm zone="client-authentication">
7 <primary>client authentication</primary>
11 When a client application connects to the database server, it specifies which
12 <productname>PostgreSQL</productname> user name it wants to connect as,
13 much the same way one logs into a Unix computer as a particular user.
14 Within the SQL environment the active
15 database user name determines access privileges to database
16 objects -- see <xref linkend="user-manag"> for more information
17 about that. It is therefore obviously essential to restrict which
18 database user name(s) a given client can connect as.
22 <firstterm>Authentication</firstterm> is the process by which the
23 database server establishes the identity of the client, and by
24 extension determines whether the client application (or the user
25 who runs the client application) is permitted to connect with the
26 user name that was requested.
30 <productname>PostgreSQL</productname> offers a number of different
31 client authentication methods. The method to be used can be selected
32 on the basis of (client) host and database; some authentication methods
33 allow you to restrict by user name as well.
37 <productname>PostgreSQL</productname> database user names are logically
38 separate from user names of the operating system in which the server
39 runs. If all the users of a particular server also have accounts on
40 the server's machine, it makes sense to assign database user names
41 that match their Unix user ids. However, a server that accepts remote
42 connections may have many users who have no local account, and in such
43 cases there need be no connection between database user names and Unix
47 <sect1 id="pg-hba-conf">
48 <title>The <filename>pg_hba.conf</filename> file</title>
50 <indexterm zone="pg-hba-conf">
51 <primary>pg_hba.conf</primary>
55 Client authentication is controlled by the file
56 <filename>pg_hba.conf</filename> in the data directory, e.g.,
57 <filename>/usr/local/pgsql/data/pg_hba.conf</filename>. (<acronym>HBA</> stands
58 for host-based authentication.) A default <filename>pg_hba.conf</filename>
59 file is installed when the
60 data area is initialized by <application>initdb</application>.
64 The general format of the <filename>pg_hba.conf</filename> file is
65 of a set of records, one per line. Blank lines and lines beginning
66 with a hash character (<quote>#</quote>) are ignored. A record is
67 made up of a number of fields which are separated by spaces and/or
68 tabs. Records cannot be continued across lines.
72 Each record specifies a connection type, a client IP address range
73 (if relevant for the connection type), a database name or names,
74 and the authentication method to be used for connections matching
76 The first record that matches the type, client address and requested
77 database name of a connection attempt is used to do the
78 authentication step. There is no <quote>fall-through</> or
79 <quote>backup</>: if one record is chosen and the authentication
80 fails, the following records are not considered. If no record
81 matches, the access will be denied.
85 A record may have one of the three formats
87 local <replaceable>database</replaceable> <replaceable>authentication-method</replaceable> [ <replaceable>authentication-option</replaceable> ]
88 host <replaceable>database</replaceable> <replaceable>IP-address</replaceable> <replaceable>IP-mask</replaceable> <replaceable>authentication-method</replaceable> [ <replaceable>authentication-option</replaceable> ]
89 hostssl <replaceable>database</replaceable> <replaceable>IP-address</replaceable> <replaceable>IP-mask</replaceable> <replaceable>authentication-method</replaceable> [ <replaceable>authentication-option</replaceable> ]
91 The meaning of the fields is as follows:
95 <term><literal>local</literal></term>
98 This record pertains to connection attempts over Unix domain
105 <term><literal>host</literal></term>
108 This record pertains to connection attempts over TCP/IP
109 networks. Note that TCP/IP connections are completely disabled
110 unless the server is started with the <option>-i</option> switch or
111 the equivalent configuration parameter is set.
117 <term><literal>hostssl</literal></term>
120 This record pertains to connection attempts with SSL over
121 TCP/IP. To make use of this option the server must be
122 built with SSL support enabled. Furthermore, SSL must be
123 enabled with the <option>-l</> option or equivalent configuration
124 setting when the server is started. (Note: <literal>host</literal>
125 records will match either SSL or non-SSL connection attempts, but
126 <literal>hostssl</literal> records match only SSL connections.)
132 <term><replaceable>database</replaceable></term>
135 Specifies the database that this record applies to. The value
136 <literal>all</literal> specifies that it applies to all
137 databases, while the value <literal>sameuser</> identifies the
138 database with the same name as the connecting user. Otherwise,
139 this is the name of a specific <productname>PostgreSQL</productname>
146 <term><replaceable>IP address</replaceable></term>
147 <term><replaceable>IP mask</replaceable></term>
150 These two fields specify to which client machines a
151 <literal>host</literal> or <literal>hostssl</literal>
152 record applies, based on their IP
153 address. (Of course IP addresses can be spoofed but this
154 consideration is beyond the scope of
155 <productname>PostgreSQL</productname>.) The precise logic is that
158 <programlisting>(<replaceable>actual-IP-address</replaceable> xor <replaceable>IP-address-field</replaceable>) and <replaceable>IP-mask-field</replaceable></programlisting>
161 must be zero for the record to match.
167 <term><replaceable>authentication method</replaceable></term>
170 Specifies the method that users must use to authenticate themselves
171 when connecting under the control of this authentication record.
172 The possible choices are summarized here,
173 details are in <xref linkend="auth-methods">.
177 <term><literal>trust</></term>
180 The connection is allowed unconditionally. This method allows
181 any user that has login access to the client host to connect as
182 any <productname>PostgreSQL</productname> user whatsoever.
188 <term><literal>reject</></term>
191 The connection is rejected unconditionally. This is mostly
192 useful to <quote>filter out</> certain hosts from a group.
198 <term><literal>password</></term>
201 The client is required to supply a password which is required to
202 match the database password that was set up for the user.
206 An optional file name may be specified after the
207 <literal>password</literal> keyword. This file is expected to
208 contain a list of users who may connect using this record,
209 and optionally alternative passwords for them.
213 The password is sent over the wire in clear text. For better
214 protection, use the <literal>md5</literal> or
215 <literal>crypt</literal> methods.
221 <term><literal>md5</></term>
224 Like the <literal>password</literal> method, but the password
225 is sent over the wire encrypted using a simple
226 challenge-response protocol. This protects against incidental
227 wire-sniffing. This is now the recommended choice for
228 password-based authentication.
232 The name of a file may follow the
233 <literal>md5</literal> keyword. It contains a list of users
234 who may connect using this record.
240 <term><literal>crypt</></term>
243 Like the <literal>md5</literal> method but uses older crypt
244 encryption, which is needed for pre-7.2
245 clients. <literal>md5</literal> is
246 preferred for 7.2 and later clients. The <literal>crypt</>
247 method is not compatible with encrypting passwords in
248 <filename>pg_shadow</>, and may fail if client and server
249 machines have different implementations of the crypt() library
256 <term><literal>krb4</></term>
259 Kerberos V4 is used to authenticate the user. This is only
260 available for TCP/IP connections.
266 <term><literal>krb5</></term>
269 Kerberos V5 is used to authenticate the user. This is only
270 available for TCP/IP connections.
276 <term><literal>ident</></term>
279 The identity of the user as determined on login to the
280 operating system is used by <productname>PostgreSQL</productname>
281 to determine whether the user
282 is allowed to connect as the requested database user.
283 For TCP/IP connections the user's identity is determined by
284 contacting the <firstterm>ident</firstterm> server on the client
285 host. (Note that this is only as reliable as the remote ident
286 server; ident authentication should never be used for remote hosts
287 whose administrators are not trustworthy.)
289 supporting <symbol>SO_PEERCRED</> requests for Unix domain sockets,
290 ident authentication is possible for local connections;
291 the system is then asked for the connecting user's identity.
294 On systems without <symbol>SO_PEERCRED</> requests, ident authentication
295 is only available for TCP/IP connections. As a workaround,
297 specify the <systemitem class="systemname">localhost</> address
298 <systemitem class="systemname">127.0.0.1</> and make connections
302 The <replaceable>authentication option</replaceable> following
303 the <literal>ident</> keyword specifies the name of an
304 <firstterm>ident map</firstterm> that specifies which operating
305 system users equate with which database users. See below for
312 <term><literal>pam</></term>
315 This authentication type operates similarly to
316 <firstterm>password</firstterm>, with the main difference that
317 it will use PAM (Pluggable Authentication Modules) as the
318 authentication mechanism. The <replaceable>authentication
319 option</replaceable> following the <literal>pam</> keyword
320 specifies the service name that will be passed to PAM. The
321 default service name is <literal>postgresql</literal>.
322 For more information about PAM, please read the <ulink
323 url="http://www.kernel.org/pub/linux/libs/pam/"><productname>Linux-PAM</productname>
324 Page</ulink> and/or the <ulink
325 url="http://www.sun.com/software/solaris/pam/"><systemitem class="osname">Solaris</> PAM
338 <term><replaceable>authentication option</replaceable></term>
341 This field is interpreted differently depending on the
342 authentication method, as described above.
350 Since the <filename>pg_hba.conf</filename> records are examined
351 sequentially for each connection attempt, order of the records is
352 very significant. Typically, earlier records will have tight
353 connection match parameters and weaker authentication methods,
354 while later records will have looser match parameters and stronger
355 authentication methods. For example, one might wish to use
356 <literal>trust</> authentication for local TCP connections but
357 require a password for remote TCP connections. In this case a
358 record specifying <literal>trust</> authentication for connections
359 from 127.0.0.1 would appear before a record specifying password
360 authentication for a wider range of allowed client IP addresses.
365 <primary>SIGHUP</primary>
367 The <filename>pg_hba.conf</filename> file is read on startup
368 and when the <application>postmaster</> receives a
369 <systemitem>SIGHUP</systemitem> signal. If you edit the file on an
370 active system, you will need to signal the <application>postmaster</>
371 (using <application>pg_ctl reload</> or <application>kill -HUP</>)
372 to make it re-read the file.
376 An example of a <filename>pg_hba.conf</filename> file is shown in
377 <xref linkend="example-pg-hba.conf">. See below for details on the
378 different authentication methods.
380 <example id="example-pg-hba.conf">
381 <title>An example <filename>pg_hba.conf</filename> file</title>
383 # TYPE DATABASE IP_ADDRESS MASK AUTHTYPE MAP
385 # Allow any user on the local system to connect to any
386 # database under any username, but only via an IP connection:
388 host all 127.0.0.1 255.255.255.255 trust
390 # The same, over Unix-socket connections:
394 # Allow any user from any host with IP address 192.168.93.x to
395 # connect to database "template1" as the same username that ident on that
396 # host identifies him as (typically his Unix username):
398 host template1 192.168.93.0 255.255.255.0 ident sameuser
400 # Allow a user from host 192.168.12.10 to connect to database "template1"
401 # if the user's password in pg_shadow is correctly supplied:
403 host template1 192.168.12.10 255.255.255.255 md5
405 # In the absence of preceding "host" lines, these two lines will reject
406 # all connection attempts from 192.168.54.1 (since that entry will be
407 # matched first), but allow Kerberos V5-validated connections from anywhere
408 # else on the Internet. The zero mask means that no bits of the host IP
409 # address are considered, so it matches any host:
411 host all 192.168.54.1 255.255.255.255 reject
412 host all 0.0.0.0 0.0.0.0 krb5
414 # Allow users from 192.168.x.x hosts to connect to any database, if they
415 # pass the ident check. If, for example, ident says the user is "bryanh"
416 # and he requests to connect as <productname>PostgreSQL</> user "guest1", the connection
417 # is allowed if there is an entry in pg_ident.conf for map "omicron" that
418 # says "bryanh" is allowed to connect as "guest1":
420 host all 192.168.0.0 255.255.0.0 ident omicron
422 # If these are the only two lines for local connections, they will allow
423 # local users to connect only to their own databases (database named the
424 # same as the user name), except for administrators who may connect to
425 # all databases. The file $PGDATA/admins lists the user names who are
426 # permitted to connect to all databases. Passwords are required in all
427 # cases. (If you prefer to use ident authorization, an ident map can
428 # serve a parallel purpose to the password list file used here.)
437 <sect1 id="auth-methods">
438 <title>Authentication methods</title>
440 The following describes the authentication methods in detail.
443 <sect2 id="auth-password">
444 <title>Password authentication</title>
447 <primary>password</primary>
454 <productname>PostgreSQL</productname> database passwords are separate from
455 operating system user passwords. Ordinarily, the password for each
456 database user is stored in the pg_shadow system catalog table.
457 Passwords can be managed with the query language commands
458 <command>CREATE USER</command> and <command>ALTER USER</command>,
459 e.g., <userinput>CREATE USER foo WITH PASSWORD
460 'secret';</userinput>. By default, that is, if no password has
461 been set up, the stored password is <literal>NULL</literal>
462 and password authentication will always fail for that user.
466 To restrict the set of users that are allowed to connect to certain
467 databases, list the set of users in a separate file (one user name
468 per line) in the same directory that <filename>pg_hba.conf</> is in,
469 and mention the (base) name of the file after the
470 <literal>password</>, <literal>md5</>, or <literal>crypt</> keyword,
471 respectively, in <filename>pg_hba.conf</>. If you do not use this
472 feature, then any user that is known to the database system can
473 connect to any database (so long as he supplies the correct password,
478 These files can also be used to apply a different set of passwords
479 to a particular database or set thereof. In that case, the files
480 have a format similar to the standard Unix password file
481 <filename>/etc/passwd</filename>, that is,
483 <replaceable>username</replaceable>:<replaceable>password</replaceable>
485 Any extra colon separated fields following the password are
486 ignored. The password is expected to be encrypted using the
487 system's <function>crypt()</function> function. The utility
488 program <application>pg_passwd</application> that is installed
489 with <productname>PostgreSQL</productname> can be used to manage
490 these password files.
494 Lines with and without passwords can be mixed in secondary
495 password files. Lines without password indicate use of the main
496 password in <literal>pg_shadow</> that is managed by
497 <command>CREATE USER</> and <command>ALTER USER</>. Lines with
498 passwords will cause that password to be used. A password entry of
499 <quote>+</quote> also means using the pg_shadow password.
503 Alternative passwords cannot be used when using the <literal>md5</>
504 or <literal>crypt</> methods. The file will be read as
505 usual, but the password field will simply be ignored and the
506 <literal>pg_shadow</> password will always be used.
510 Note that using alternative passwords like this means that one can
511 no longer use <command>ALTER USER</command> to change one's
512 password. It will appear to work but the password one is
513 changing is not the password that the system will end up
519 <sect2 id="kerberos-auth">
520 <title>Kerberos authentication</title>
522 <indexterm zone="kerberos-auth">
523 <primary>Kerberos</primary>
527 <productname>Kerberos</productname> is an industry-standard secure
528 authentication system suitable for distributed computing over a
529 public network. A description of the
530 <productname>Kerberos</productname> system is far beyond the scope
531 of this document; in all generality it can be quite complex (yet
532 powerful). The <ulink
533 url="http://www.nrl.navy.mil/CCS/people/kenh/kerberos-faq.html">Kerberos
534 <acronym>FAQ</></ulink> or <ulink
535 url="ftp://athena-dist.mit.edu">MIT Project Athena</ulink> can be
536 a good starting point for exploration. Several sources for
537 <productname>Kerberos</> distributions exist.
541 In order to use <productname>Kerberos</>, support for it must be
542 enabled at build time. Both Kerberos 4 and 5 are supported
543 (<literal>./configure --with-krb4</> or <literal>./configure
544 --with-krb5</> respectively), although only one version can be
545 supported in any one build.
549 <productname>PostgreSQL</> operates like a normal Kerberos service.
550 The name of the service principal is
551 <replaceable>servicename/hostname@realm</>, where
552 <replaceable>servicename</> is <literal>postgres</literal>
553 (unless a different service name was selected at configure time
554 with <literal>./configure --with-krb-srvnam=whatever</>).
555 <replaceable>hostname</> is the fully qualified domain name of the server
556 machine. The service principal's realm is the preferred realm of the
561 Client principals must have their <productname>PostgreSQL</> username as
562 their first component, for example
563 <replaceable>pgusername/otherstuff@realm</>.
564 At present the realm of the client is not checked by
565 <productname>PostgreSQL</>; so
566 if you have cross-realm authentication enabled, then any principal
567 in any realm that can communicate with yours will be accepted.
571 Make sure that your server key file is readable (and
572 preferably only readable) by the
573 <productname>PostgreSQL</productname> server account (see
574 <xref linkend="postgres-user">). The location of the key file
575 is specified with the <varname>krb_server_keyfile</> run time
576 configuration parameter. (See also <xref linkend="runtime-config">.)
577 The default is <filename>/etc/srvtab</> if you are using Kerberos 4
578 and <filename>FILE:/usr/local/pgsql/etc/krb5.keytab</> (or whichever
579 directory was specified as <varname>sysconfdir</> at build time)
584 To generate the keytab file, use for example (with version 5)
586 <prompt>kadmin% </><userinput>ank -randkey postgres/server.my.domain.org</>
587 <prompt>kadmin% </><userinput>ktadd -k krb5.keytab postgres/server.my.domain.org</>
589 Read the <productname>Kerberos</> documentation for details.
593 When connecting to the database make sure you have a ticket for a
594 principal matching the requested database username.
595 An example: For database username <literal>fred</>, both principal
596 <literal>fred@EXAMPLE.COM</> and
597 <literal>fred/users.example.com@EXAMPLE.COM</> can be
598 used to authenticate to the database server.
602 If you use <application>mod_auth_krb</application> and <application>mod_perl</application> on your <productname>Apache</productname> web server,
603 you can use <literal>AuthType KerberosV5SaveCredentials</literal> with a <application>mod_perl</application>
604 script. This gives secure database access over the web, no extra
611 <title>Ident-based authentication</title>
614 <primary>ident</primary>
618 The <quote>Identification Protocol</quote> is described in
619 <citetitle>RFC 1413</citetitle>. Virtually every Unix-like
620 operating system ships with an ident server that listens on TCP
621 port 113 by default. The basic functionality of an ident server
622 is to answer questions like <quote>What user initiated the
623 connection that goes out of your port <replaceable>X</replaceable>
624 and connects to my port <replaceable>Y</replaceable>?</quote>.
625 Since <productname>PostgreSQL</> knows both <replaceable>X</> and
626 <replaceable>Y</> when a physical connection is established, it
627 can interrogate the ident server on the host of the connecting
628 client and could theoretically determine the operating system user
629 for any given connection this way.
633 The drawback of this procedure is that it depends on the integrity
634 of the client: if the client machine is untrusted or compromised
635 an attacker could run just about any program on port 113 and
636 return any user name he chooses. This authentication method is
637 therefore only appropriate for closed networks where each client
638 machine is under tight control and where the database and system
639 administrators operate in close contact. In other words, you must
640 trust the machine running the ident server.
643 <attribution>RFC 1413</attribution>
645 The Identification Protocol is not intended as an authorization
646 or access control protocol.
652 On systems supporting <symbol>SO_PEERCRED</symbol> requests for Unix-domain sockets,
653 ident authentication can also be applied to local connections. In this
654 case, no security risk is added by using ident authentication; indeed
655 it is a preferable choice for such a system.
659 When using ident-based authentication, after having determined the
660 name of the operating system user that initiated the connection,
661 <productname>PostgreSQL</productname> checks whether that user is allowed
662 to connect as the database user he is requesting to connect as.
663 This is controlled by the ident map
664 argument that follows the <literal>ident</> keyword in the
665 <filename>pg_hba.conf</filename> file. There is a predefined ident map
666 <literal>sameuser</literal>, which allows any operating system
667 user to connect as the database user of the same name (if the
668 latter exists). Other maps must be created manually.
672 <indexterm><primary>pg_ident.conf</primary></indexterm>
673 Ident maps other than <literal>sameuser</literal> are defined
674 in the file <filename>pg_ident.conf</filename>
675 in the data directory, which contains lines of the general form:
677 <replaceable>map-name</> <replaceable>ident-username</> <replaceable>database-username</>
679 Comments and whitespace are handled in the usual way.
680 The <replaceable>map-name</> is an arbitrary name that will be
681 used to refer to this mapping in <filename>pg_hba.conf</filename>.
682 The other two fields specify which operating system user is
683 allowed to connect as which database user. The same
684 <replaceable>map-name</> can be used repeatedly to specify more
685 user-mappings within a single map. There is no restriction regarding
687 database users a given operating system user may correspond to and vice
693 <primary>SIGHUP</primary>
695 The <filename>pg_ident.conf</filename> file is read on startup
696 and when the <application>postmaster</> receives a
697 <systemitem>SIGHUP</systemitem> signal. If you edit the file on an
698 active system, you will need to signal the <application>postmaster</>
699 (using <application>pg_ctl reload</> or <application>kill -HUP</>)
700 to make it re-read the file.
704 A <filename>pg_ident.conf</filename> file that could be used in
705 conjunction with the <filename>pg_hba.conf</> file in <xref
706 linkend="example-pg-hba.conf"> is shown in <xref
707 linkend="example-pg-ident.conf">. In this example setup, anyone
708 logged in to a machine on the 192.168 network that does not have
709 the Unix user name <systemitem>bryanh</>, <systemitem>ann</>, or <systemitem>robert</> would not be granted access.
710 Unix user <systemitem>robert</> would only be allowed access when he tries to
711 connect as <productname>PostgreSQL</> user <systemitem>bob</>,
712 not as <systemitem>robert</>
713 or anyone else. <systemitem>ann</> would only be allowed to connect as
714 <systemitem>ann</>. User <systemitem>bryanh</> would be allowed to connect as either
715 <systemitem>bryanh</> himself or as <systemitem>guest1</>.
718 <example id="example-pg-ident.conf">
719 <title>An example <filename>pg_ident.conf</> file</title>
721 #MAP IDENT-NAME POSTGRESQL-NAME
723 omicron bryanh bryanh
725 # bob has username robert on these machines
727 # bryanh can also connect as guest1
728 omicron bryanh guest1
734 <sect1 id="client-authentication-problems">
735 <title>Authentication problems</title>
738 Genuine authentication failures and related problems generally
739 manifest themselves through error messages like the following.
744 No pg_hba.conf entry for host 123.123.123.123, user joeblow, database testdb
746 This is what you are most likely to get if you succeed in
747 contacting the server, but it doesn't want to talk to you. As the
748 message suggests, the server refused the connection request
749 because it found no authorizing entry in its <filename>pg_hba.conf</filename>
755 Password authentication failed for user 'joeblow'
757 Messages like this indicate that you contacted the server, and
758 it's willing to talk to you, but not until you pass the
759 authorization method specified in the
760 <filename>pg_hba.conf</filename> file. Check the password you're
761 providing, or check your Kerberos or IDENT software if the
762 complaint mentions one of those authentication types.
767 FATAL 1: user "joeblow" does not exist
769 The indicated user name was not found in pg_shadow.
774 FATAL 1: Database "testdb" does not exist in the system catalog.
776 The database you're trying to connect to doesn't exist. Note that
777 if you don't specify a database name, it defaults to the database
778 user name, which may or may not be the right thing.
782 Note that the server log may contain more information
783 about an authentication failure than is reported to the client.
784 If you are confused about the reason for a failure, check the log.