Instalación de PostgreSQL y configuraciones para su 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.
Índice de contenido
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:
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';
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
1 2 3 4 5 6 7 8 9 10 |
root@postgresql:/home/vagrant# su postgres postgres@postgresql:/home/vagrant$ psql psql (9.4.9) Type "help" for help. postgres=# CREATE DATABASE arriquitraum; CREATE DATABASE postgres=# GRANT ALL PRIVILEGES ON DATABASE arriquitraum TO cliente; GRANT postgres=# |
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_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
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
Referencias
Muchas gracias por esta guía, me ha sido de gran ayuda.
Muchas gracias excelente Guia
Gracias! me ayudó bastante.
Gracias amigo, como le haría para usar esa base de datos alojada en otro servidor para usarlo con PHP y http?
Muchas gracias por tu guia, esta buenisima!
Excelente gia, gracias por tu tiempo y por compartir
Disculpa y como se puede hacer para que te pida contraseña desde la línea de comando pgsql