Instalación de PostgreSQL y configuraciones para su acceso remoto

Configurar PostgreSQL para acceso remoto.

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 exit

Podéis ver el proceso en la captura de pantalla que adjunto:
Instalación y configuración de PostgreSQL en Debian.

Creació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';

Configuración de PostgreSQL para su acceso remoto.

Creando un usuario y asignándole una 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.

Configuración de conexiones externas a PostgreSQL.

Fichero de configuración de PostgreSQL.

Fichero de configuración pg_hba.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
Fichero de configuración de PostgreSQL.

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.service

Conexió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

Comprobación del acceso remoto a PostgreSQL.

Comprobación del acceso remoto desde la máquina cliente.

Referencias

Compartir es agradecer. Si te ha gustado... ¡compartélo!
Comparte con tus amigos










Enviar

8 Respuestas

  1. Victor dice:

    Muchas gracias por esta guía, me ha sido de gran ayuda.

  2. Alberto dice:

    Muchas gracias excelente Guia

  3. Gracias! me ayudó bastante.

  4. Luis dice:

    Gracias amigo, como le haría para usar esa base de datos alojada en otro servidor para usarlo con PHP y http?

  5. Hewerth dice:

    Muchas gracias por tu guia, esta buenisima!

  6. Kike dice:

    Excelente gia, gracias por tu tiempo y por compartir

  7. MaGo dice:

    Disculpa y como se puede hacer para que te pida contraseña desde la línea de comando pgsql

  1. 16 noviembre, 2016

    […] Por lo tanto, es necesario configurar PostgreSQL para conexiones entrantes a través de la red y asegurarse de que los usuarios constan con suficientes permisos. Todo queda recogido en la entrada: Instalación de PostgreSQL y configuraciones para su acceso remoto […]

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.