<!--

$PostgreSQL: pgsql/doc/src/sgml/libpq.sgml,v 1.174 2004/12/28 23:17:18 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/libpq.sgml,v 1.175 2005/01/06 18:29:07 tgl Exp $

-->

<chapter id="libpq">

likely to fail if <application>libpq</application> is passed a host name

that is not the name of the machine at <literal>hostaddr</>.) Also,

<literal>host</> rather than <literal>hostaddr</> is used to identify

the connection in <filename>$HOME/.pgpass</> (or

<filename>%USERPROFILE%\.pgpass</> on Microsoft Windows).

the connection in <filename>~/.pgpass</> (see

<xref linkend="libpq-pgpass">).

</para>

<para>

Without either a host name or host address,

<envar>PGPASSWORD</envar>

sets the password used if the server demands password

authentication. This environment variable is deprecated for security

reasons; instead consider using the <filename>$HOME/.pgpass</>

reasons; instead consider using the <filename>~/.pgpass</>

file (see <xref linkend="libpq-pgpass">).

</para>

</listitem>

The file <filename>.pgpass</filename> in a user's home directory is a file

that can contain passwords to be used if the connection requires a

password (and no password has been specified otherwise).

This file should have lines of the following format:

On Microsoft Windows the file is named

<filename>APPDATA/postgresql/pgpass.txt</>.

</para>

<para>

This file should contain lines of the following format:

<synopsis>

<replaceable>hostname</replaceable>:<replaceable>port</replaceable>:<replaceable>database</replaceable>:<replaceable>username</replaceable>:<replaceable>password</replaceable>

</synopsis>

If an entry needs to contain <literal>:</literal> or

<literal>\</literal>, escape this character with <literal>\</literal>.

</para>

<para>

The permissions on <filename>.pgpass</filename> must disallow any

access to world or group; achieve this by the command

<command>chmod 0600 ~/.pgpass</command>.

If the permissions are less strict than this, the file will be ignored.

(The file permissions are not currently checked on Microsoft Windows,

however.)

</para>

</sect1>

If the server demands a client certificate,

<application>libpq</application>

will send the certificate stored in file

<filename>.postgresql/postgresql.crt</> within the user's home directory.

A matching private key file <filename>.postgresql/postgresql.key</>

<filename>~/.postgresql/postgresql.crt</> within the user's home directory.

A matching private key file <filename>~/.postgresql/postgresql.key</>

must also be present, and must not be world-readable.

(On Microsoft Windows these files are named

<filename>APPDATA/postgresql/postgresql.crt</filename> and

<filename>APPDATA/postgresql/postgresql.key</filename>.)

</para>

<para>

If the file <filename>.postgresql/root.crt</> is present in the user's

If the file <filename>~/.postgresql/root.crt</> is present in the user's

home directory,

<application>libpq</application> will use the certificate list stored

therein to verify the server's certificate. The SSL connection will

therein to verify the server's certificate.

(On Microsoft Windows the file is named

<filename>APPDATA/postgresql/root.crt</filename>.)

The SSL connection will

fail if the server does not present a certificate; therefore, to

use this feature the server must also have a <filename>root.crt</> file.

</para>

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v 1.47 2005/01/04 03:58:16 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v 1.48 2005/01/06 18:29:08 tgl Exp $

PostgreSQL documentation

-->

times to the <productname>PostgreSQL</productname> server (once per

database). If you use password authentication it is likely to ask for

a password each time. It is convenient to have a

<filename>$HOME/.pgpass</> file in such cases.

<filename>~/.pgpass</> file in such cases. See <xref

linkend="libpq-pgpass"> for more information.

</para>

</refsect1>

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/psql-ref.sgml,v 1.127 2005/01/04 03:58:16 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/psql-ref.sgml,v 1.128 2005/01/06 18:29:08 tgl Exp $

PostgreSQL documentation

-->

<term><option>--no-psqlrc</></term>

<listitem>

<para>

Do not read the start-up file <filename>/psqlrc</filename> or

<filename>~/.psqlrc</filename>.

Do not read the start-up file (neither the system-wide

<filename>psqlrc</filename> file nor the user's

<filename>~/.psqlrc</filename> file).

</para>

</listitem>

</varlistentry>

=> <userinput>\echo `date`</userinput>

Tue Oct 26 21:40:57 CEST 1999

</programlisting>

If the first argument is an unquoted <literal>-n</literal> thethetrailing

If the first argument is an unquoted <literal>-n</literal> the trailing

newline is not written.

</para>

The autocommit-on mode is <productname>PostgreSQL</>'s traditional

behavior, but autocommit-off is closer to the SQL spec. If you

prefer autocommit-off, you may wish to set it in the system-wide

<filename>psqlrc</filename> or your

<filename>.psqlrc</filename> file.

<filename>psqlrc</filename>fileor your

<filename>~/.psqlrc</filename> file.

</para>

</note>

</listitem>

<para>

<application>psql</application> supports the <application>Readline</application>

library for convenient line editing and retrieval. The command

history isstored in a file named <filename>.psql_history</filename>

in your home directory and is reloaded when

history isautomatically saved when <application>psql</application>

exits and is reloaded when

<application>psql</application> starts up. Tab-completion is also

supported, although the completion logic makes no claim to be an

<acronym>SQL</acronym> parser. If for some reason you do not like the tab completion, you

<title>Environment</title>

<variablelist>

<varlistentry>

<term><envar>HOME</envar></term>

<listitem>

<para>

Directory for initialization file (<filename>.psqlrc</filename>)

and command history file (<filename>.psql_history</filename>).

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><envar>PAGER</envar></term>

<listitem>

<para>

Before starting up, <application>psql</application> attempts to

read and execute commands from the the system-wide

<filename>psqlrc</filename> file and the

<filename>$HOME/.psqlrc</filename> file in the user's home

directory. See <filename><replaceable>PREFIX</>/share/psqlrc.sample</>

read and execute commands from the system-wide

<filename>psqlrc</filename> file and the user's

<filename>~/.psqlrc</filename> file.

(On Windows, the user's startup file is named

<filename>APPDATA/postgresql/psqlrc.txt</filename>.)

See <filename><replaceable>PREFIX</>/share/psqlrc.sample</>

for information on setting up the system-wide file. It could be used

to set up the client or the server to taste (using the <command>\set

</command> and <command>SET</command> commands).

</para>

</listitem>

<listitem>

<para>

Both the system-wide <filename>psqlrc</filename> file and the user's

<filename>~/.psqlrc</filename> file can be made version-specific

by appending a dash and the <productname>PostgreSQL</productname>

release number, for example <filename>~/.psqlrc-&version;</filename>.

A matching version-specific file will be read in preference to a

non-version-specific file.

</para>

</listitem>

<listitem>

<para>

The command-line history is stored in the file

<filename>$HOME/.psql_history</filename>.

<filename>~/.psql_history</filename>, or

<filename>APPDATA/postgresql/psql_history</filename> on Windows.

</para>

</listitem>

</itemizedlist>

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/vacuumdb.sgml,v 1.34 2003/11/29 19:51:39 pgsql Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/vacuumdb.sgml,v 1.35 2005/01/06 18:29:08 tgl Exp $

PostgreSQL documentation

-->

<term><option>--all</option></term>

<listitem>

<para>

Vacuum all databases.

Vacuum all databases.

</para>

</listitem>

</varlistentry>

<term><option><optional>--dbname</> <replaceable class="parameter">dbname</replaceable></option></term>

<listitem>

<para>

Specifies the name of the database to be cleaned or analyzed.

If this is not specified and <option>-a</option> (or

<option>--all</option>) is not used, the database name is read

from the environment variable <envar>PGDATABASE</envar>. If

that is not set, the user name specified for the connection is

used.

Specifies the name of the database to be cleaned or analyzed.

If this is not specified and <option>-a</option> (or

<option>--all</option>) is not used, the database name is read

from the environment variable <envar>PGDATABASE</envar>. If

that is not set, the user name specified for the connection is

used.

</para>

</listitem>

</varlistentry>

<listitem>

<para>

Echo the commands that <application>vacuumdb</application> generates

and sends to the server.

and sends to the server.

</para>

</listitem>

</varlistentry>

<term><option>--table <replaceable class="parameter">table</replaceable> [ (<replaceable class="parameter">column</replaceable> [,...]) ]</option></term>

<listitem>

<para>

Clean or analyze <replaceable class="parameter">table</replaceable> only.

Column names may be specified only in conjunction with

the <option>--analyze</option> option.

Clean or analyze <replaceable class="parameter">table</replaceable> only.

Column names may be specified only in conjunction with

the <option>--analyze</option> option.

</para>

<tip>

<para>

<term><option>--verbose</option></term>

<listitem>

<para>

Print detailed information during processing.

Print detailed information during processing.

</para>

</listitem>

</varlistentry>

<term><option>--analyze</option></term>

<listitem>

<para>

Calculate statistics for use by the optimizer.

Calculate statistics for use by the optimizer.

</para>

</listitem>

</varlistentry>

<term><option>--host <replaceable class="parameter">host</replaceable></></term>

<listitem>

<para>

Specifies the host name of the machine on which the

server

is running. If the value begins with a slash, it is used

as the directory for the Unix domain socket.

Specifies the host name of the machine on which the

server

is running. If the value begins with a slash, it is used

as the directory for the Unix domain socket.

</para>

</listitem>

</varlistentry>

<term><option>--port <replaceable class="parameter">port</replaceable></></term>

<listitem>

<para>

Specifies the TCP port or local Unix domain socket file

extension on which the server

is listening for connections.

Specifies the TCP port or local Unix domain socket file

extension on which the server

is listening for connections.

</para>

</listitem>

</varlistentry>

<application>vacuumdb</application> might need to connect several

times to the <productname>PostgreSQL</productname> server, asking

for a password each time. It is convenient to have a

<filename>$HOME/.pgpass</> file in such cases. See <xref

<filename>~/.pgpass</> file in such cases. See <xref

linkend="libpq-pgpass"> for more information.

</para>

</refsect1>

<!--

$PostgreSQL: pgsql/doc/src/sgml/release.sgml,v 1.317 2005/01/0601:49:24 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/release.sgml,v 1.318 2005/01/0618:29:07 tgl Exp $

-->

<appendix id="release">

<sect3>

<title>libpq</title>

<itemizedlist>

<listitem><para>Add$HOME/.pgpass to store host/user password combinations (Alvaro Herrera)</para></listitem>

<listitem><para>Add~/.pgpass to store host/user password combinations (Alvaro Herrera)</para></listitem>

<listitem><para>Add PQunescapeBytea() function to libpq (Patrick Welche)</para></listitem>

<listitem><para>Fix for sending large queries over non-blocking connections (Bernhard Herzog)</para></listitem>

<listitem><para>Fix for libpq using timers on Win9X (David Ford)</para></listitem>

for the shell program to be bogus (e.g., /bin/false), if the

tunnel is set up in to avoid launching a remote shell.

On each client system the$HOME/.ssh/config file should contain

On each client system the~/.ssh/config file should contain

an additional line similiar to

LocalForward 5555 psql.example.com:5432

#!/bin/sh

HOST=psql.example.com

IDENTITY=$HOME/.ssh/identity.psql

IDENTITY=~/.ssh/identity.psql

/usr/bin/ssh -1 -i $IDENTITY -n $HOST 'sleep 60' & \

/usr/bin/psql -h $HOST -p 5555 $1

The client.conf file is normally installed in /etc/postgresql/root.crt.

The client should also copy the server's root.crt file to

$HOME/.postgresql/root.crt.

~/.postgresql/root.crt.

2. If the user has the OpenSSL applications installed, they can

run pgkeygen.sh. (An equivalent compiled program will be available

in the future.) They should provide a copy of the

$HOME/.postgresql/postgresql.pem file to their DBA.

~/.postgresql/postgresql.pem file to their DBA.

3. The DBA should sign this file the OpenSSL applications:

$ openssl ca -config root.conf -ss_cert ....

and return the signed cert (postgresql.crt) to the user.

4. The user should install this file in$HOME/.postgresql/postgresql.crt.

4. The user should install this file in~/.postgresql/postgresql.crt.

The server will log every time a client certificate has been

used, but there is not yet a mechanism provided for using client

> for the shell program to be bogus (e.g., /bin/false), if the

> tunnel is set up in to avoid launching a remote shell.

>

> On each client system the$HOME/.ssh/config file should contain

> On each client system the~/.ssh/config file should contain

> an additional line similiar to

>

> LocalForward 5555 psql.example.com:5432