<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title> Practical PostgreSQL</title>
<meta name="keywords" content="postgresql, support, recovery, design, software, hosting, expterise, programming, postgres, pgsql, replication, mysql, firebird, database, sql, free, open source, software" />
<meta name="description" content="Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. " />
<link rel="shortcut icon" href="../favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="../css/layout.css" />
<link rel="stylesheet" type="text/css" href="../css/cmd4.css" />
<script language="javascript" type="text/javascript"><!--
greymenuhomewideLit = new Image();
greymenuhomewideLit.src = "/images/greymenu_homewide_lit.gif";
greymenuaboutLit = new Image();
greymenuaboutLit.src = "/images/greymenu_about_lit.gif";
greymenuproductsLit = new Image();
greymenuproductsLit.src = "/images/greymenu_products_lit.gif";
greymenuservicesLit = new Image();
greymenuservicesLit.src = "/images/greymenu_services_lit.gif";
greymenusupportLit = new Image();
greymenusupportLit.src = "/images/greymenu_support_lit.gif";
greymenucommunityLit = new Image();
greymenucommunityLit.src = "/images/greymenu_community_lit.gif";
//--></script>
</head>
<body>
<div align="center">
<div id="widecontainer">
<div id="widetop">
<div class="throwleft" style="width: 300px; text-align: left;"><a href="http://www.commandprompt.com/"><img src="../images/cmd4logo_wide.gif" border="0" alt="Command Prompt, Inc." /></a></div>
<div class="throwright" style="width: 400px; text-align: right;">
<div id="topright">
<form action="index.html" name="cmdform" method="post">
<ul>
<li><input type="text" name="search" value="" /></li>
<li><input type="submit" name="action" value="Search Book" /></li>
</ul>
</form>
</div>
</div>
</div>
<ul id="greymenu_wide">
<li><a href="http://www.commandprompt.com/home/"><img onmouseover="this.src = greymenuhomewideLit.src;" onmouseout="this.src = '/images/greymenu_homewide.gif';" src="../images/greymenu_homewide.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/about/"><img onmouseover="this.src = greymenuaboutLit.src;" onmouseout="this.src = '/images/greymenu_about.gif';" src="../images/greymenu_about.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/products/"><img onmouseover="this.src = greymenuproductsLit.src;" onmouseout="this.src = '/images/greymenu_products.gif';" src="../images/greymenu_products.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/services/"><img onmouseover="this.src = greymenuservicesLit.src;" onmouseout="this.src = '/images/greymenu_services.gif';" src="../images/greymenu_services.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/support/"><img onmouseover="this.src = greymenusupportLit.src;" onmouseout="this.src = '/images/greymenu_support.gif';" src="../images/greymenu_support.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/community/"><img onmouseover="this.src = greymenucommunityLit.src;" onmouseout="this.src = '/images/greymenu_community.gif';" src="../images/greymenu_community.gif" border="0" height="22" title="" alt="" /></a></li> </ul>
<div style="padding-left: 16px; width: 96%;">
<HTML
><HEAD
><TITLE
>Authentication and Encryption</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.72
"><LINK
REL="HOME"
TITLE="Practical PostgreSQL"
href="book1"><LINK
REL="UP"
TITLE="Administrating PostgreSQL"
href="p15677"><LINK
REL="PREVIOUS"
TITLE="Administrating PostgreSQL"
href="p15677"><LINK
REL="NEXT"
TITLE="Encrypting sessions"
href="x16230"></HEAD
><BODY
CLASS="CHAPTER"
BGCOLOR="#FFFFFF"
TEXT="#000000"
LINK="#0000FF"
VLINK="#840084"
ALINK="#0000FF"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>Practical PostgreSQL</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
href="p15677"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
href="x16230"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="CHAPTER"
><H1
><A
NAME="AUTHENTICATIONANDENCRYPTION"
>Chapter 8. Authentication and Encryption</A
></H1
><DIV
CLASS="TOC"
><DL
><DT
><B
>Table of Contents</B
></DT
><DT
><A
href="c15679#CLIENTAUTHENTICATION"
>Client Authentication</A
></DT
><DT
><A
href="x16230"
>Encrypting sessions</A
></DT
></DL
></DIV
><P
>This chapter documents the fundamental concepts involved with authenticating and encrypting a client session to the
PostgreSQL server. This includes how to correctly configure the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> file for a variety of
authentication schemes, as well as a few common ways to encrypt your client connections.</P
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="CLIENTAUTHENTICATION"
>Client Authentication</A
></H1
><P
>Client authentication is a central feature to PostgreSQL. Without it, you would either have to sacrifice remote
connectivity, or blindly allow anyone to connect to your database and retrieve, or even modify your data. PostgreSQL has
several different types of client authentication at its disposal. As the site administrator, you need to decide which
one is best for your system.</P
><P
>As of PostgreSQL 7.1.x, host-based client access is specified in the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> file. The rights
and restrictions described in this file should not be confused with a PostgreSQL user's rights to objects within the
database. The <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> file allows you to set the type of <SPAN
><I
CLASS="EMPHASIS"
>host-based</I
></SPAN
>
authentication to be used. This authentication is performed before PostgreSQL establishes a connection to the intended
database, where user rights would be relevant.</P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>The <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> is located in the PostgreSQL data directory
(e.g., <SPAN
><I
CLASS="EMPHASIS"
>/usr/local/pgsql/data</I
></SPAN
> ), and is installed automatically upon the execution of the
<SPAN
><I
CLASS="EMPHASIS"
>initdb</I
></SPAN
> command when PostgreSQL is installed.</P
></BLOCKQUOTE
></DIV
><P
>PostgreSQL's host-based authentication is flexible, featuring a wide variety of configurable options. You may restrict
database access to specific hosts, as well as allow access to a range of IP addresses by using netmasks. Each configured host
has its own <SPAN
><I
CLASS="EMPHASIS"
>host record</I
></SPAN
>, which is a single line in the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> file.</P
><P
>With these host records, you may specify access either to a particular database or all databases. Furthermore, you
may require a user from a specified host to authenticate via the PostgreSQL users table after qualifying for a
connection.</P
><P
>Put simply, the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> file allows you to determine <SPAN
><I
CLASS="EMPHASIS"
>who</I
></SPAN
> is allowed to
connect to <SPAN
><I
CLASS="EMPHASIS"
>which</I
></SPAN
> databases from <SPAN
><I
CLASS="EMPHASIS"
>what</I
></SPAN
> machines, and to <SPAN
><I
CLASS="EMPHASIS"
>what degree</I
></SPAN
>
they must prove their authenticity to gain access.</P
><DIV
CLASS="WARNING"
><P
></P
><TABLE
CLASS="WARNING"
BORDER="1"
WIDTH="100%"
><TR
><TD
ALIGN="CENTER"
><B
>Warning</B
></TD
></TR
><TR
><TD
ALIGN="LEFT"
><P
>Through remote password-based authentication, passwords may be transmitted in clear text depending on whether or not
you are using encrypted sessions. Be sure that you understand how your application is communicating with PostgreSQL before
allowing users to remotely connect to a PostgreSQL database.</P
></TD
></TR
></TABLE
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN15710"
>Password Authentication</A
></H2
><P
>Passwords allow PostgreSQL users a way to identify themselves and prevent unauthorized individuals from
connecting with a user that is not theirs. As of PostgreSQL 7.1.x, user passwords are
stored in plain text in the <TT
CLASS="LITERAL"
>pg_shadow</TT
> system table. The structure of this table is
illustrated in <A
href="c15679#THEPGSHADOWTABLE"
>Table 8-1</A
>. Note that while the passwords are stored as plain text, only PostgreSQL
<SPAN
><I
CLASS="EMPHASIS"
>superusers</I
></SPAN
> are allowed to view the <TT
CLASS="LITERAL"
>pg_shadow</TT
> table.</P
><DIV
CLASS="TABLE"
><A
NAME="THEPGSHADOWTABLE"
></A
><P
><B
>Table 8-1. The pg_shadow table</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><THEAD
><TR
><TH
WIDTH="6"
ALIGN="LEFT"
VALIGN="TOP"
><P
>Column</P
></TH
><TH
WIDTH="6"
ALIGN="LEFT"
VALIGN="TOP"
><P
>Type</P
></TH
></TR
></THEAD
><TBODY
><TR
><TD
WIDTH="6"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>usename</TT
></P
></TD
><TD
WIDTH="6"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>name</TT
></P
></TD
></TR
><TR
><TD
WIDTH="6"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>usesysid</TT
></P
></TD
><TD
WIDTH="6"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>integer</TT
></P
></TD
></TR
><TR
><TD
WIDTH="6"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>usecreatedb</TT
></P
></TD
><TD
WIDTH="6"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>boolean</TT
></P
></TD
></TR
><TR
><TD
WIDTH="6"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>usetrace</TT
></P
></TD
><TD
WIDTH="6"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>boolean</TT
></P
></TD
></TR
><TR
><TD
WIDTH="6"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>usesuper</TT
></P
></TD
><TD
WIDTH="6"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>boolean</TT
></P
></TD
></TR
><TR
><TD
WIDTH="6"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>usecatupd</TT
></P
></TD
><TD
WIDTH="6"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>boolean</TT
></P
></TD
></TR
><TR
><TD
WIDTH="6"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>passwd</TT
></P
></TD
><TD
WIDTH="6"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>text</TT
></P
></TD
></TR
><TR
><TD
WIDTH="6"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>valuntil</TT
></P
></TD
><TD
WIDTH="6"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>abstime</TT
></P
></TD
></TR
></TBODY
></TABLE
></DIV
><P
>The <TT
CLASS="LITERAL"
>pg_shadow</TT
> table is a system table, and thus is accessible from any database. It
follows, therefore, that users are not assigned to a specific database. If a user exists in the
<TT
CLASS="LITERAL"
>pg_shadow</TT
> table, that user will be able to connect to any database on the server machine,
though not necessarily from any remote machine (depending on your configuration).</P
><P
>Users typically set passwords in PostgreSQL when the user is created (with the
<TT
CLASS="LITERAL"
>CREATE USER</TT
> command) or after the user has been created (using the
<TT
CLASS="LITERAL"
>ALTER USER</TT
> command). Alternatively, you may manually modify a user's password by using an
<TT
CLASS="LITERAL"
>UPDATE</TT
> statement. (For a more detailed explanation about defining passwords for users, see
<A
href="c18591"
>Chapter 10</A
>.)</P
><P
>If a password is not set, a user's password defaults to <TT
CLASS="LITERAL"
>NULL</TT
>. If
password-based authentication is enabled in the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> file, connection attempts will always fail
for such a user. Conversely, if the host that establishes the connection is a <SPAN
><I
CLASS="EMPHASIS"
>trusted</I
></SPAN
> host (such as
<SPAN
><I
CLASS="EMPHASIS"
>localhost</I
></SPAN
>, by default), <SPAN
><I
CLASS="EMPHASIS"
>anyone</I
></SPAN
> from the trusted host may connect as a user with a
<TT
CLASS="LITERAL"
>NULL</TT
> password. In fact, passwords are ignored entirely for trusted hosts.</P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>The <TT
CLASS="LITERAL"
>GRANT</TT
> command allows you to restrict or allow a variety of access types to
tables within a database. See <A
href="c18591"
>Chapter 10</A
> for more on this topic.</P
></BLOCKQUOTE
></DIV
><P
>Unless your needs for security are very minimal, you will not want to rely on password-only authentication with your PostgreSQL server.
Using a password-only method to authenticate users will allow any verified user access to any database on the system, and authenticating with
a password over clear text can result in unauthorized individuals acquiring user passwords. If you are likely to have your database connected to
the Internet in some fashion, we strongly suggest that you read the following sections. These cover the use of the
<SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> file and session encryption. </P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN15817"
>The pg_hba.conf file</A
></H2
><P
>We mentioned earlier in this section that the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> file enables client authentication between the PostgreSQL server and the client application. This file consists of a series of <SPAN
><I
CLASS="EMPHASIS"
>entries</I
></SPAN
>,
which define a host and its associated permissions (e.g., the database it is allowed to connect to, the authentication method
to use, and so on).</P
><P
>When an application requests a connection, the request will specify a PostgreSQL username and database with which it
intends to connect to PostgreSQL. Optionally, a password may be provided, depending on the expected configuration for the
connecting host.</P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>PostgreSQL has its own user and password tables, which are separate from system accounts. It is not
required that your PostgreSQL users match users available to the operating system.</P
></BLOCKQUOTE
></DIV
><P
>When PostgreSQL receives a connection request it will check the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> file to verify
that the machine from which the application is requesting a connection has rights to connect to the specified database. If
the machine requesting access has permission to connect, PostgreSQL will check the conditions that the application must
meet in order to successfully authenticate. This affects connections that are initiated locally as well as remotely.</P
><P
>PostgreSQL will check the authentication method via the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> for every connection
request. This check is performed every time a new connection is requested from the PostgreSQL server, so there is no need
to re-start PostgreSQL after you add, modify or remove an entry in the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> file. <A
href="c15679#ASIMPLEPGHBACONFFILE"
>Example 8-1</A
> is a simple example of the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> file.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="ASIMPLEPGHBACONFFILE"
></A
><P
><B
>Example 8-1. A simple pg_hba.conf file</B
></P
><PRE
CLASS="SCREEN"
># PostgreSQL HOST ACCESS CONTROL FILE
#
local all trust
host all 127.0.0.1 255.255.255.255 trust
host booktown 192.168.1.3 255.255.255.255 ident sales
host all 192.168.1.4 255.255.255.255 ident audit</PRE
></DIV
><P
>When a connection is initialized, PostgreSQL will read through the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> one entry at a
time, from the top down. As soon a matching record is found, PostgreSQL will stop searching and allow or reject the
connection, based on the found entry. If PostgreSQL does not find a matching entry in the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
>
file, the connection fails completely.</P
><P
>Table-level permissions still apply to a database, even if a user has permissions to connect to the database. If
you can connect, but cannot select data from a table, you may want to verify that your connected user has permission to use
<TT
CLASS="LITERAL"
>SELECT</TT
> on that table. Using the <SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
> command-line application, you
can check the permissions of the tables within a database by using the <TT
CLASS="LITERAL"
>\z</TT
> slash command.
From any other interface to PostgreSQL, use the query demonstrated in <A
href="c15679#CHECKINGUSERPERMISSIONS"
>Example 8-2</A
> to
see the same information provided by the <TT
CLASS="LITERAL"
>\z</TT
> slash command.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="CHECKINGUSERPERMISSIONS"
></A
><P
><B
>Example 8-2. Checking user permissions</B
></P
><PRE
CLASS="SCREEN"
>testdb=# <TT
CLASS="USERINPUT"
><B
>SELECT relname as "Relation", relacl as "Access permissions"</B
></TT
>
testdb-# <TT
CLASS="USERINPUT"
><B
> FROM pg_class</B
></TT
>
testdb-# <TT
CLASS="USERINPUT"
><B
> WHERE relkind IN ('r', 'v', 'S')</B
></TT
>
testdb-# <TT
CLASS="USERINPUT"
><B
> AND relname !~ '^pg_'</B
></TT
>
testdb-# <TT
CLASS="USERINPUT"
><B
> ORDER BY relname;</B
></TT
>
Relation | Access permissions
----------+----------------------------------
foo | {"=arwR","jdrake=arwR"}
my_list | {"=","jdrake=arwR","jworsley=r"}
(2 rows)</PRE
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="AEN15856"
>Structure of the pg_hba.conf file</A
></H3
><P
>The <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> file contains sequential entries that define the settings PostgreSQL should
use during the client authentication process for a specified host. This file is designed to be easily customizable to
your system needs.</P
><P
>Within this file, you may associate a TCP/IP host address (or a range of addresses) with a particular database (or
<SPAN
><I
CLASS="EMPHASIS"
>all</I
></SPAN
> databases), and one of several available authentication methods. You may also specify access for
local connections using the term <TT
CLASS="LITERAL"
>localhost</TT
>, or
<TT
CLASS="LITERAL"
>127.0.0.1</TT
>, rather than using the system's external IP address. Several syntax rules
apply to the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
>.</P
><P
>First, you may only place one host record per line in the file. Subsequently, host records are not allowed to wrap
across multiple lines. Second, each host record must contain multiple fields, which must be separated by either tabs or
spaces. The number of fields in a host record is directly related to the type of host entry being defined. <A
href="c15679#AVALIDENTRYWITHSPACESANDTABS"
>Example 8-3</A
> shows two host records, the first with the fields separated by spaces, and the
second with the file separated by tabs.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="AVALIDENTRYWITHSPACESANDTABS"
></A
><P
><B
>Example 8-3. A valid pg_hba.conf entry with spaces and tabs</B
></P
><PRE
CLASS="SCREEN"
>host all 127.0.0.1 255.255.255.255 trust
host all 127.0.0.1 255.255.255.255 trust</PRE
></DIV
><P
>Commenting is allowed within the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> by placing a hash mark
(<TT
CLASS="LITERAL"
>#</TT
>) at the beginning of each line being commented. <A
href="c15679#AVALIDCOMMENT"
>Example 8-4</A
>
demonstrates valid commented lines.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="AVALIDCOMMENT"
></A
><P
><B
>Example 8-4. Valid pg_hba.conf comments</B
></P
><PRE
CLASS="SCREEN"
># Book Town host entries
#
#
host all 127.0.0.1 255.255.255.255 trust</PRE
></DIV
><P
>Regarding the actual form of each host record, there are three general <SPAN
><I
CLASS="EMPHASIS"
>types</I
></SPAN
>
available in the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> (the <SPAN
><I
CLASS="EMPHASIS"
>type</I
></SPAN
> keyword is always the
first word in the host record):</P
><P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="LITERAL"
>host</TT
></DT
><DD
><P
>A <TT
CLASS="LITERAL"
>host</TT
> entry is used to specify remote hosts that are allowed to connect to
the PostgreSQL server. PostgreSQL's <SPAN
><I
CLASS="EMPHASIS"
>postmaster</I
></SPAN
> backend must be running with the
<SPAN
><I
CLASS="EMPHASIS"
>-i</I
></SPAN
> option (TCP/IP) in order for a <TT
CLASS="LITERAL"
>host</TT
> entry to work
correctly.</P
></DD
><DT
><TT
CLASS="LITERAL"
>local</TT
></DT
><DD
><P
>A <TT
CLASS="LITERAL"
>local</TT
> entry is semantically the same as a
<TT
CLASS="LITERAL"
>host</TT
> entry. However, you do not need to specify a host that is allowed to connect. The local
entry is used for client connections that are initiated from the same machine that the PostgreSQL server is operating on.</P
></DD
><DT
><TT
CLASS="LITERAL"
>hostssl</TT
></DT
><DD
><P
>A <TT
CLASS="LITERAL"
>hostssl</TT
> 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 <SPAN
><I
CLASS="EMPHASIS"
>postmaster</I
></SPAN
> backend must be running with the
<SPAN
><I
CLASS="EMPHASIS"
>-l</I
></SPAN
> (SSL) and <SPAN
><I
CLASS="EMPHASIS"
>-i</I
></SPAN
> (TCP/IP) options.</P
></DD
></DL
></DIV
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>See <A
href="c16573"
>Chapter 9</A
> for more on how to start the <SPAN
><I
CLASS="EMPHASIS"
>postmaster</I
></SPAN
> process with
the appropriate run-time options.</P
></BLOCKQUOTE
></DIV
><P
><A
href="c15679#HOSTENTRYSYNTAX"
>Example 8-5</A
> illustrates the general syntax for each type of host record available within
the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> file. Notice that the format is essentially identical for each record, with the
exception that a <SPAN
><I
CLASS="EMPHASIS"
>local</I
></SPAN
> record does not require an IP address or netmask to be specified, as
the connection is assumed to be from the same machine on which PostgreSQL is running.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="HOSTENTRYSYNTAX"
></A
><P
><B
>Example 8-5. Host entry syntax</B
></P
><PRE
CLASS="SCREEN"
># A "local" record.
local <TT
CLASS="REPLACEABLE"
><I
>database auth_method [ auth_option ] </I
></TT
>
# A "host" record.
host <TT
CLASS="REPLACEABLE"
><I
>database ip_addr netmask auth_method [ auth_option ]</I
></TT
>
# A "hostssl" record.
hostssl <TT
CLASS="REPLACEABLE"
><I
>database ip_addr netmask auth_method [ auth_option ]</I
></TT
></PRE
></DIV
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>Remember that each entry in the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> must be a single line. You cannot
word wrap or use line breaks.</P
></BLOCKQUOTE
></DIV
><P
>The following list is a description of the keywords for the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
>
entries mentioned previously:</P
><P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="REPLACEABLE"
><I
>database</I
></TT
></DT
><DD
><P
>This is the database name that the specified host is allowed to connect to.
The <TT
CLASS="REPLACEABLE"
><I
>database</I
></TT
> keyword has three possible values:
<P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="LITERAL"
>all</TT
></DT
><DD
><P
>The <TT
CLASS="LITERAL"
>all</TT
> keyword specifies that the client
connecting can connect to any database the PostgreSQL server is hosting.</P
></DD
><DT
><TT
CLASS="LITERAL"
>sameuser</TT
></DT
><DD
><P
>The <TT
CLASS="LITERAL"
>sameuser</TT
> keyword specifies that the client
can only connect to a database that matches the clients authenticated user name.
</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>name</I
></TT
></DT
><DD
><P
>A specific <TT
CLASS="REPLACEABLE"
><I
>name</I
></TT
> may be specified, so that the client
can only connect to the database as specified by <TT
CLASS="REPLACEABLE"
><I
>name</I
></TT
>.</P
></DD
></DL
></DIV
>
</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>ip_addr</I
></TT
>, <TT
CLASS="REPLACEABLE"
><I
>netmask</I
></TT
></DT
><DD
><P
>The <TT
CLASS="REPLACEABLE"
><I
>ip_addr</I
></TT
> and <TT
CLASS="REPLACEABLE"
><I
>netmask</I
></TT
> fields specify
either a specific IP address, or range of IP addresses, that are allowed to connect to the PostgreSQL server. Such
a range can by specified by describing an IP network with an associated netmask. Otherwise, for a single IP
address, the <TT
CLASS="REPLACEABLE"
><I
>netmask</I
></TT
> field should be set to
<TT
CLASS="LITERAL"
>255.255.255.255</TT
>.</P
><P
>If you are unsure of how to specify a netmask, view the online Linux Networking HOWTO, at
<SPAN
><I
CLASS="EMPHASIS"
>http://www.thelinuxreview.com/howto/networking</I
></SPAN
>, or consult your system administrator.</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>auth_method</I
></TT
></DT
><DD
><P
>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
available for <TT
CLASS="REPLACEABLE"
><I
>auth_method</I
></TT
>:
<P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="LITERAL"
>trust</TT
></DT
><DD
><P
>The <TT
CLASS="LITERAL"
>trust</TT
> method allows any user from the defined host to connect to a
PostgreSQL database without the use of a password, as any PostgreSQL user. You are <SPAN
><I
CLASS="EMPHASIS"
>trusting</I
></SPAN
> the host-based
authentication with the use of this method, and any user on the specified host. This is a dangerous condition
if the specified host is not a secure machine, or provides access to users unknown to you.</P
></DD
><DT
><TT
CLASS="LITERAL"
>reject</TT
></DT
><DD
><P
>The <TT
CLASS="LITERAL"
>reject</TT
> method automatically denies access to PostgreSQL
for that host or user. This can be a prudent setting for sites that you know are <SPAN
><I
CLASS="EMPHASIS"
>never</I
></SPAN
>
allowed to connect to your database server.</P
></DD
><DT
><TT
CLASS="LITERAL"
>password</TT
></DT
><DD
><P
>The <TT
CLASS="LITERAL"
>password</TT
> method specifies that a password must exist for a
connecting user. The use of this method will require the connecting user to supply a password that matches
the password found in the global <TT
CLASS="LITERAL"
>pg_shadow</TT
> system table for their username.
If you use the <TT
CLASS="LITERAL"
>password</TT
> method, the password will be sent in clear
text.</P
></DD
><DT
><TT
CLASS="LITERAL"
>crypt</TT
></DT
><DD
><P
>The <TT
CLASS="LITERAL"
>crypt</TT
> method is similar to the
<TT
CLASS="LITERAL"
>password</TT
> method. When using <TT
CLASS="LITERAL"
>crypt</TT
>,
the password is not sent in clear text, but through a simple form of encryption. The use of this
method is not very secure, but is better than using the clear text <TT
CLASS="LITERAL"
>password</TT
>
method.</P
></DD
><DT
><TT
CLASS="LITERAL"
>krb4</TT
>, <TT
CLASS="LITERAL"
>krb5</TT
></DT
><DD
><P
>The <TT
CLASS="LITERAL"
>krb4</TT
> and <TT
CLASS="LITERAL"
>krb5</TT
> methods are used to
specify Version 4 or 5 of the Kerberos authentication system. The installation and configuration of Kerberos
is beyond the scope of this book, but if you wish to authenticate via Kerberos, these methods are
available.</P
></DD
><DT
><TT
CLASS="LITERAL"
>ident</TT
></DT
><DD
><P
>The <TT
CLASS="LITERAL"
>ident</TT
> method specifies that an <SPAN
><I
CLASS="EMPHASIS"
>ident map</I
></SPAN
>
should be used when a host is requesting connections from a valid IP address listed in the
<SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> file. This method requires one option.</P
><P
>The required option may be either the special term <TT
CLASS="LITERAL"
>sameuser</TT
>, or a named
map that is defined within the <SPAN
><I
CLASS="EMPHASIS"
>pg_ident.conf</I
></SPAN
> file. For more information on defining an
ident map, see <A
href="c15679#THEPGIDENTCONFFILE"
>the Section called <I
>The pg_ident.conf file</I
></A
>."</P
></DD
></DL
></DIV
>
</P
></DD
><DT
><TT
CLASS="LITERAL"
>auth_option</TT
></DT
><DD
><P
>The <TT
CLASS="LITERAL"
>auth_option</TT
> field may or may not be required, based on the type of
authentication method that is used; as of PostgreSQL 7.1.x, only the <TT
CLASS="LITERAL"
>ident</TT
> method
requires an option.</P
></DD
></DL
></DIV
><DIV
CLASS="WARNING"
><P
></P
><TABLE
CLASS="WARNING"
BORDER="1"
WIDTH="100%"
><TR
><TD
ALIGN="CENTER"
><B
>Warning</B
></TD
></TR
><TR
><TD
ALIGN="LEFT"
><P
>We do not suggest the use of either <TT
CLASS="LITERAL"
>password</TT
> or
<TT
CLASS="LITERAL"
>crypt</TT
> without the use of an external encryption mechanism. See <A
href="x16230"
>the Section called <I
>Encrypting sessions</I
></A
>" in this chapter for information on installing a central encryption mechanism for all of
your PostgreSQL traffic.</P
></TD
></TR
></TABLE
></DIV
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="AEN16035"
>Example pg_hba.conf entries</A
></H3
><P
>This section contains a series of examples that can be used within the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
>. To begin,
the host record within <A
href="c15679#SINGLEHOSTENTRY"
>Example 8-6</A
> allows a single machine with the IP address
192.168.1.10 to connect to any database as any user, without the use of a password. This is because
it is configured with the <TT
CLASS="LITERAL"
>all</TT
> and <TT
CLASS="LITERAL"
>trust</TT
> terms,
respectively.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="SINGLEHOSTENTRY"
></A
><P
><B
>Example 8-6. Single host entry</B
></P
><PRE
CLASS="SCREEN"
>host all 192.168.1.10 255.255.255.255 trust</PRE
></DIV
><P
><A
href="c15679#REJECTIONENTRY"
>Example 8-7</A
> shows a host record which will reject all users from host
192.168.1.10, for any requested database. This is set by the use of the terms
<TT
CLASS="LITERAL"
>all</TT
> and <TT
CLASS="LITERAL"
>reject</TT
> as the database target and
authentication method, respectively.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="REJECTIONENTRY"
></A
><P
><B
>Example 8-7. Rejection entry</B
></P
><PRE
CLASS="SCREEN"
>host all 192.168.1.10 255.255.255.255 reject</PRE
></DIV
><P
>The host record in <A
href="c15679#SINGLEDATABASEENTRY"
>Example 8-8</A
> will allow any user with the IP of
192.168.1.10, and a valid password, to connect to the database <TT
CLASS="LITERAL"
>template1</TT
>. The
password will be encrypted during authentication because of the use of the term
<TT
CLASS="LITERAL"
>crypt</TT
>.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="SINGLEDATABASEENTRY"
></A
><P
><B
>Example 8-8. Single host, single database entry</B
></P
><PRE
CLASS="SCREEN"
>host template1 192.168.1.10 255.255.255.255 crypt</PRE
></DIV
><P
>The host record in <A
href="c15679#SMALLNETWORKCONNECTIONENTRY"
>Example 8-9</A
> allows a small subnet of computers to access any
database, without the need of a password. This subnet describes any IP from 192.168.1.1 to
192.168.1.15. Again, if you are unsure of how to configure your netmask, consult your network administrator, or
view the Linux Networking HOWTO at <SPAN
><I
CLASS="EMPHASIS"
>http://www.thelinuxreview.com/howto/networking</I
></SPAN
>.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="SMALLNETWORKCONNECTIONENTRY"
></A
><P
><B
>Example 8-9. Small network connection entry</B
></P
><PRE
CLASS="SCREEN"
>host all 192.168.1.0 255.255.255.240 trust</PRE
></DIV
><P
>Expanding on the use of subnets, the host record in <A
href="c15679#LARGERNETWORKCONNECTIONENTRY"
>Example 8-10</A
> allows any
machine on the 192.168.1 block to connect to the <TT
CLASS="LITERAL"
>booktown</TT
> database,
without the use of a password.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="LARGERNETWORKCONNECTIONENTRY"
></A
><P
><B
>Example 8-10. Larger network connection entry</B
></P
><PRE
CLASS="SCREEN"
>host booktown 192.168.1.0 255.255.255.0 trust</PRE
></DIV
><P
>Remember, as stated earlier in this section, each host record line is read in succession from the top of the file
to the bottom. The first record which matches the host attempting to connect is used. If no matching record is found,
connection is completely disallowed.</P
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="THEPGIDENTCONFFILE"
>The pg_ident.conf file</A
></H3
><P
>When specifying the <TT
CLASS="LITERAL"
>ident</TT
> term as a host record's authentication method,
PostgreSQL uses the <SPAN
><I
CLASS="EMPHASIS"
>pg_ident.conf</I
></SPAN
> file to map the <SPAN
><I
CLASS="EMPHASIS"
>identifying username</I
></SPAN
> to a
PostgreSQL username. The identifying username is the name provided by the connecting client's
<SPAN
><I
CLASS="EMPHASIS"
>identd</I
></SPAN
> service (RFC 1413), which is required to identify the name of the system account initiating
the connection. This method is similar to the <TT
CLASS="LITERAL"
>trust</TT
> method, but restricts access based
on the identifying username.</P
><P
>As stated in the specification for the <SPAN
><I
CLASS="EMPHASIS"
>ident</I
></SPAN
> protocol, "The Identification Protocol is not
intended as an authorization or access control protocol." This is only a useful method of identification for secure,
controlled machines, and is <SPAN
><I
CLASS="EMPHASIS"
>not</I
></SPAN
> intended as a means for secure control from a wide array of external
machines. This is because an <SPAN
><I
CLASS="EMPHASIS"
>identd</I
></SPAN
> daemon merely returns an arbitrary username describing the
current system user. For example, allowing the username <TT
CLASS="LITERAL"
>jworsley</TT
> from an entire subnet
of IP addresses would create a serious security hole, because anyone with a machine in that subnet could create a user named
<TT
CLASS="LITERAL"
>jworsley</TT
> and become "authenticated" as a result.</P
><P
>The <SPAN
><I
CLASS="EMPHASIS"
>pg_ident.conf</I
></SPAN
> file should be located in the same path as the
<SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> file. This should be the path defined by the <TT
CLASS="LITERAL"
>PGDATA</TT
> environment
variable (e.g., <SPAN
><I
CLASS="EMPHASIS"
>/usr/local/pgsql/data</I
></SPAN
>). Like the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
>, changes to the
<SPAN
><I
CLASS="EMPHASIS"
>pg_ident.conf</I
></SPAN
> file do not require PostgreSQL to be re-started.</P
><P
>The content of the <SPAN
><I
CLASS="EMPHASIS"
>pg_ident.conf</I
></SPAN
> associates identifying usernames with PostgreSQL usernames
via definitions called ident <SPAN
><I
CLASS="EMPHASIS"
>maps</I
></SPAN
>. This is useful for users whose system usernames do not match
their PostgreSQL usernames. Some rules you should keep in mind when defining and using an ident map are:
<P
></P
><UL
><LI
><P
>Each ident map <SPAN
><I
CLASS="EMPHASIS"
>member</I
></SPAN
> is defined on a single line, which associates a map name with an identifying username, and a translated PostgreSQL username.</P
></LI
><LI
><P
>The <SPAN
><I
CLASS="EMPHASIS"
>pg_ident.conf</I
></SPAN
> file can contain multiple map names. Each group of single lines with the same associative map name are considered a single map.</P
></LI
><LI
><P
>The <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> file determines the types of connections that relate to users in this file.</P
></LI
></UL
>
</P
><P
>A single line record to define an ident map consist of 3 tokens: the name of the map, the identifying username, and the
translated PostgreSQL username. This syntax is entered as follows, where each token is separated by spaces, or tabs:</P
><PRE
CLASS="SCREEN"
><TT
CLASS="REPLACEABLE"
><I
>mapname</I
></TT
> <TT
CLASS="REPLACEABLE"
><I
>identname</I
></TT
> <TT
CLASS="REPLACEABLE"
><I
>postgresqlname</I
></TT
></PRE
><P
> <P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="REPLACEABLE"
><I
>mapname</I
></TT
></DT
><DD
><P
>The map name used in the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> file to refer to the ident map.</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>identname</I
></TT
></DT
><DD
><P
>The identifying username, which is generally the name of the system user attempting to establish a connection to the database.
This is the name provided by the <SPAN
><I
CLASS="EMPHASIS"
>identd</I
></SPAN
> daemon, which must be running on the system attempting to connect.</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>postgresqlname</I
></TT
></DT
><DD
><P
>The database username which is allowed for the preceding identifying username. You may specify several
lines with the same <TT
CLASS="REPLACEABLE"
><I
>identname</I
></TT
>, but with different <TT
CLASS="REPLACEABLE"
><I
>postgresqlname</I
></TT
> values,
in order to allow a single system user access to several accounts, which do not all need to be on the same database.</P
></DD
></DL
></DIV
>
</P
><P
>As an example, suppose that the Book Town server has a set of system accounts named
<TT
CLASS="LITERAL"
>jdrake</TT
>, <TT
CLASS="LITERAL"
>jworsley</TT
>, and
<TT
CLASS="LITERAL"
>auditor</TT
>, used for two salespeople and an internal auditor, respectively.</P
><P
>You may wish to create a pair of ident maps for these two groups of users. Suppose that the sales department's
workstation has an IP address of 192.168.1.3, and only needs access to the
<TT
CLASS="LITERAL"
>booktown</TT
> database, while the audit department's workstation has an IP address of
192.168.1.4, and requires access to all databases. This scenario might result in a <SPAN
><I
CLASS="EMPHASIS"
>pga_hba.conf</I
></SPAN
>,
such as the one displayed in <A
href="c15679#EXAMPLEIDENTCONFIGURATION"
>Example 8-11</A
>.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="EXAMPLEIDENTCONFIGURATION"
></A
><P
><B
>Example 8-11. An ident configuration in pg_hba.conf</B
></P
><PRE
CLASS="SCREEN"
>host booktown 192.168.1.3 255.255.255.255 ident sales
host all 192.168.1.4 255.255.255.255 ident audit</PRE
></DIV
><P
>This host access configuration states that the sales machine may connect to the
<TT
CLASS="LITERAL"
>booktown</TT
> database using an ident map named <SPAN
><I
CLASS="EMPHASIS"
>sales</I
></SPAN
>, and the audit
workstation may connect to <SPAN
><I
CLASS="EMPHASIS"
>any</I
></SPAN
> database using an ident map named <SPAN
><I
CLASS="EMPHASIS"
>audit</I
></SPAN
>. Each of
these maps must then be configured within the <SPAN
><I
CLASS="EMPHASIS"
>pg_ident.conf</I
></SPAN
> file. <A
href="c15679#EXAMPLEIDENTCONF"
>Example 8-12</A
>
demonstrates such a configuration.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="EXAMPLEIDENTCONF"
></A
><P
><B
>Example 8-12. A pg_ident.conf configuration</B
></P
><PRE
CLASS="SCREEN"
># MAP IDENT POSTGRESQL_USERNAME
sales jdrake sales
sales jworsley sales
audit auditor sales
audit auditor postgres</PRE
></DIV
><P
>The file shown in <A
href="c15679#EXAMPLEIDENTCONF"
>Example 8-12</A
> allows either of the system users
<TT
CLASS="LITERAL"
>jdrake</TT
> or <TT
CLASS="LITERAL"
>jworsley</TT
> to connect as the PostgreSQL
<TT
CLASS="LITERAL"
>sales</TT
> user, and allows the system user named <TT
CLASS="LITERAL"
>auditor</TT
> to
connect to PostgreSQL as either <TT
CLASS="LITERAL"
>sales</TT
>, or <TT
CLASS="LITERAL"
>postgres</TT
>.</P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>It is possible for an identifying username to be mapped to multiple PostgreSQL usernames. This is illustrated in
<A
href="c15679#EXAMPLEIDENTCONF"
>Example 8-12</A
> with the <TT
CLASS="LITERAL"
>auditor</TT
> user.</P
></BLOCKQUOTE
></DIV
><P
>If you wish only to use <TT
CLASS="LITERAL"
>ident</TT
> as a means of automatically identifying your remote
username, you do not need to use the <SPAN
><I
CLASS="EMPHASIS"
>pg_ident.conf</I
></SPAN
> file. You can instead use the special term
<TT
CLASS="LITERAL"
>sameuser</TT
> in the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> file, in place of a map name.</P
><P
>Again, this is similar to the <TT
CLASS="LITERAL"
>trusted</TT
> method, however
<TT
CLASS="LITERAL"
>ident sameuser</TT
> restricts connections based on the username provided by
<SPAN
><I
CLASS="EMPHASIS"
>identd</I
></SPAN
>. Providing a PostgreSQL username to connect with (e.g., with the <SPAN
><I
CLASS="EMPHASIS"
>-U</I
></SPAN
>  flag
to <SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
> ) that is different from the name sent by <SPAN
><I
CLASS="EMPHASIS"
>identd</I
></SPAN
> will result in a
failure to connect.</P
><P
>Use of the <TT
CLASS="LITERAL"
>sameuser</TT
> map is demonstrated in <A
href="c15679#EXAMPLESAMEUSERCONFIGURATION"
>Example 8-13</A
>.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="EXAMPLESAMEUSERCONFIGURATION"
></A
><P
><B
>Example 8-13. A sameuser configuration</B
></P
><PRE
CLASS="SCREEN"
>host booktown 192.168.1.0 255.255.255.0 ident sameuser</PRE
></DIV
><P
>The host record in <A
href="c15679#EXAMPLESAMEUSERCONFIGURATION"
>Example 8-13</A
> allows any machine on the
192.168.1 network block to connect to the <TT
CLASS="LITERAL"
>booktown</TT
> database, using
the PostgreSQL username that matches the username provided by <SPAN
><I
CLASS="EMPHASIS"
>identd</I
></SPAN
>. The
<TT
CLASS="LITERAL"
>sameuser</TT
> term causes PostgreSQL to implicitly compare the requested PostgreSQL username
against the name provided by <SPAN
><I
CLASS="EMPHASIS"
>identd</I
></SPAN
>.</P
></DIV
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN16189"
>Authentication Failure</A
></H2
><P
>When authentication failure occurs, PostgreSQL will usually do its best to provide a useful error message, rather
than blindly fail. The following are common error messages you may encounter, with explanations:
<P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="LITERAL"
>FATAL 1: user "testuser" does not exist</TT
></DT
><DD
><P
>The specified username was not found in the <TT
CLASS="LITERAL"
>pg_shadow</TT
> system table, meaning the user
does not exist. See <A
href="c18591"
>Chapter 10</A
> for more on adding users.</P
></DD
><DT
><TT
CLASS="LITERAL"
>FATAL 1: Database "testdb" does not exist in the system catalog</TT
></DT
><DD
><P
>This database cannot be found because it does not exist. Note that if you do not specify a database name to a
PostgreSQL connection, it will attempt to connect to the provided username.</P
></DD
><DT
><TT
CLASS="LITERAL"
>No pg_hba.conf entry for host 123.123.123.1, user testuser, database testdb</TT
></DT
><DD
><P
>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
<TT
CLASS="LITERAL"
>testuser</TT
> using <TT
CLASS="LITERAL"
>testdb</TT
> at their IP address (123.123.123.1)
in the <SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> file.</P
></DD
><DT
><TT
CLASS="LITERAL"
>Password authentication failed for user 'testuser'</TT
></DT
><DD
><P
>You have succeeded in contacting the server and it is replying back, but the connection
failed password authorization. Check the password you are supplying to the server,
and make sure that it is correct. Further, you can check the Kerberos or Ident software
programs if you are using them for your password authentication.</P
><P
>You may want to check if this user has a password. If this user does not have one, and the
<SPAN
><I
CLASS="EMPHASIS"
>pg_hba.conf</I
></SPAN
> file is set to check for passwords, it will still check every user for their
password. For all users without a defined password, a <TT
CLASS="LITERAL"
>NULL</TT
> password is assigned to
that user. When the user tries to log in and does not specify a password, it will compare the
<TT
CLASS="LITERAL"
>NULL</TT
> password to the <TT
CLASS="LITERAL"
>NULL</TT
> input, and it will return
<TT
CLASS="LITERAL"
>false</TT
>.</P
><P
>On the other hand, if the user tries to supply a password (even a blank one), it will compare that input value
with the <TT
CLASS="LITERAL"
>NULL</TT
> password and still return <TT
CLASS="LITERAL"
>false</TT
>. If you are using
password authentication, you must assign a password to all users. If a password is not assigned to a user in
such a scheme, password authentication will always fail, and the user will not be able to log in.</P
></DD
></DL
></DIV
>
</P
></DIV
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
href="p15677"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
href="book1"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
href="x16230"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Administrating PostgreSQL</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
href="p15677"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Encrypting sessions</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
</div>
<br />
<div id="thefineprint">
<a href="http://www.commandprompt.com/products/mammothpostgresql/"><img src="../images/powered_by_mammoth.gif" alt="Powered by Mammoth PostgreSQL" title="Powered by Mammoth PostgreSQL" border="0" /></a>
<br />
Copyright © 2000-2007 Command Prompt, Inc. All Rights Reserved. All trademarks property of their respective owners.
</div>
</div>
</div>
</body>
</html>