I’m setting up a homelab and obviously some services need a database to do the job.

Install PostgreSQL

First I install the needed packages:

$ sudo pkg install postgresql13-server postgresql13-client

I initialize then start and enable the service to start at boot time:

$ sudo service postgresql oneinitdb
$ sudo service postgresql enable
$ sudo service postgresql start

Now I should be able to connect as postgres user

psql -U postgres
Password for user postgres:
psql (13.6)
Type "help" for help.

postgres=#

It works!

Configure PostgreSQL

By default the postgres user, who is the super admin of our PostgreSQL service, doesn’t have password. Let me change that:

$ psql -U postgres
postgres=# /* By default the value of password_encryption is md5
postgres*# we change temporarily to a better one: */
postgres=# SET password_encryption = 'scram-sha-256';
postgres=# SHOW password_encryption;
 password_encryption
---------------------
 scram-sha-256
(1 row)
postgres=# ALTER USER "postgres" WITH PASSWORD 'YourStrongPasswordHere';
ALTER ROLE

I can now edit /var/db/postgres/data13/postgresql.conf to change the default password encryption method to scram-sha-256:

password_encryption = scram-sha-256

Then we force users to authenticate by editing /var/db/postgres/data13/pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     scram-sha-256
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     scram-sha-256
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

We can now restart PostgreSQL to apply changes and test the results:

$ sudo service postgresql restart
$ psql -U postgres
Password for user postgres:
psql (13.6)
Type "help" for help.

postgres=#

Create a PostgreSQL user and database

In PostgreSQL, user and groups are called roles.

postgres=# CREATE USER YourUser WITH PASSWORD 'YourPassword!?';
postgres=# CREATE DATABASE YourDBName WITH OWNER YourUser;

To change a user’s password:

postgres=# ALTER USER YourUser WITH PASSWORD 'YourNewPassword';

Useful when I forgot to set password_encryption to scram-sha-256 at run time during the first configuration.

To delete a database or a user:

postgres=# DROP DATABASE YourDBName;
postgres=# DROP USER YourName;
Warning

Before you drop a database, do a backup first! If you want to delete the user but keep the database. You can change the role owning the database or remove the user from all the roles it attached to.

Now I have a database running and ready to use.

Sources

TODO

  • Backup and restoration procedure

  • Setup the database service for remote connection

Updates

  • 2022 Apr 20: Fix a typo

  • 2022 Apr 21:

    • Fix the service command execution order

    • Apply the correct document covention on SQL commands