Cómo crear un usuario de sólo lectura en PostgreSQL

En este artículo, veamos cómo crear un usuario de solo lectura en el motor PostgresSQL.

Las consideraciones que debemos tener en este punto, son los permisos que otorga Postgres por defecto, particularmente al usuario/grupo/palabra clave public.

Ver detalle en la siguiente entrada Permisos en PostgreSQL.

Manos al código (trabajando desde PSQL).

Creemos una base de datos para trabajar:

=> CREATE DATABASE prueba;

Ahora creemos un usuario para jugar:

=> CREATE USER pruebauser WITH PASSWORD 'Una password fuerte por favor';

Ahora, empecemos a tunear los permisos.

Por seguridad, revocamos los permisos al public para que ningun usuario tenga acceso a la base de datos, salvo los que especifiquemos.

=> REVOKE ALL ON DATABASE prueba FROM public;

Listo, paso siguiente es darle privilegios a nuestro usuario de sólo lectura para que pueda conectarse a la base de datos:

=> GRANT CONNECT ON DATABASE prueba TO pruebauser;

El usuario ya puede conectarse a la base de datos,  pero cuidado, el public tiene permisos por defecto sobre el esquema público, con lo cual, el usuario puede crear estructuras e insertarle datos. Quitemos los permisos al public y asignemos permisos USAGE al usuario para que pueda ver  los objetos del esquema.

=> REVOKE ALL ON SCHEMA public FROM public;
=> GRANT USAGE ON SCHEMA public TO pruebauser;

Ahora demosle permisos para consultar todas las tablas (incluye vistas) EXISTENTES en la base (suponiendo que estamos en el esquema public).

=> GRANT SELECT ON ALL TABLES IN SCHEMA public TO pruebauser;

Nótese el EXISTENTES en mayúsculas. Cuando se otorga un GRANT, éste solo se aplica a los objetos que existen actualmente en la base de datos, por lo cual, cuando se crea un objeto nuevo, es necesario volver a otorgar los permisos al usuario. Esto, en una base de gran tamaño, muchas tablas y cambios constantes, puede volverse inmanejable. Por suerte para nostros, Postgres nos ofrece una solución.

ALTER DEFAULT PRIVIEGES: con esta sentencia podemos especificarle al motor que, en cada objeto nuevo que se cree del tipo determinado, se otorguen determinados privilegios. Siguiendo el ejemplo, vamos a decirle al motor que, para toda nueva tabla, se le otorguen privilegios de lectura al usuario pruebauser.

=> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO pruebauser;

De esta forma ya contamos con un usuario que sólo puede consultar datos en las tablas.

Tip: En PSQL, con el comando \z podemos ver qué permisos tiene un usuario sobre determinada tabla.