Fecha: 2019-09-09 Tiempo de lectura: 10 minutos Categoría: Sistemas Tags: introducción / postgresql
Cuando MongoDB decidió cambiar la licencia por una que no cumple los criterios básicos de software libre, muchos decidieron abandonar el barco, siendo las principales distribuciones de linux las primeras en hacerlo. No faltaron voces que cantaran las maravillas de PostgreSQL, y como soy curioso, le he dado un intento.
Para esta batería de pruebas, he utilizado dos servidores, ambos con Debian:
Asumiremos que ambos tienen las herramientas cliente, y que database ejecuta el servidor.
gerard@database:~$ sudo apt install postgresql postgresql-client
...
gerard@database:~$
gerard@server:~$ sudo apt install postgresql-client
...
gerard@server:~$
Por defecto, cuando se instala una base de datos en Debian, la política es que escuche solamente en la interfaz de red localhost; esto se hace para evitar exponerlo a otros vecinos de la red, a menos que el administrador sepa lo que hace y lo modifique.
Esto se puede hacer fácilmente con la directiva de configuración listen_addresses, que
podemos poner como '*' para que escuche en todas. Para que aplique el cambio, es necesario
que se reinicie el servicio.
gerard@database:~$ grep ^listen /etc/postgresql/11/main/postgresql.conf
listen_addresses = '*'
gerard@database:~$
gerard@database:~$ sudo systemctl restart postgresql
gerard@database:~$
Ahora ya podemos comprobar que la base de datos escucha en todas las direcciones IP (0.0.0.0):
gerard@database:~$ ss -lnt
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 0.0.0.0:5432 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 128 [::]:5432 [::]:*
gerard@database:~$
Si intentamos acceder de forma remota, veremos que no nos deja; esto es porque el servidor implementa una restricción de acceso propia, que debe configurarse.
gerard@server:~$ psql -h database -U postgres
psql: FATAL: no hay una línea en pg_hba.conf para «10.0.0.243», usuario «postgres», base de datos «postgres», SSL activo
FATAL: no hay una línea en pg_hba.conf para «10.0.0.243», usuario «postgres», base de datos «postgres», SSL inactivo
gerard@server:~$
Estas reglas se guardan en el fichero pg_hba.conf, que viene comentado y es bastante
fácil de seguir. En un ejercicio de minimalismo, ya he dejado lo siguiente:
gerard@database:~$ sudo egrep -v '^[[:space:]]*(#|$)' /etc/postgresql/11/main/pg_hba.conf
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
host all all all md5
gerard@database:~$
Básicamente indica que se deja entrar sin contraseña a todos los usuarios des del servidor local (a través de IP o de unix socket) y requiere contraseña para todo el resto.
Para aplicar los cambios, basta con enviar un SIGHUP al proceso principal de postgresql.
Para ello, listaremos los procesos; el proceso principal es aquél cuyo padre es el PID 1,
siendo este el padre del resto. Acto seguido, le mandamos el signal.
gerard@database:~$ ps -eo ppid,pid,args | grep postgres
1 2166 /usr/lib/postgresql/11/bin/postgres -D /var/lib/postgresql/11/main -c config_file=/etc/postgresql/11/main/postgresql.conf
2166 2168 postgres: 11/main: checkpointer
2166 2169 postgres: 11/main: background writer
2166 2170 postgres: 11/main: walwriter
2166 2171 postgres: 11/main: autovacuum launcher
2166 2172 postgres: 11/main: stats collector
2166 2173 postgres: 11/main: logical replication launcher
389 2248 grep postgres
gerard@database:~$
gerard@database:~$ sudo kill -HUP 2166
gerard@database:~$
Y con esto podemos comprobar que se puede acceder desde local sin password, y que se puede acceder desde un servidor remoto con la contraseña que toque.
gerard@database:~$ psql -U postgres
psql (11.5 (Debian 11.5-1+deb10u1))
Digite «help» para obtener ayuda.
postgres=# exit
gerard@database:~$
gerard@server:~$ psql -h database -U postgres
Contraseña para usuario postgres:
psql: FATAL: la autentificación password falló para el usuario «postgres»
FATAL: la autentificación password falló para el usuario «postgres»
gerard@server:~$
TRUCO: El usuario administrativo no tiene una contraseña, precisamente para evitar una intrusión remota. Si se necesita acceder remotamente con este usuario, hay que darle una contraseña adecuada, como sigue:
gerard@database:~$ psql -U postgres
psql (11.5 (Debian 11.5-1+deb10u1))
Digite «help» para obtener ayuda.
postgres=# alter user postgres with encrypted password 's3cr3t';
ALTER ROLE
postgres=# exit
gerard@database:~$
Ahora ya deberíamos poder acceder remotamente con la contraseña indicada.
Vamos a crear un usuario y una base de datos para hacer las pruebas relevantes. Una propiedad nada deseable de postgresql es que cualquier usuario tiene permisos para hacer lo que quiera en el schema public, lo que no es lo recomendable.
Como ya sabéis, soy un firme defensor de la separación forzada de aplicaciones y usuarios, con lo que voy a revocar todos estos permisos, y se los voy a conceder al usuario responsable de cada aplicación.
gerard@database:~$ psql -U postgres
psql (11.5 (Debian 11.5-1+deb10u1))
Digite «help» para obtener ayuda.
postgres=# create database kittendb;
CREATE DATABASE
postgres=# revoke all on database kittendb from public;
REVOKE
postgres=# create user kittenuser with encrypted password 'kittenpass';
CREATE ROLE
postgres=# grant all privileges on database kittendb to kittenuser;
GRANT
postgres=# exit
gerard@database:~$
Ahora ya deberíamos poder usar el nuevo usuario para acceder al la base de datos, ya sea de forma remota (con contraseña) o local (sin ella), tal como indicamos en la configuración del control de acceso.
gerard@server:~$ psql -h database -U kittenuser -d kittendb
Contraseña para usuario kittenuser:
psql (11.5 (Debian 11.5-1+deb10u1))
conexión SSL (protocolo: TLSv1.3, cifrado: TLS_AES_256_GCM_SHA384, bits: 256, compresión: desactivado)
Digite «help» para obtener ayuda.
kittendb=> exit
gerard@server:~$
gerard@database:~$ psql -U kittenuser -d kittendb
psql (11.5 (Debian 11.5-1+deb10u1))
Digite «help» para obtener ayuda.
kittendb=>
Normalmente vamos a dar unas credenciales específicas a los desarrolladores de las aplicaciones, y es responsabilidad de estos lanzar las operaciones que crean oportunas. Vamos a lanzar algunas operaciones solo para ver que todo funciona como debe.
Vamos a aprovechar que tenemos un usuario nominal y vamos a utilizarlo, por ejemplo, en remoto. Como el usuario se creó para esta base de datos concreta, vamos a utilizarlo. Abriremos un shell de postgresql desde donde haremos el resto de operaciones.
gerard@server:~$ psql -h database -U kittenuser -d kittendb
Contraseña para usuario kittenuser:
psql (11.5 (Debian 11.5-1+deb10u1))
conexión SSL (protocolo: TLSv1.3, cifrado: TLS_AES_256_GCM_SHA384, bits: 256, compresión: desactivado)
Digite «help» para obtener ayuda.
kittendb=>
Vamos a crear dos tablas, que nos van a ayudar a visualizar el ejemplo: se trata de un sistema en el que tenemos usuarios y gatitos, indicando cada gatito su propietario. No es gran cosa, pero expresa varias tablas y una relacion entre ellas vía una foreign key.
kittendb=> CREATE TABLE owners (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE
kittendb=> CREATE TABLE kittens (id SERIAL PRIMARY KEY, name TEXT, owner_id INTEGER REFERENCES owners (id));
CREATE TABLE
kittendb=>
Vamos a declarar algunos propietarios de gatitos, por poner algún dato en la base de datos:
kittendb=> INSERT INTO owners (name) VALUES ('Bob');
INSERT 0 1
kittendb=> INSERT INTO owners (name) VALUES ('Alice');
INSERT 0 1
kittendb=> SELECT * FROM owners;
id | name
----+-------
1 | Bob
2 | Alice
(2 filas)
kittendb=>
De la misma forma, vamos a poner gatitos, dos para Bob y uno para Alice:
kittendb=> INSERT INTO kittens (name, owner_id) VALUES ('Smirnov', 1);
INSERT 0 1
kittendb=> INSERT INTO kittens (name, owner_id) VALUES ('Eristoff', 1);
INSERT 0 1
kittendb=> INSERT INTO kittens (name, owner_id) VALUES ('Stolichnaya', 2);
INSERT 0 1
kittendb=> SELECT * FROM kittens;
id | name | owner_id
----+-------------+----------
1 | Smirnov | 1
2 | Eristoff | 1
3 | Stolichnaya | 2
(3 filas)
kittendb=>
Ahora toca hacer alguna consulta. Ya que estamos en un sistema relacional, voy a
utilizar una operación de JOIN, que nos muestre un agregado de gatitos y propietarios.
kittendb=> SELECT * FROM owners INNER JOIN kittens ON owners.id = kittens.owner_id;
id | name | id | name | owner_id
----+-------+----+-------------+----------
1 | Bob | 1 | Smirnov | 1
1 | Bob | 2 | Eristoff | 1
2 | Alice | 3 | Stolichnaya | 2
(3 filas)
kittendb=>
Funciona, así que ya podemos crear un usuario y su base de datos para un proyecto un poco más interesante, pero como indico, esto queda como responsabilidad para el desarrollador, que seguramente utilizará alguna librería tipo ORM para ello.
Siguiendo el estilo de mysql, el backup se hace con una herramienta de dump propia, que volcará un fichero SQL con los comandos necesarios para recrear la base de datos entera. Solo hay que indicar el fichero de salida, o recoger la salida estándar.
gerard@server:~$ pg_dump -h database -U kittenuser -d kittendb > kittendb.sql
Contraseña:
gerard@server:~$
Este fichero de texto está lleno de espacios y comentarios, con lo que se comprime bien;
podemos dejar el trabajo a la misma utilidad, o comprimirlo a posteriori con gzip,
bzip2 o similares (es lo que hace el flag --compress).
gerard@server:~$ pg_dump -h database -U kittenuser -d kittendb --compress 9 > kittendb.sql.gz
Contraseña:
gerard@server:~$
gerard@server:~$ pg_dump -h database -U kittenuser -d kittendb | gzip -9c > kittendb.sql.gz
Contraseña:
gerard@server:~$
TRUCO: Es recomendable poner la fecha del backup en el nombre del fichero, y archivarlo en algún lugar seguro, para posibles futuros usos. Herramientas tipo cron nos pueden ayudar a lanzar scripts de backup automatizados.
Alternativamente, podemos utilizar la herramienta pg_dumpall que necesita ejecutarse
con el usuario administrador, pero permite sacar un backup de todas las bases
de datos del servidor, incluídos metadatos de usuarios.
Tras hacer el backup obtuvimos un fichero SQL, que debe ser interpretado como cualquier
otro script, con el binario psql. Solo hay que tener en cuenta dos cosas:
Por ello, vamos a destruir la base de datos original, con los permisos y usuarios necesarios.
Una vez la tengamos vacía, basta con ejecutar el comando psql con la entrada estándar
conectada al fichero de backup, o con una pipe al comando que lo descomprime.
Empezaremos eliminando la base de datos que no deseamos conservar; para ello necesitamos acceder con el usuario administrador de la base de datos, sea de forma local o remota:
postgres=# drop database kittendb;
DROP DATABASE
postgres=#
Creamos la base de datos de nuevo, dejándola vacía y sin acceso a nadie:
postgres=# create database kittendb;
CREATE DATABASE
postgres=# revoke all on database kittendb from public;
REVOKE
postgres=#
TRUCO: Si estuviéramos en otro servidor de postgresql o hubiéramos borrado el usuario kittenuser, también tendríamos que crearlo en este paso; es necesario para restablecer el backup y para entrar luego para utilizar la base de datos.
Ahora que tenemos un usuario adecuado para trabajar con esta nueva base de datos, vamos a darle permisos para hacer con ella lo que quiera; ya de paso, haremos la restauración con este mismo usuario, que puede escribir las tablas y filas sin problemas.
postgres=# grant all privileges on database kittendb to kittenuser;
GRANT
postgres=#
Solo nos queda ejecutar el script de backup a través del shell de postgresql:
gerard@server:~$ zcat kittendb.sql.gz | psql -h database -U kittenuser -d kittendb
Contraseña para usuario kittenuser:
...
gerard@server:~$
Y con esto ya tenemos nuestros datos restablecidos.
PostgreSQL nos ofrece un motor de base de datos completo y relativamente poco complejo. Sin embargo, se trata de un modelo de bases de datos relacionales, que nada tiene que ver con MongoDB; más que hablar de sustituirlo, lo correcto sería hablar de complementarlo.
Sigue teniendo su encanto y sirve en la mayoría de casos de uso necesarios; por lo que he podido leer, hay administradores que le sacan un rendimiento más que aceptable, sin renunciar el modelo relacional con el que todo el mundo está cómodo y acostumbrado.