Fediversity/matrix/postgresql

Table of Contents

• Installing PostgreSQL and creating database and user
• Configuring access
• Network
• UNIX socket
• Permissions
• Tuning

Installing PostgreSQL and creating database and user

Installing PostgreSQL on Debian is very easy:

apt install postgresql python3-psycopg

sudo -u postgres bash

createuser --pwprompt synapse
createdb --encoding=UTF8 --locale=C --template=template0 --owner=synapse synapse

After this, PostgreSQL is installed, the database synapse exists and so does the database user synapse. Make sure you choose a strong password.

Configuring access

After a clean installation, PostgreSQL will listen on localhost, both IPv4 and IPv6 (if available). In many cases, this is exactly what you want.

Network

PostgreSQL will listen on localhost, this is configured in /etc/postgresql/<version>/main/postgresql.conf:

listen_addresses = 'localhost'

This line is usually commented out, but as it is the default, it's really there.

UNIX socket

If you want PostgreSQL to listen only to a local UNIX socket (more efficient than network and -depending on the configuration of the rest of you system- easier to protect), make the aforementioned option explicitly empty and uncomment it:

listen_addresses = ''

Check these options to make sure the socket is placed in the right spot and given the correct permissions:

unix_socket_directories = '/var/run/postgresql'
#unix_socket_group = ''
#unix_socket_permissions = 0777

Permissions

Add permission for the user to connect to the database from localhost (if PostgreSQL listens on localhost), or the socket (if you use that). This is configured in /etc/postgresql/<version>/main/pg_hba.conf:

local    synapse     synapse     password                     # for use with UNIX sockets
host     synapse     synapse     localhost       md5          # for use with localhost network

Make sure you add these lines under the one that gives access to the postgres superuser, the first line.

Tuning

This is for later, check Tuning your PostgreSQL Server on the PostgreSQL wiki.