Setting up PostgreSQL #

This page is for database administrators who prepare PostgreSQL for an Exponential site, the project's recommended database server. It answers: how to create the account and the database, why the account must own the database, why pgcrypto is needed and how to provide it, what the settings look like and how to run the installer. Source: 9.4 PostgreSQL; driver lib/ezdb/classes/ezpostgresqldb.php.

Contents #

1. Requirements
2. Create the user and the database
3. pgcrypto
4. Allow the connection
5. The settings
6. Install
7. First checks
8. Checklist

Requirements #

• PostgreSQL 13 or later (the wizard accepts 8.0, but pgcrypto is a trusted extension only from 13 on, and the installer relies on that). The project's CI runs PostgreSQL 15; the development server runs 13.
• PHP extension pgsql in every PHP that uses the site (PHP extensions per engine).
• Your distribution's PostgreSQL contrib package, which carries pgcrypto, if the server does not have it yet.

Create the user and the database #

sudo -u postgres createuser --pwprompt exponential
sudo -u postgres createdb --owner=exponential --encoding=UTF8 --template=template0 exponential

• The owner matters. From PostgreSQL 15 on, ordinary users may no longer create tables in the public schema of a database they do not own. Making the site's account the owner gives it what the installer and every later update file need, and nothing more.
• --template=template0 lets you choose the encoding regardless of the server's default.
• --pwprompt asks for the password instead of taking it on the command line.

None of the installers creates the database or the user; create them first.

pgcrypto #

The driver computes MD5 with encode(digest(..., 'md5'), 'hex'), and digest() comes from pgcrypto. The installer runs CREATE EXTENSION IF NOT EXISTS pgcrypto when the server ships the extension; as a trusted extension the database owner may create it. If the server lacks it, install the contrib package, then, as the owner or a superuser:

sudo -u postgres psql -d exponential -c 'CREATE EXTENSION IF NOT EXISTS pgcrypto;'

When it is missing, the setup wizard says: "The 'digest' function is not available in your database, and Exponential cannot run without it." Create the extension and click Next again. The update files also need digest() (11.3 per-database notes).

Allow the connection #

"Connection refused" or an authentication error at the first connection is usually the server's own configuration:

• listen_addresses in postgresql.conf must include the address the web servers connect to (for a database on the same machine localhost is enough);
• pg_hba.conf needs a line that allows the site's user, from the web servers' addresses, to the site's database, with a password method.

Reload PostgreSQL after a change. The authoritative reference is PostgreSQL's client authentication chapter.

The settings #

In settings/override/site.ini.append.php (the installers write it):

[DatabaseSettings]
DatabaseImplementation=ezpostgresql
Server=localhost
# 5432 is the server's default
Port=5432
User=exponential
Password=<the password>
Database=exponential
# enabled: pg_pconnect instead of pg_connect
UsePersistentConnection=disabled

The aliases postgresql and pgsql name the same driver.

Install #

EXP_INSTALL_DB_PASSWORD='<the password>' php bin/php/console exp:install --apply --db=pgsql \
    --db-host=127.0.0.1 --db-name=exponential --db-user=exponential --allow-root-user

exp:install takes the password from EXP_INSTALL_DB_PASSWORD, so it stays out of the process list. --db=postgresql and --db=postgres are accepted too. The setup wizard prefills port 5432 and user postgres (settings/setup.ini DefaultPort_pgsql, DefaultUser_pgsql); replace the user with the site's account. The kickstarter takes [database_choice] Type=pgsql.

First checks #

psql -U exponential -d exponential -c '\dx'                       # pgcrypto is listed
psql -U exponential -d exponential -c "SELECT name, value FROM ezsite_data WHERE name LIKE 'ezpublish-%';"
psql -U exponential -d exponential -c '\dt' | wc -l               # the kernel's tables are there

Then open Setup > System information: the Database card shows PostgreSQL, the server version and UTF8.

Checklist #

• To do: PostgreSQL 13 or later, a release with security support.
• To do: The site's account owns the database; the database is UTF8.
• To do: pgcrypto exists in the site's database.
• To do: pg_hba.conf allows only the web servers, with a password method.
• To do: The account is not a superuser.
• To do: Backups with pg_dump -Fc are scheduled (Backups per engine).

Related pages #

• Operating PostgreSQL: sequences, case and gotchas
• Database accounts and privileges
• PostgreSQL for hosts
• Databases and users in hosting panels
• Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.