2. Motor de bases de datos PostgreSQL

PostgreSQL es un motor de bases de datos relacionales (RDBMS) que verifica integridad referencial con gran funcionalidad como base de datos, aunque un poco más lenta que otros motores. Su licencia es tipo BSD. En esta sección describimos brevemente la instalación y uso en un sistema adJ.

2.1. Primera instalación del servidor

Este motor de bases de datos se instala con el archivo de ordenes /inst-adJ.sh que en instalaciones tipicas de adJ basta ejecutar y volver a ejecutar para actualizar o para volver a inicializar PostgreSQL u otro paquete de esta distribución. En caso de actualizar este archivo sacará respaldo de la información de la base de 2 formas (copiando directorios de PostgreSQL y sacando un volcado de toda la base).

A continuación se dan detalles del proceso de instalación y uso de PostgreSQL en caso de que requiera instalar por su cuenta o aprender más sobre este motor de bases de datos.

Para emplearlo por primera vez instale el paquete postgresql-server-9.3.2 (también es recomendable postgresql-docs).

Este paquete deja instrucciones específicas para inicializar la base de datos, permitir conexiones de red e inicializar la base de datos cada vez que arranque el sistema en: /usr/local/share/doc/postgresql/README.OpenBSD. Los pasos que este escrito describe son:

  • Opcional. El paquete de PostgreSQL disponible crea el usuario del sistema _postgresql, sin embargo si está actualizando o lo requiere puede crearlo con:

    # useradd -c "Administrador de PostgreSQL" -g =uid -m -d /var/postgresql \
      -s /bin/sh -u 503 _postgresql 
    # passwd _postgresql
    	  

  • A diferencia de versiones anteriores, este paquete ya no inicializa la base. Inicialicela con:

    # mkdir -p /var/postgresql/data
    # chown -R _postgresql:_postgresql /var/postgresql
    # su - _postgresql
    $ initdb -D /var/postgresql/data -Upostgres --encoding=ISO8859-1
    	    

    En adJ se emplea por defecto autenticación md5, que requiere suministrar clave cada vez que se carga la interfaz psql o abre conexiones. Si no desea autenticación md5, al inicializar la base con initdb emplee la opción --auth=trust. Una vez inicializada puede cambiar de un método a otro en el archivo /var/postgresql/data/pg_hba.conf

  • Altamente Recomendado. Agregue a /etc/sysctl.conf:

    kern.seminfo.semmni=256
    kern.seminfo.semmns=2048
    kern.shminfo.shmmax=50331648
    		  

  • Configurar servicio para que inicie en el arranque y se detenga al apagar. En /etc/rc.conf.local agregue:

    pg_ctl_flags="start \
    	-D /var/postgresql/data -l /var/postgresql/logfile \
    	-o '-D /var/postgresql/data'"
    

    y en el mismo archivo en la línea donde se define pkg_scripts agregue postgresql, así un computador donde estén configuradas las particiones cifradas, respaldo cifrado y los servicios httpd, cupsd dirá:

    pkg_scripts="cron montaencres montaencpos postgresql httpd cupsd"
    

Inicialmente el servidor queda configurado con sockets Unix (solo desde la misma máquina). Puede comprobar que está corriendo el servidor (postmaster) con:

# pgrep post
    

y revisar el socket examinando la presencia del archivo /var/www/tmp/.s.PGSQL.5432 (otra ubicación puede ser /tmp/.s.PGSQL.5432).

Para emplear el protocolo TCP/IP para conexiones desde algunas máquinas de su elección, edite /var/postgresql/data/pg_hba.conf y agregue por ejemplo máquinas y usuarios que puedan hacer conexiones. También edite /var/postgresql/data/postgresql.conf para que incluya líneas de configuración como:

max_connections = 100
port = 5432
    

Para mejorar desempeño especialmente en sitios que atiendan bastantes conexiones simultáneamente, consulte primero /usr/local/share/doc/postgresql/README.OpenBSD.

En adJ por seguridad (e.g cuando ejecuta Apache con chroot en /var/www) no se permiten conexiones TCP/IP y se emplea una ruta para los sockets diferente a la ruta por defecto (i.e /tmp), se trata de /var/www/tmp, que se define en el archivo de configuración de PostgreSQL con:

unix_socket_directory = '/var/www/tmp'

Antes de reiniciar PostgreSQL asegúrese de crear el directorio:

# mkdir	/var/www/tmp
# chmod a+w /var/www/tmp
# chmod +t /var/www/tmp
    

También tenga en cuenta que las diversas herramientas reciben como parámetro adicional -h ruta. Por ejemplo si ejecuta Apache con chroot en /var/www/ puede tener configurado su directorio para sockets en /var/www/tmp, en ese caso puede iniciar psql con la base prueba usando:

psql -h /var/www/tmp prueba

En paquetes anteriores al de adJ 4.1 el superusuario de la base coincidía con el usuario del sistema _postgresql, desde 4.1 el superusuario de la base es postgres, así que para realizar operaciones debe agregarse la opción -U postgres. El instalador de adJ seleccionará una clave con el programa apg y la dejará en el archivo /var/postgresql/.pgpass en una línea de la forma:

*:*:*:postgres:clave
	

De esta manera desde la cuenta _postgresql no necesitará dar la clave del usuario postgres.

2.2. Habilitando autenticación

Por defecto la forma de inicializar PostgreSQL no establecerá una clave de administrador ni exigirá autenticación para cuentas que se conecten desde la misma máquina. Sin embargo esto debe mejorarse si tiene varias cuentas en el mismo servidor.

Una manera es en el momento de la inicialización de la base de datos con las opciones --auth y --pwfile o -W de initdb, por ejemplo:

su - _postgresql
echo "MiClave" > clave.txt
initdb -Upostgres --auth=md5 --pwfile=clave.txt -D/var/postgresql/data
		  

que inicializará PostgreSQL con autenticación md5 y clave de administrador MiClave

Otra posibilidad es cambiar la configuración después de haber inicializado sin autenticación. Para esto cambie la clave del administrador con[29]:

# psql -U postgres template1 
template1=# alter user postgres with password 'MiClave';

Después edite /var/postgresql/data/pg_hba.conf y cambie en las lineas de acceso la palabra trusted por md5, por ejemplo:

local   all         all                               md5
host    all         all         127.0.0.1/32          md5
host    all         all         ::1/128               md5

Detenga el servidor y vuelvalo a iniciar, notará que todo intento de ingreso exige la clave.

El listado de bases de datos puede consultarse con:

SELECT * FROM pg_database ;
	

y el listado de los usuarios con:

SELECT username FROM pg_users;
	

Las claves de los diversos usuarios pueden cambiarse de forma análoga a la presentada para postgres:

ALTER ROLE pablo with UNENCRYPTED PASSWORD 'clave-plana';
	

Desde PostgreSQL 8.1 se emplea un esquema de roles que unifica los conceptos de usuario y grupo. Además de exigir clave para cada ingreso, cada rol de PostgreSQL que crea objetos puede modificar permisos para restringir o dar acceso a otros roles. Por ejemplo para restringir el acceso a una tabla cuenta:

REVOKE ALL ON cuenta FROM PUBLIC;
	

Cuando un rol crea una base de datos, queda como dueño de todas las tablas y en principio es el único que puede acceder a estas. Otro rol en principio no podrá ni siquiera examinar los datos de las tablas:

SELECT * FROM solicitud;
ERROR:  permission denied for relation solicitud
	

Para dar permiso a otro rol puede usarse:

GRANT ALL on solicitud TO rodrigo;
	

2.3. Creación de una base de datos

Para crear la base de datos prueba puede usar el superusuario con la opción -U postgres o desde una cuenta que tenga permiso para crear bases de datos:

	createdb prueba
	psql prueba

Desde la interfaz psql, pueden darse comandos SQL y otros específicos de PostgreSQL (ver Sección 2.4, “Uso de una base de datos”). En particular el usuario postgres y desde cuentas con permiso para crear usuarios, puede crear otros usuarios (globales para todas las bases de datos manejadas por el servidor). Por ejemplo para crear un usuario normal sin clave, desde psql ingresar:

	CREATE USER usejemplo
      

El comando CREATE USER presentado puede ir seguido de CREATEUSER para crear un superusuario (sin restricción alguna), o CREATEDB para crear un usuario que pueda crear bases de datos o PASSWORD 'clave' para crear un usuario con una clave (emplea autenticación configurada). Desde la línea de comandos puede crearse un usuario con:

createuser usejemplo
      

Para eliminar un usuario desde psql se usa:

DROP USER usejemplo;
      

y para eliminarlo desde línea de comandos:

dropuser usejemplo
      

Puede ejecutarse un script SQL (crea.sql) desde la línea de comandos a un base de datos con

	psql -d test -U ejusuario --password -f crea.sql
      

2.4. Uso de una base de datos

Puede emplear psql, la interfaz texto que acepta comandos SQL y que se distribuye con PostgreSQL. Para esto, entre a una base (digamos b1908) como un usuario (digamos u1908) con:

psql -U u1908 -d b1908

En esta interfaz puede dar comandos SQL y algunos comandos internos que puede listar con \h. Algunos ejemplos de operaciones útiles son:

\dt

para ver tablas disponibles.

\d usuarios

Describe la estructura de la tabla usuarios

SELECT victim_nombre,victim_apellido FROM victimas WHERE victim_edad<=12;

Que muestre los nombres de niños de 12 años o menos listados en la tabla victimas

\h update

Da ayuda sobre el comando update (que permite actualizar registros de una tabla.)

Es recomendable que los usuarios del sistema que también son usuarios de PostgreSQL creen el archivo ~/.pgpass donde puede almacenarse la clave que usa en PostgreSQL ---de forma que las diversas herramientas no la solicitaran--- con una línea de la forma:

*:*:*:usuario:clave
      

2.5. Cotejación en PostgreSQL

Desde adJ 5.2 se incluye un porte de PostgreSQL (la versión incluida en adJ 5.4 es postgresql-server-9.3.2) que soporta cotejaciones de acuerdo al locale, y por defecto se crean cotejaciones para todos los países de habla hispana, con nombres de la forma es_CO_UTF_8. Donde CO representa Colombia, pero se pueden emplear otras abreviaturas de países como se usan en Internet (estándar ISO-3166-1): ES CO PE VE EC GT CU BO HN PY SV CR PA GQ MX AR CH DO NI UY PR.

Puede examinar las cotejaciones disponibles ingresando al interprete psql y ejecutando:

SELECT * FROM pg_collation;
	

Puede crear una cotejación (digamos esp) para un locale soportado por el sistema operativo y con la misma codificación de su base de datos (digamos es_CO.ISO8859-1) con:

CREATE COLLATION esp (LOCALE='es_CO.ISO8859-1');
	

Una vez creada puede realizar operaciones empleándola por ejemplo:

SELECT 'Á' < 'B' COLLATE "esp";
...
SELECT nombre FROM clase ORDER BY nombre COLLATE "esp";
	

la primera sentencia dará:

 ?column? 
 ----------
 t
 (1 row)

Puede crear columnas de tablas especificando el tipo de cotejación por defecto para operaciones con esa columna.

CREATE TABLE ejc (
  nombre VARCHAR(100) COLLATE "esp"
);

o cambiar las existentes:

ALTER TABLE clase ALTER nombre TYPE VARCHAR(500) COLLATE "esp";

También podrá renombrar cotejaciones que haya creado con ALTER COLLATION esp RENAME TO es_CO_ISO8859_1;, así como borrarlas con DROP COLLATION esp;. Puede consultar más en http://www.postgresql.org/docs/9.1/static/collation.html.

2.6. Copias de respaldo

Para sacar una copia de respaldo de todas las base de datos manejadas con PostgreSQL (y suponiendo que el socket está en /var/www/tmp): ingrese a la cuenta del administrador:

sudo su - _postgresql
pg_dumpall -U postgres -h /var/www/tmp/ \
      --inserts --attribute-inserts > /respaldos/pgdump.sql
		  

Puede restablecer una copia con

psql -U postgres -h /var/www/tmp/ \
	-f /respaldos/pgdump.sql template1

2.7. Referencias y lecturas recomendadas

  • Documentación del paquete postgresql (README.OpenBSD, INSTALL).

  • Documentación disponible en el paquete postgresql-doc (ver [postgresql-doc]) y en http://www.postgresql.org/docs.

  • Páginas del manual de Unix: psql(1)



[29] Note que de esta forma puede cambiar la clave de otros usuarios de PostgreSQL.