Skip to main content
Version: 5.2

PostgreSQL Configuration

Installing and Initial Setup of PostgreSQL​

Note

Before installation, make sure the server meets the hardware requirements.

If PostgreSQL is not yet installed, follow these steps:

Installing PostgreSQL​

sudo apt update
sudo apt install postgresql postgresql-contrib

Check the service status:

systemctl status 'postgresql*'

Creating a User and Database​

sudo su - postgres -c "createuser user_name"
sudo su - postgres -c "createdb db_name"

Configuring Access​

Open the postgresql.conf configuration file and modify the following parameter:

listen_addresses = '*'

In the pg_hba.conf file, add the following line:

host all all 0.0.0.0/0 password

After making the changes, restart PostgreSQL:

sudo systemctl restart postgresql
warning

This rule grants access to the database from any IP address. If you need to restrict access to specific addresses, create separate entries with the required IPs or subnets instead of 0.0.0.0/0.

For example, to allow connections only from a single host 172.16.0.8, use:

host all all 172.16.0.8/32 password

After making the changes, restart PostgreSQL:

sudo systemctl restart postgresql

Setting User Password and Permissions​

Note

In all commands below, replace user_name, db_name и password with values appropriate for your system:

  • user_name — your PostgreSQL username
  • db_name — your database name
  • password — the user's password

Run psql as the postgres user:

sudo -u postgres psql

Within the interactive shell, execute the following commands in sequence:

\c db_name
ALTER USER user_name WITH PASSWORD 'password';
GRANT CREATE ON SCHEMA public TO user_name;
ALTER DATABASE db_name OWNER TO user_name;
Important!

If you encounter the error relation does not exist, it may be necessary to manually prepare the tables:

sudo -u postgres psql

\c db_name
CALL public.preparetables('assets_name');
ALTER TABLE assets_name OWNER TO user_name;

To test the connection:

psql -U user_name -h 127.0.0.1 -p 5432 -d db_name

Configuring SSL/TLS​

Enabling SSL on the PostgreSQL Server​

In the postgresql.conf file, enable SSL and specify the certificate paths:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

In the pg_hba.conf file, add the following line to require SSL connections:

hostssl all all 0.0.0.0/0 md5

The server.crt and server.key files must be placed in the PostgreSQL data directory ($PGDATA). Set the appropriate permissions:

chmod 600 server.key
chown postgres:postgres server.*
Important!

The certificate files must be in PEM format. PostgreSQL only supports PEM.

Note

Generating self-signed certificates

If you don't have existing certificates, you can generate a self-signed SSL certificate using OpenSSL:

openssl req -new -x509 -days 365 -nodes \
  -out server.crt \
  -keyout server.key \
  -subj "/CN=your_domain_or_ip"

For more details, refer to the official PostgreSQL.

Restart PostgreSQL to apply the changes:

sudo systemctl restart postgresql