Autor: Pablo Mora H.

Fecha: 08 Octubre de 2006

Plataforma: FreeBSD 5.5-STABLE

Éste documento puede ser copiado en cualquier sitio web siempre y cuando no se modifique su contenido sin la respectiva autorización de su autor. Además se debe especificar el sitio web original del documento - en éste caso www.eldemonio.org y el nombre de su autor.

Hace algunos meses me interesé por PostgreSQL. En ese instante si mal no recuerdo existía en los ports la versión 8.0. Debido a mi inexperiencia con Base de Datos tuve bastantes inconvenientes con la instalación y configuración del mismo. La documentación que encontré, para configurarlo principalmente, era muy excasa y en google nunca dí con documentación para esa versión en particular. Hoy en día he aprendido algo más y estoy dispuesto a compartir el conocimiento adquirido con cada uno de Uds. esperando no tengan que pasar lo mismo que yo hace algunos meses ;).

En éste documento cubriré la instalación y configuración de postgresql 8.1 usando los maravillosos ports :). Como es mi costumbre también abordaré aspectos de seguridad.

Cómo está indicado más arriba, éste documento cubre la instalación en una máquina FreeBSD 5.5, pero debe servir de la misma forma para una 6.x. Requerimientos

Se debe tener en cuenta los siguientes requerimientos para llebar a cabo todo el proceso:

1) Tener privilegios de Administrador. 
2) Tener el árbol de ports actualizado. 

Lo primero que haremos será instalar nuestro servidor de base de datos.

# cd /usr/ports/databases/postgresql81-server/
# make config

Tan solo dejamos las opciones por defecto y presionamos OK.

# make install

Siempre acostumbro por experiencia a dejar el comado make clean para el final y así percatarme de todos los mensajes que me pueda dar el port una vez instalado/registrado en el sistema. El comando anterior hará que también se instale el programa databases/postgresql81-client. Así que paciencia, ya que la instalación demorará unos cuantos minutos dependiendo de la velocidad de tu computador.

# make clean

Eso es todo. Ya tenemos nuestro servidor y cliente instalado en el sistema. Si te has fijado en el mensaje después de terminada la instalación se nos indica que se debe agregar postgresql_enable=“YES” a nuestro archivo /etc/rc.conf para iniciar nuestro servidor una vez iniciado nuestro computador. Entonces el siguiente comando nos servirá:

# echo 'postgresql_enable="YES"' >> /etc/rc.conf 

Como notamos en el mensaje de instalación, primero se debe usar el comando initdb, que nos sirve para crear una nueva 'database cluster', que no es más que una colección de base de datos que serán manejadas por una única instancia del servidor.

Ejemplos de archivos de configuración se encuentran en: /usr/local/share/postgresql/

# mkdir /usr/local/pgsql/data
# chown pgsql:pgsql /usr/local/pgsql/data

Si todo sale bien ya podemos hacer uso del programa initdb de la siguiente forma:

# su -l pgsql -c "initdb -D /usr/local/pgsql/data -W -A md5"

Salida del comando anterior:

Los archivos de este cluster serán de propiedad del usuario «pgsql».
Este usuario también debe ser quien ejecute el proceso servidor.
El cluster será inicializado con configuración local es_ES.ISO8859-15.
La codificación por omisión ha sido por lo tanto definida a LATIN9.
corrigiendo permisos en el directorio existente /usr/local/pgsql/data ... hecho
creando directorio /usr/local/pgsql/data/global ... hecho
creando directorio /usr/local/pgsql/data/pg_xlog ... hecho
creando directorio /usr/local/pgsql/data/pg_xlog/archive_status ... hecho
creando directorio /usr/local/pgsql/data/pg_clog ... hecho
creando directorio /usr/local/pgsql/data/pg_subtrans ... hecho
creando directorio /usr/local/pgsql/data/pg_twophase ... hecho
creando directorio /usr/local/pgsql/data/pg_multixact/members ... hecho
creando directorio /usr/local/pgsql/data/pg_multixact/offsets ... hecho
creando directorio /usr/local/pgsql/data/base ... hecho
creando directorio /usr/local/pgsql/data/base/1 ... hecho
creando directorio /usr/local/pgsql/data/pg_tblspc ... hecho
seleccionando el valor para max_connections ... 40
seleccionando el valor para shared_buffers ... 1000
creando archivos de configuración ... hecho
creando base de datos template1 en /usr/local/pgsql/data/base/1 ... hecho
inicializando pg_authid ... hecho
Ingrese la nueva contraseña del superusuario:
Ingrésela nuevamente: 
estableciendo contraseña ... hecho
habilitando tamaño de registro ilimitado para tablas de sistema ... hecho
inicializando dependencias ... hecho
creando las vistas de sistema ... hecho
cargando pg_description ... hecho
creando conversiones ... hecho
estableciendo privilegios en objetos predefinidos ... hecho
creando el esquema de información ... hecho
haciendo vacuum a la base de datos template1 ... hecho
copiando template1 a template0 ... hecho
copiando template1 a postgres ... hecho

Completado. Puede iniciar el servidor de bases de datos usando:

    postmaster -D /usr/local/pgsql/data
o
    pg_ctl -D /usr/local/pgsql/data -l archivo_de_registro start

Expliquemos un poco lo anterior

Para poder hacer uso de PostgreSQL debemos haber creado nuestro cluster(initdb(1)) que se localizará en /usr/local/pgsql/data/. Con el argumento -D indicamos el lugar donde nuestro cluster será almacenado. La opción -W nos permitirá setear nuestra clave de super usuario llamado pgsql. Finalmente, con el argumento -A podemos indicar el método de autentificación para usuarios locales(en éste caso: md5(1)).

Para más opciones de configuración de PostgreSQL también puedes consultar los archivos /usr/local/pgsql/data/pg_hba.conf y /usr/local/pgsql/data/postgresql.conf. Conexiones seguras con SSL

PostgreSQL tiene soporte nativo para conexiones con SSL y así encriptar las comunicaciones cliente/servidor para incrementar la seguridad de las mismas. PostgreSQL instalado de los ports viene con soporte SSL por defecto lo que nos evita unos cuantos dolores de cabeza. Para usar conexiones con SSL tenemos que activar la opción ssl en /usr/local/pgsql/data/postgresql.conf. Es decir, cambiar ssl = off por ssl = on. Es importante notar que ssl = on debe quedar descomentado, en otras palabras, sin el #.

# cd /usr/local/pgsql/data/
# openssl req -new -text -out server.req

Salida del comando anterior:

Generating a 1024 bit RSA private key
....++++++
.........................++++++
writing new private key to 'privkey.pem'
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:CL
State or Province Name (full name) [Some-State]:Concepcion
Locality Name (eg, city) []:Concepcion
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Particular
Organizational Unit Name (eg, section) []:None
Common Name (eg, YOUR name) []:yo.undominio.cl
Email Address []:yo@micorreo.com

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:               
An optional company name []:

El comando anterior requerirá una frase(Recuérdala). Asegúrate de entrar el nombre de tu host como “Common Name”. El challenge password puede quedar en blanco.

# openssl rsa -in privkey.pem -out server.key

Acá debes ingresar la frase.

# rm privkey.pem

# openssl req -x509 -in server.req -text -key server.key -out server.crt
# chmod og-rwx server.key
# chown pgsql:pgsql server.*

Una vez hecho lo anterior, reiniciamos. Acostumbro a reiniciar ya que muchas veces me he encontrado con que el comando /usr/local/etc/rc.d/myport.sh start no me ha servido luego de una instalación. Por lo tanto y para asegurarnos que todo salga bien, hacemos:

# shutdown -r now

Una vez reiniciado nuestro computador comprobamos que el servidor se esté ejecutando:

# ps auxwwww | grep postmaster

Si ves procesos postmaster es que todo a salido bien. Si no es así debes revisar que hayas hecho todos los pasos anteriores correctamente.

Ahora haremos algo más entretenido. Sólo permitiremos conexiones locales encriptadas. Para ellos debemos modificar unas cuantas cositas a nuestro archivo pg_hba.conf. Para ello, abrimos ese archivo y nos vamos casi al final. Veremos lo siguiente:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

Y lo cambiamos por ésto:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local      all         all                            md5
# IPv4 local connections:
hostssl    all         all      127.0.0.1/32          md5
# IPv6 local connections:
hostssl    all         all      ::1/128               md5

Debo hacer notar que por defecto nuestro servidor sólo acepta conexiones locales, lo que personalmente me parece excelente. Para modificar ese comportamiento es necesario modificar nuestro archivo postgresql.conf. Para ello se debe modificar el valor de listen_addresses que por defecto es localhost. Para más información consultar acá.

SIEMPRE que hayamos modificado archivos de configuración debemos reiniciar nuestro servidor y así nuestros cambios tomen efecto. Para ello, el siguiente comando nos será útil:

# su -l pgsql -c "pg_ctl -D /usr/local/pgsql/data/ restart"

Como habrás adivinado, para parar nuestro servidor reemplazamos ese start por un stop. Y para iniciarlo por start. Para Finalizar

Si has llegado a éste punto felicitaciones. Ahora no queda mas que probar nuestro servidor usando el programa psql de la siguiente forma:

# psql -U pgsql template1

Te pedirá la contraseña de administrador y si vez algo como ésto: Contraseña para usuario pgsql:

Bienvenido a psql 8.1.4, la terminal interactiva de PostgreSQL.
Digite:  \copyright para ver los términos de distribución
       \h para ayuda de comandos SQL
       \? para ayuda de comandos psql
       \g o or termine con punto y coma para ejecutar una consulta
       \q para salir
template1=#

Significa que todo a salido bien y ya podemos interactuar con nuestro servidor de bases de datos. Muchas Felicitaciones

Nuesto servidor por defecto escucha conexiones de clientes en el puerto 5432.

Existen 3 formas de comunicarnos con un servidor pgsql de forma encriptada. La acá expuesta es la Built-in SSL. Para más información dejo éste enlace.

Personalmente conosco 2 programas GUIs para administrar nuestras Base de Datos en PostgreSQL. La primera de ellas se llama PhpPgAdmin ubicada en databases/phppgadmin/ y la segunda se llama pgAdmin III ubicada en databases/pgadmin3/. La primera de ellas se ejecuta via web, es bastante sencilla de configurar, pero no tiene tantas opciones como la segunda. Les dejo la inquietud :D.

En un principio, el servidor tiene asignado un solo usuario, llamado pgsql, que es el login de nuestro super usuario. Para agregar más usuarios se debe usar el comando createuser(createuser(1)) y para agregar más bases de datos, el comando createdb(createdb(1)). Cabe mencionar que éstas tareas pueden ser automatizadas con los 2 programas anteriormente citados.

En /usr/local/etc/rc.d/ al instalar nuestro servidor se nos creará un script de inicio llamado 010.pgsql.sh. A través de /etc/rc.conf podemos definir ciertos parámetros que serán pasados a este script. En primer lugar podemos definir postgresql_enable=“YES” que como ya había dicho nos sirve para ejecutar nuestro servidor al iniciar el computador. También podemos definir postgresql_data=“/usr/local/pgsql/data” donde podemos indicar la ruta donde se encuentran las bases de datos que queremos inicializar - en éste caso en /usr/local/pgsql/data. Y por último postgresql_flags=“-w -s -m fast” que nos permite pasarle parámetros adicionales a nuestro servidor - en éste caso se le pasan -w -s -m fast.

Debo decir además que a través del script 010.pgsql.sh también podemos crear nuestro cluster mediante el comando # sh /usr/local/etc/rc.d/010.pgsql.sh initdb - se leerá el valor de postgresql_data en /etc/rc.conf. El inconveniente de hacer ésto último es que el método de autentificación por defectó será trust para usuarios locales, es decir, el servidor no pedirá contraseña alguna para conectarnos(ventaja para algunos y desventaja para otros). Si se desea modificar ésto tan solo se debe editar el archivo pg_hba.conf. Más parámetros que acepta el script son: start, stop, restart, rcvar, reload, status y poll. Lo Último de lo Último

Finalmente debo agradecer a acardenas y Ernest0 del canal #eldemonio en freenode por incentivarme a escribir este tutorial.

Demás está decir que cualquier tipo de comentarios y sugerencias serán muy bienvenidos.