6.5 Postgres

The postgres module provides interface to PostgreSQL database management system. It may be used to build smap databases over SQL ones.

The module is in many regards similar to mysql module, described above. In particular, its overall functionality is exactly the same as described in Mysql, except, of course, that it uses PostgreSQL databases.

6.5.1 Postgres Configuration

A Postgres database is configured using a set of options understood by the Postgres PQconnectdb function. See http://www.postgresql.org/docs/8.4/static/libpq-connect.html, for a detailed description. The following is a short summary of the most useful options:

host=name

Name of host to connect to. If this begins with a slash, it specifies Unix-domain communication rather than TCP/IP communication; the value is the name of the directory in which the socket file is stored.

hostaddr=ip

Numeric IP address of host to connect to.

port=number

Port number to connect to at the server host, or socket file name extension for Unix-domain connections.

dbname=name

The database name.

user=name

PostgreSQL user name to connect as. Defaults to be the same as the operating system name of the user running the smapd.

password=string

Password to be used if the server demands password authentication.

connect_timeout=number

Maximum wait for connection, in seconds. Zero or not specified means wait indefinitely.

options=string

Any additional command-line options to send to the server at run-time. For example, setting this to ‘-c geqo=off’ sets the session's value of the ‘geqo’ parameter to ‘off’. For a detailed discussion of the available options, see Postgres documentation(7).

sslmode=mode

This option determines whether or with what priority an SSL TCP/IP connection will be negotiated with the server. There are six modes: ‘disable’, ‘allow’, ‘prefer’, ‘require’, ‘verify-ca’ and ‘verify-full(8).

sslcert=file

This parameter specifies the file name of the client SSL certificate.

sslkey==file-or-engine-name

This parameter specifies the location for the secret key used for the client certificate.

sslrootcert=file

This parameter specifies the file name of the root SSL certificate.

sslcrl=name

This parameter specifies the file name of the SSL certificate revocation list (CRL).

krbsrvname=name

Kerberos service name to use when authenticating with Kerberos 5 or GSSAPI.

service=name

Service name to use for additional parameters.

6.5.2 Postgres Query and SMAP Replies

Postgres SQL query and the smap replies are configured the same way as for mysql module (see section MySQL Query and SMAP Replies). The following is a short summary:

query=template

Define the Postgres query template. The template may reference the following variables:

Variable Meaning
map Name of the map being queried
key Lookup key

Table 6.2: Postgres query template variables

If the database definition lacks the query option, it will attempt to use one from the module statement. If the module statement lacked it as well, an error is reported.

positive-reply=template

Defines a reply to be sent if the query returned a non-empty set of tuples. In addition to the variables described above (see postgres-query-vars), the template may also refer to the column names from the SQL result set.

The default positive-reply is ‘OK’.

negative-reply=template

Defines a reply to be sent if the query returned an empty set of tuples. The template may refer to the variables described in postgres-query-vars.

Default value is ‘NOTFOUND’.

onerror-reply=template

Defines a reply to be sent if an error occurred when executing the query. The template may refer to the variables described in postgres-query-vars.

Default value is ‘NOTFOUND’.

Footnotes

(7)

For PostgreSQL version 8.4, see Chapter 18 in PostgreSQL Manual.

(8)

For PostgreSQL version 8.4, see Section 30.17 in PostgreSQL Manual.