Uso de PostgreSQL en OpenBSD

Instalación de paquetes

Primero buscamos los paquetes a instalar y corroboramos la versión disponible:

$ pkg_info -Q postgresql
...
postgresql-client-14.3
postgresql-contrib-14.3
postgresql-docs-14.3
postgresql-ip4r-2.4.1pl1
postgresql-odbc-13.02.0000
postgresql-pg_upgrade-14.3
postgresql-pllua-2.0.10
postgresql-plpython-14.3
postgresql-plr-8.4.1
postgresql-previous-13.5p0
postgresql-server-14.3p1
postgresql_autodoc-1.40p1
...

Para instalar la versión 14.3 de postgresql se utiliza

$ doas pkg_add postgresql-server postgresql-client postgresql-contrib

y si se quiere tener la documentación localmente:

$ doas pkg_add postgresql-docs w3m

Creación de base de datos

Comenzamos leyendo el README del paquete, /usr/local/share/doc/pkg-readmes/postgresql-server.

SIEMPRE LEER EL README DEL PAQUETE PRIMERO.

En este se indican los pasos para crear una base de datos por defecto:

# su - _postgresql
$ mkdir /var/postgresql/data
$ initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -E UTF8 -W

Desglosando las opciones de initdb utilizadas se tiene:

-D /var/postgresql/data: directorio donde se creará el cluster de la base de datos
-U postgres: nombre de usuario del superusuario de la base de datos
-A scram-sha-256: método de autenticación por defecto para los usuarios locales utilizados en el archivo pg_hba.conf.
-E UTF8: encoding de la template database y encoding por defecto que será utilizado para cualquier base de datos que se cree luego.
-W: pedir password para el superusuario de la base de datos.

Se utilizará una ligera variación del comando anterior:

Se cambiará el directorio del cluster de la base de datos. Estando acostumbrado a utilizar debian, creo que el esquema utilizado por debian es superior. A continuación se copia parte de la wiki donde se indican los archivos utilizados:

File locations

Debian splits the database configuration from the database files, opposed to generic PostgreSQL installation that puts everything under same directory. Note that Debian allows multiple clusters and even different versions of PostgreSQL to co-exist in same host.

Configuration files: /etc/postgresql/[version]/[cluster]/ Binaries: /usr/lib/postgresql/[version] Data files: /var/lib/postgresql/[version]/[cluster]

Log files: Installing PostgreSQL creates log directory /var/log/postgresql/. Starting the database engine creates log file with name postgresql-[version]-[cluster].log.

La ventaja de debian es explicita la versión utilizada de postgresql y está pensado para tener más de un cluster, siendo main el nombre del cluster por defecto.

Por esto se utilizará como esquema del nombre de directorio para el cluster /var/postgresql/data-[version]-[cluster] y en este caso particular queda como /var/postgresql/data-14-main.

Se agrega la opción --locale=C para explicitar la localización utilizada.

Este es el mismo locale creado más arriba, ya que del manual de su:

-     Same as the -l option (deprecated).
-l    Simulate a full login.  The shell of the target login is invoked
      and the current working directory is changed to the home
      directory of the target login.  HOME, SHELL, LOGNAME, and USER
      are set to the default values for the target login.  PATH and the
      umask(2) value are set according to login.conf(5).  Except for
      preserving TERM, the rest of the environment is discarded.

y si no se explicita el locale en el entorno por defecto es C pero ya que el locale es importante y a tener en cuenta deseo explicitarlo. En caso de que se requiera otra localización se debe reemplazar --locale=C por la opción --locale=locale o por las opciones --lc-*=locale.

Se utilizará la opción --pwfile=filename en lugar de -W, para obtener el password desde un archivo. Este password será aleatorio y no se utilizará a futuro, ya que en lugar de tener que recordar el password para el usuario postgres prefiero conectarme mediante el usuario _postgresql del sistema operativo.

Esto se configurará más adelante.

De todo lo anterior, creamos el cluster utilizando:

$ doas su -l _postgresql
$ pwfile="$(mktemp -p "$HOME")"
$ cat /dev/urandom | tr -dc '[:print:]' | fold -w 64 | head -n 1 > "$pwfile"
$ mkdir /var/postgresql/data-14-main
$ initdb -D /var/postgresql/data-14-main -U postgres -A scram-sha-256 -E UTF8 --locale=C --pwfile="$pwfile"
The files belonging to this database system will be owned by user "_postgresql".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/postgresql/data-14-main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 20
selecting default shared_buffers ... 128MB
selecting default time zone ... America/Montevideo
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D /var/postgresql/data-14-main -l logfile start

$ rm "$pwfile"

Para guardar los logs se utilizará también el esquema seguido en debian: como directorio /var/log/postgresql/ y como nombre de archivo postgresql-[version]-[cluster].log:

$ doas mkdir /var/log/postgresql
$ doas chown _postgresql:_postgresql /var/log/postgresql

Configuración para autenticar al usuario postgres sin password

Como se mencionó previamente, es posible contectarse con el superusuario de la base de datos sin utilizar un password mediante la autenticación de tipo Peer. En este tipo de autenticación utiliza el nombre de usuario del sistema operativo para validar el usuario y permite opcionalmente realizar un mapeo (usuario local, usuario db).

Ya que el usuario en el sistema es _postgresql y el usuario en la base de datos es postgres, vamos a necesitar realizar el mapeo como se indica la documentación sobre el mapeo de usuarios agregando al archivo pg_ident.conf la linea:

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
map-postgres    _postgresql             postgres

Notar que llamamos a dicho mapeo map-postgres.

Ahora, pasamos a editar el archivo pg_hba.conf para agregar al principio la linea:

# TYPE  DATABASE        USER            METHOD          OPTIONS
local   postgres        postgres        peer            map=map-postgres

que habilita a realizar una conexión local a la base de datos postgres por el usuario postgres mediante el método peer usando el mapeo map-postgres.

TODO: Notar que al agregar dicha linea, cuando otros usuarios quieran autenticarse utilizando el usuario postgres se devuelve el mensaje:

$ psql -U postgres
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "postgres"

Queda pendiente revisar si es posible evitar dar la pista de que está habilitada la autenticación peer para el usuario postgres.

Ejecución de la base de datos - v1

El paquete postgresql-server incluye el archivo /etc/rc.d/postgresql, el cual comienza con:

$ head /etc/rc.d/postgresql
#!/bin/ksh

daemon="/usr/local/bin/pg_ctl"
daemon_flags="-D /var/postgresql/data -w -l /var/postgresql/logfile"
daemon_user="_postgresql"
daemon_timeout=300

. /etc/rc.d/rc.subr

rc_usercheck=NO

Aquí se ve que los flags por defecto implican un directorio de datos y archivo de logs distintos a los elegidos. Para habilitar el servicio y cambiar los flags debe hacerse:

$ doas rcctl enable postgresql
$ doas rcctl set postgresql flags -D /var/postgresql/data-14-main -w -l /var/log/postgresql/postgresql-14-main.log

NOTAS:

Hay que habilitar primero el servicio, ya que en caso contrario no nos deja establecer las flags con el mensaje:
```
rcctl: postgresql is not enabled
```
Cuando se deshabilita el servicio se borran los flags y al volver a habilitar el servicio hay que recordar volver a modificar los flags cada vez. Esto no está muy bueno…

No alcanza con agregar la linea
```
# postgresql_flags=-D /var/postgresql/data-14-main -w -l /var/log/postgresql/postgresql-14-main.log
```
en el archivo /etc/rc.conf.local como recordatorio, ya que el servicio también debe aparecer en pkg_scripts=.
La mejor forma para ejecutar el servicio es mediante rcctl, ya que de esta forma no tenemos que preocuparnos si el servicio está siendo ejecutado o no.

Ejecución de la base de datos - v2

Una alternativa a habilitar y definir los flags utilizando rcctl es crear un nuevo daemon control script que contenga los flags correctos, de forma que alcance con utilizar rcctl disable|enable [daemon].

Para ello se parte del archivo /etc/rc.d/postgresql instalado por el paquete postgresql-server (pkg_info -E /etc/rc.d/postgresql) y modificar la linea daemon_flags:

$ doas cp /etc/rc.d/postgresql /etc/rc.d/postgresql14_main
$ doas sed -i 's#^daemon_flags=.*$#daemon_flags="-D /var/postgresql/data-14-main -w -l /var/log/postgresql/postgresql-14-main.log"#' \
    /etc/rc.d/postgresql14_main

La ventaja de esta opción es que no es necesario tener habilitado el demonio postgresql ni tener que recordar los flags.

Otra ventaja es que podemos tener más de un cluster ya que se siguió la convención postgresql[version]_[cluster].

Creación de usuario para conectarse a la base de datos

Se comienza creando un usuario de la base de datos. En mi caso utilizaré como usuario de la base el mismo nombre de usuario del sistema.

Conectarse con el usuario de sistema sin utilizar password

Para volver a obviar el tener que ingresar un usuario/password para conectarme a la base de datos se volvera a modificar el archivo pg_hba.conf. Esta vez para agregar el método de autenticación peer para todos los usuarios del sistema y que les permita conectarse a todas las bases de datos.

# TYPE  DATABASE        USER            METHOD          OPTIONS
local   postgres        postgres        peer            map=map-postgres
local   all             jmpc            peer

Se reinicia la base:

$ doas rcctl reload postgresql14_main

NOTA: ¿Porque se especifica el usuario directamente, jmpc, en lugar de utilizar all?. Para evitar que sea creado un usuario de nombre postgres y que tenga permisos para conectarse a todas las bases de datos. Por supuesto, si root o un administrador pueden crear el usuario postgres también podrían modificar el archivo pg_hba.conf y lograr acceso al cluster…

Ahora se crea el usuario en la base de datos:

$ doas su -l _postgresql
$ createuser -U postgres -e --createdb --no-createrole --no-superuser --no-replication --login jmpc
SELECT pg_catalog.set_config('search_path', '', false);
CREATE ROLE jmpc NOSUPERUSER CREATEDB NOCREATEROLE INHERIT LOGIN NOREPLICATION;

No son necesarias todas las opciones anteriores ya que muchas estan por defecto, pero prefiero explicito sobre implícito.

Conectarse con el usuario de sistema utilizando password

Al crear el usuario se ingresa un password. Luego este es agregado en el archivo ~/.pgpass.

$ doas su -l _postgresql
$ printf 'Password:\n%s\n\n' "$(cat /dev/urandom | tr -dc '[:alpha:]' | fold -w 64 | head -n 1)"
Password:
xbRgpOWqELkaiofWhIqFquoMxOmyuRkwThgBpptvaBijgWzIYfkQsGqHWAiQZUAm
$ createuser -U postgres -e --createdb --no-createrole --no-superuser --no-replication --login --pwprompt jmpc
Enter password for new role:
Enter it again:
SELECT pg_catalog.set_config('search_path', '', false);
CREATE ROLE jmpc PASSWORD 'SCRAM-SHA-256$4096:VQ4QiExM+hVYtiFl2/XNsw==$YHeSB8L/8NAwjcFiu2YcGmzVWvZhFEteKEFE3h8W2fw=:S0q36nFJ/lrym0bPECJ0izBhgMPpZf5Zh5flIgg/ayg=' NOSUPERUSER CREATEDB NOCREATEROLE INHERIT LOGIN NOREPLICATION;

Y se agrega en /home/jmpc/.pgpass con el usuario jmpc:

$ echo 'localhost:*:*:jmpc:xbRgpOWqELkaiofWhIqFquoMxOmyuRkwThgBpptvaBijgWzIYfkQsGqHWAiQZUAm' >> ~/.pgpass
$ chmod 600 ~/.pgpass

NOTA: tener en cuenta que el formato del archivo ~/.pgpass es:

# hostname:port:database:username:password

Creación de base de datos para el usuario

Ya que le dimos al usuario jmpc permisos para crear bases de datos no es necesario crear la base utilizando el usuario postgres.

Utilizando el comando createdb tenemos:

$ createdb -e -T template1 -E UTF8 -l C -O jmpc firstdb 'First db created for testing'
SELECT pg_catalog.set_config('search_path', '', false);
CREATE DATABASE firstdb OWNER jmpc ENCODING 'UTF8' TEMPLATE template1 LC_COLLATE 'C' LC_CTYPE 'C';
COMMENT ON DATABASE firstdb IS 'First db created for testing';

Nuevamente, se intenta explicitar las opciones utilizadas al crear la base de datos. ¿Porqué template1 como template?. Según la documentación de CREATE DATABASE:

By default, the new database will be created by cloning the standard system database template1.

Y se verifica la creación y conexión a la base utilizando:

$ psql -d firstdb
psql (14.3)
Type "help" for help.

firstdb=>

Conclusiones

Creo recordar que en debian era mucho más sencillo la creación del cluster de la base de datos, ya que este se realizaba automaticamente en los scripts de post instalación. También, al corresponder el usuario del sistema operativo al superusuario del cluster de la base de datos no era necesario definir un mapeo de usuarios (pg_ident.conf).
En debian los archivos de configuración de postgresql estaban bajo /etc/postgresql y no en el directorio del cluster de la base de datos. Igualmente esto es trivial.
Es bastante duro utilizar OpenBSD, ya que este es un buen ejemplo de que hay que conocer el software que se está usando ya que los paquetes no suelen realizar una configuración automática.
Siempre hay que empezar leyendo los readme de los paquetes de OpenBSD.
¿Vale la pena el esfuerzo? ¡SIEMPRE!. No es raro tener que hacer un troubleshooting por problemas de conexión o permisos en bases de desarrollo o testing configuradas por uno mismo o por otros. Sin duda este tipo de ejercicios ayuda a entender mejor el programa.
La mayoría de las diferencias respecto a los pasos dados en el readme del paquete se debe a que se quisieron explicitar las opciones de los distintos comandos utilizados y que se eligió una ubicación no estandar (según el readme) para el cluster y el archivo de logs.

2022/06/14