Fichero pg_hba.conf de Postgresql

Como continuación del post sobre primeros pasos con Postgresql, hoy vamos a ver un poco más en profundidad el fichero pg_hba.conf que se suele ubicar en el directorio /etc/postgresql/XX/main/ (siendo XX el número de versión, por ejemplo 10 ó 9.3).

El fichero pg_hba.conf sirve para especificar los accesos a las bases de datos que permitiremos, es decir, sirve de ACL.

Si se edita el fichero, se comprobará que cada linea (no comentada) consta de una serie de campos separados por un espacio o un tabulador. El significado de los distintos campos es el siguiente:

Columna 1: TYPE

Puede tener el valor local o host.

Si utilizamos local, indicaremos que el acceso que estamos definiendo tendrá como origen el propio equipo donde está instalado el servicio Postgresql y accederemos al localhost.

Si usamos host, indicaremos que el acceso provendrá de un equipo en red, es decir, el que inicie el acceso no será la máquina donde se tenga instalado el servicio Postgresql y por tanto accederá por IP o hostname al servidor.

Accediendo a Postgresql desde Python

Continuando con la anterior entrada del blog donde se explica cómo instalar Postgresql y realizar las configuraciones iniciales básicas, vamos a ver ahora un ejemplo de conexión a esta base de datos usando Python.

Se usará Python 2.7 y para conectar con la BD se utilizará la librería Pygresql.

Primeros pasos con Postgresql

En la entrada de hoy vamos a ver cómo montar un servidor de Postgresql paso a paso para disponer de lo básico para empezar a trabajar.

Indicar que en este ejemplo se usará la distribución Ubuntu 18.04 en su edición server, aunque el proceso no variará demasiado para otras distribuciones.

Vamos a dividir en dos secciones los pasos a realizar: por un lado estará el proceso de instalación y por otro el de realización de configuraciones básicas para poder usar la base de datos.

Comandos útiles para pgpool-II

Pgpool-II es un middleware entre clientes y servidores PostgreSQL que permite montar un sistema de réplica. Si no lo conocéis os aconsejo que le echéis un vistazo. Es muy interesante y cubre necesidades que otros métodos de réplica sobre PostgreSQL no abordan.

Un aspecto negativo de este proyecto es que no hay demasiada documentación. A continuación indico algunos comandos que facilitan la administración del cluster y permiten conocer el estado de pgpool-II:

Conocer el número de conexiones disponibles

Es necesario saber cuántas conexiones hay disponibles para los clientes con el propósito de anticiparse a un 100% de uso y por tanto a no aceptar nuevas conexiones.

# ps -ef | grep "wait for connection request" | grep -v grep | grep -v PCP | wc -l
3030

Conocer el número de conexiones usadas por un equipo

Cuando se usan librerías de terceros para conectar una aplicación a una base de datos, como Hibernate, no se sabe a ciencia cierta cuántas conexiones abre contra pgpool-II. Esta métrica permite conocer mejor el uso que hacen estas librerías de las conexiones y posibilita saber cuántos clientes podemos tener conectados al mismo tiempo.

# ps -ef | grep -E "pgpool: .*192.168.46.6" | grep -v grep | wc -l

Ver el estado de los nodos configurados en el cluster

En pgpool-II cada nodo PostgreSQL añadido al cluster puede estar en uno de estos tres estados:

1. Nodo marcado como válido y sobre el que pgpool no ha escrito desde que se marcó con este estado.
2. Nodo marcado como válido y sobre el que pgpool ya ha realizado escrituras.
3. Nodo fuera de servicio, bien porque esa máquina está apagada, no tiene PostgreSQL en ejecución o sí lo está pero no tiene sus datos sincronizados con el resto de servidores válidos.

Dado que el log de pgpool-II tiene bastante verbosidad, la mejor forma de ver el estado de los servidores es el siguiente:

# tail -f /var/log/pgpool.log | grep "DB node"


Poco a poco iré poniendo más trucos sobre Pgpool-II, pues pueden resultar muy útiles para los que se inician en este mundo.

Ejecución de sentencias SQL desde scripts

En alguna ocasión nos puede interesar que algún script realice una consulta o inserción contra una base de datos. Os voy a indicar una sencilla forma de hacerlo, siempre y cuando uséis PostgreSQL como SGBD.

Lo primero de todo, será tener instalado el cliente de Postgresql, que para la versión 9.10 de Ubuntu es el siguiente paquete:

# aptitude install postgresql-client-8.3 (ó 8.4)

Tras instalarlo, deberemos tener disponible el comando psql.

Desde un script, basta con ejecutar:

$ echo "INSERT INTO inventario_macs VALUES ('192.168.1.1', '00:60:08:63:f5:4f');" | psql -h 192.1681.100 -d bd_pruebas -U upruebas

Indicar varias cosas:

• La opción -h especifica la IP o nombre del servidor que aloja la base de datos.
• La opción -d especifica la base de datos a la que queremos conectarnos (el servidor puede alojar varias bases de datos).
• La opción -U especifica el usuario con que nos vamos a conectar.

Hay que tener en cuenta que la base de datos puede estar configurada para que solicite contraseña ante un acceso (autenticación activada) o no. Si no está activa esta opción, el comando anterior funcionará sin problemas. Si estuviese activa, al ejecutar el comando psql se nos preguntará por la contraseña, por lo que no es viable para usarlo en scripts de ejecución automatizada.

Para establecer entre una máquina cliente y otra servidor un vínculo de confianza con el objetivo de que no nos pidan la contraseña por cada acceso que queramos hacer (como ocurre con ssh), realizar los siguientes pasos en la máquina cliente:

1. Crear un fichero con nombre .pgpass en el directorio home del usuario que accederá a la base de datos. Este fichero contendrá lineas con el siguiente formato: hostname:puerto:base_datos:nombre_usuario:password. En nuestro caso:

192.168.1.100:5432:bd_pruebas:upruebas:micarromelorobaron

2. Asignar los permisos adecuados al fichero:

$ chmod 0600 .pgpass

Tras ello, podremos lanzar el comando anterior pues no se volverá a preguntar el password de acceso a la base de datos.

Como añadido, indicar que es muy cómodo a la hora de crear tablas redactar en nuestro propio equipo un archivo de texto con las sentencias SQL correspondientes, y luego ejecutarlas en el servidor de la forma:

$ cat fichero.sql | psql -h 192.1681.100 -d bd_pruebas -U upruebas