Warning: postgresql configuration is not controlled by a Debian package, but it should be. Ideally `pg_hba.conf` and `pg_ident.conf` would be managed by DomTool, or some other system management daemon. In the meantime, here's what we're doing.

Current node is fritz, with Postgres 8.1 on port 5422, and Postgres 9.1 on 5433. Note that navajos/bog can only use Postgres 9.1, and support for 8.1 will be removed once mire has been decommissioned.

The source code of DomTool's dbms module is useful as documentation.

== Tablespaces ==

Each user has a table space in `/srv/database/$PATHBITS/$USER/{postgres,postgres-9.1}`, created by the `create-user-database` script.

Tablespaces are an artifact from when we stored databases in afs. There may be some organizational advantage as well, but future admins should revisit the issue.

== Authentication ==

The `pg_hba.conf` of every install must explicitely list the allowed hosts. Firewall rules on both sides should be opened.

Additionally, there must be a rule to allow clients on the database server to connect to itself over tcp for various administrative functions, since DomTool is configured to use TCP for maintaining node independence.

Postgres 8.1 uses ident. It's a hack, but GSSAPI support wasn't quite functional enough, or at least we'll say it wasn't.

Postgres 9.1 uses GSSAPI. An `pg_ident.conf` is used to map `$USER/daemon@HCOOP.NET` to the Postgres user `$USER`. This has the advantage that `$USER@HCOOP.NET` resolves to the same database user. See the [[http://www.postgresql.org/docs/9.1/static/auth-methods.html#KERBEROS-AUTH|postgres auth docs]] for details; we're using a pretty standard set up.

=== Kerberos Service Key ===

Remeber to create and extract a service key for postgres. '''Note that the keytab is not the system wide keytab, but a postgres specific one'''. You must also chown it to be readable by only the postgres server account.

{{{
addprinc -randkey postgres/$HOST.hcoop.net
ktadd -k /etc/postgresql-common/krb5.keytab postgres/$HOST.hcoop.net
}}}

(where `$HOST` is the name Kerberos and reverse DNS use for the node, not the `postgres` alias).

== Network ==

Always remember to set `listen_addresses = '*'`, or (better) the IP that postgres should really listen on. Otherwise, connections will fail mysteriously.

----
CategoryNeedsWork CategorySystemAdministration