I’m setting up a homelab and obviously some services need a database to do the job.
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=#
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;
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.
Backup and restoration procedure
Setup the database service for remote connection
2022 Apr 20: Fix a typo
2022 Apr 21:
Fix the service command execution order
Apply the correct document covention on SQL commands