Pedro El Pheriko Moreno

El otro dia me solicito una programadora si se podia crear un usuario en PostgreSQL que solo pudiera ejecutar “SELECT” tipo Solo-Lectura.

Como sabran cada manejador de BD tiene su manera de controlar los accesos a las bases de datos, era una solicitud algo peculiar pero como dicen por ahi…todo tiene un porque y ella tenia sus motivos.

Ahora esto que voy hacer lo hare sobre un PostgreSQL version 10 corriendo bajo Ubuntu 18, sin embargo tambien aplica a la misma version corriendo bajo Ubuntu 20.04, todas las versiones que yo uso son las LTS version servidor.

Los permisos van a ir sobre una base de datos en especifico no sobre todas las bases de datos, recuerden solo abrir lo que se necesita.

Este es el usuario que vamos a crear y su clave de acceso:

Nombre: usuario1

Clave de acceso: T#m$2G7v

Base de Datos: purchase

Aqui van los comandos los cuales se ejecutaran ya dentro de la consola de administración de PostgreSQL como lo muestra la figura 1.

1; Creamos el usuario.

postgres=# CREATE USER usuario1;
CREATE ROLE

2; Le asignamos clave a el usuario ya que nos vamos a conectar remotamente con pgAdmin.

postgres=# ALTER USER usuario1 with PASSWORD 'T#m$2G7v';
 ALTER ROLE

3; Le damos acceso a el SCHEMA public.

postgres=# GRANT USAGE ON SCHEMA public TO usuario1;
 GRANT

4; Le asignamos el acceso de conexión a la base de datos deseada.

postgres=# GRANT CONNECT ON DATABASE "purchase" TO usuario1;
 GRANT

5; Nos conectamos a la base de datos deseada.

postgres=# \c purchase;
 You are now connected to database "purchase" as user "postgres".

6; Revisamos que privilegios tiene este usuario.

purchase=# select * from information_schema.role_table_grants where grantee='usuario1';
  grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
 ---------+---------+---------------+--------------+------------+----------------+-------------
 (0 rows)

Como podemos observar, no tiene ningun privilegio sobre la base de datos.

7; Asignacion del privilegio “SELECT” para la base de datos en cuestión.

purchase=# GRANT SELECT on ALL TABLES IN SCHEMA public TO usuario1;
 GRANT

8; Repetimos el paso 6 para ver si ya tiene privilegios asignados de nuevo.

Como pueden ver ya muestra el privilegio “SELECT” para todas las tablas de la base de datos ‘purchase’, no muestra todo por que son mas de 400+, solo una parte.

9; Salimos de la consola de PSQL y nos regresamos a consola de root.

\q
exit

Ahora debemos llevar a cabo el cambio en la configuración de PostgreSQL para que el usuario ‘usuario1’ pueda conectarse remotamente con su contraseña, para estos debemos editar el archivo ‘pg_hba.conf’ de PostgreSQL.

Los programadores trabajan tanto dentro de la red como por VPN:

LAN 192.168.2.0/24

VPN 10.0.99.0/24

10; Agregando Permisos de acceso a usuario1 en PostgreSQL.

Ahora si abrimos el archivo ‘pg_hba.conf’ con nuestro editor favorito y agregamos nuestros accesos a el ‘usuario1’ y la base de datos ‘purchase’ atraves de las 2 redes mencionadas.

vim /etc/postgresql/10/main/pg_hba.conf

Las ultimas 2 lineas se agregaron.

Reiniciamos el servicio para que tome los cambios.

systemctl restart postgresql

11; Acceso remoto atraves de pgAdmin.

11.1 Creamos servidor.

11.2 Agregamos los parametros de la conexión y datos del usuario deseado.

Si todo en orden, no les deba arrojar ningun error, de lo contrario revisar mensaje a ver si no se equivocaron en la clave, datos del servidor, error de tecla, nombre de la base de datos, etc. En mi caso nada erroneo y me regresa a la pantalla inicial de pgAdmin.

No se asusten cuando expandan la lista, si intentan acceder a otras bases de datos les arrojara algo similiar a esta pantalla.

11.3 Expandimos nuestra base de datos hasta dar con las tablas.

Nos marca 437 tablas.

12; Prueba de query ‘SELECT’ en tabla ‘account_account’.

En el menu nos vamos a tools -> Query Tool.

Ejecutamos un query ‘SELECT *..”.

Perfecto el “SELECT” funciono, tenemos resultados.

Ahora tratemos de crear un UPDATE, no debe funcionar…

Perfecto, no dejo ejecutar UPDATE ya que el usuario no tiene ese privilegio sobre esta base de datos y mucho menos acceso a las demas bases de datos.

Que nos dice los logs de postgresql?

13; Fin.

Resumen.

Lo solicitado por la programadora quedo resuelto, probamos el ABC y no permitio agregar, modificar ni actualizar ninguna tabla de la base de datos.

Podemos decir nuestro usuario Solo-Lectura esta funcionando.

Si alguien por ahi tenia este pendiente espero les funcione y sigamos aprendiendo PostgreSQL, nos vemos pronto!!!