PostgreSQL es un gestor de base de datos relacional. Se trata de un
proyecto de código abierto cuya primera versión fue lanzada el 1 de
agosto de 1996 y que en ocasiones es llamado de forma abreviada
“Postgres”.
Actualmente, está disponible la versión 9.4.9 para
su instalación en Debian Jessie, sistema operativo que utilizaremos como
servidor para este sistema de gestión de base de datos que
configuraremos para que ofrezca servicio de conexión remota.
Si ya tienes PostgreSQL instalado y en funcionamiento, puedes ir directamente a Configurar PostgreSQL para acceso remoto o conexiones entrantes a través de la red.
Entre los servicios de grandes organizaciones que usan o han usado
PostgreSQL están: la base de datos de virus de McAfee, el historial de
chat y llamadas de Skype, el servidor de VOIP Asterisk, el diseño de
todos los aeropuertos de Estados Unidos, la aplicación de fitness
Runkeeper… y algunas organizaciones más listadas en Usuarios destacados que usan PostgreSQL.
Instalación y configuración inicial de PostgreSQL
Comenzamos instalando el servidor PostgreSQL, en nuestro caso en una
máquina Vagrant con Debian Jessie y dirección IP 192.168.1.75. En Debian
está disponible PostgreSQL 9.4.9 que instalamos con el comando
apt-get install postgresql
Seguidamente modificamos la contraseña al usuario postgres que se crea tras la instalación del paquete del SGDB haciendo uso del comando
passwd postgres con privilegios de superusuario.
Debemos cambiar la contraseña del usuario postgres en el sistema gestor de base de datos. Por ello, nos identificamos con
su postgres y accedemos a la consola con psql
Continuamos haciendo uso de
ALTER ROLE postgres PASSWORD 'ContraseñaNueva'; para asignar una nueva contraseña al usuario dentro de PostgreSQL.
Por último, salimos de la consola con
\q y nos deslogueamos con exitCreación de usuarios y credenciales
Es necesario acceder con privilegios de root en PostgreSQL para crear usuarios y asignarle permisos. Por ello nos logueamos con
su postgres
Creamos el usuario con la ejecución de
createuser -S -R -D -l cliente
Esto creará un usuario sin permisos de superusuario, que no podrá
crear bases de datos ni tampoco roles pero sí le permitirá hacer login.
Los atributos pasados en mayúsculas a createuser implican negación, mientras que en minúsculas concesión. Puedes leer más sobre createuser aquí.
Seguidamente accedemos a la consola de comandos para asignarle una contraseña cifrada al usuario cliente con el comando
ALTER USER cliente WITH ENCRYPTED PASSWORD 'contraseña';
Por último, podemos comprobar que el usuario se ha creado correctamente consultando la tabla pg_user o pg_shadow, conteniendo esta última la contraseña de los usuarios en MD5.
Crear de una base de datos en PostgreSQL
El siguiente paso es crear una base de datos para, posteriormente, asociarle al usuario cliente todos los privilegios sobre ella.
Igualmente nos identificamos con el usuario postgres y creamos una base de datos llamada arriquitraum
En ella creamos una tabla llamada Usuarios con las columnas id y nombre:
CREATE TABLE usuarios (id VARCHAR(3), nombre VARCHAR(75)); e insertamos algunos datos en ella, pues el único fin es obtener varios datos con los que realizar pruebas de funcionamiento.Configurar PostgreSQL para acceso remoto o conexiones entrantes a través de la red
Para permitir las conexiones entrantes a través de una red en un
servidor PostgreSQL es necesario realizar unas modificaciones en los
ficheros de configuración del motor de base de datos.
Esto permitirá que un equipo cliente (como una aplicación web) tenga
acceso a nuestro sistema gestor de base de datos, pudiendo ser tan
versátil para él como privilegios se le hayan otorgado al usuario con el
que realiza la conexión.
Fichero de configuración postgresql.conf
El fichero de configuración de PostgreSQL se encuentra en la ruta /etc/postgresql/9.4/main/postgresql.conf
En él podemos establecer ajustes como la ubicación del resto de
ficheros de configuración, las conexiones y autenticaciones, los
recursos de los que dispondrá PostgreSQL…
En este fichero buscamos y descomentamos la línea
listen_addresses = 'localhost'
donde puede especificarse las direcciones IP de las que aceptará
peticiones. Estas, en caso de ser varias, han de ser separadas por
comas.
En nuestro caso tenemos interés en que atienda cualquier petición de cualquier red, por lo cual reemplazamos localhost por *, dejando la línea mencionada de esta manera:
listen_addresses = '*' guardamos y salimos del fichero.Fichero de configuración pg_hda.conf
También es necesario realizar cambios en el fichero de configuración pg_hba.conf, que es el fichero de configuración de autenticaciones de clientes PostgreSQL. Este se ubica en /etc/postgresql/9.4/main/pg_hba.conf
En él buscamos la línea que está bajo el comentario que indica la
configuración de conexiones locales con IPv4 (IPv4 local connections)
para reemplazar 127.0.0.1/32 por all
De esta forma, serían aceptadas todas las conexiones desde cualquier
dirección IP, con cualquier usuario y a cualquier base de datos siempre y
cuando tenga permisos para ello. En la captura de pantalla también he
modificado el comentario para que tenga relación con los ajustes
realizados.
Ya por último, reiniciamos el servicio de PostgreSQL
systemctl restart postgresql.serviceConexión desde un cliente a un servidor PostgreSQL remoto
Para la conexión a un servidor de base de datos PostgreSQL desde un cliente es necesario instalar el cliente de PostgreSQL en dicha máquina. En Debian Jessie ejecutamos
apt-get install postgresql-client instalar este paquete que permitirá el uso de la terminal psql.
En nuestro caso, hemos establecido conexión con la base de datos arriquitraum ubicada en el servidor con dirección IP 192.168.1.75 a través de la ejecución del comando
psql -h 192.168.1.75 -U cliente -d arriquitraum