Crear un usuario de solo lectura en PostgreSQL

1. Otorgar Seleccionar/Uso a una sola tabla
2. Múltiples Tablas/Vistas para PostgreSQL Versión 9.0+
3. Crear usuario de solo lectura
4. Conecte Postgres a Redash

Las bases de datos de usuario de solo lectura son beneficiosas para los informes porque tienen acceso limitado de solo lectura a la base de datos y su tabla y campos.

Un usuario de solo lectura de Postgres significa que el usuario solo puede leer información de la base de datos y no puede escribir en ella. Entonces, primero, aprendamos cómo crear un usuario de solo lectura de Postgres.

Otorgar Seleccionar/Uso a una sola tabla

Si ha tenido un historial con un usuario en Postgres, probablemente haya visto los comandos GRANT y REVOKE. Estos han demostrado ser la base fundamental del control de acceso en Postgres.

Si bien es confuso lidiar con ellos, Postgres ofrece varias configuraciones de control de acceso para que pueda navegar a través de ellos con cuidado. Otra cosa a tener en cuenta acerca de estos comandos es que no se aplican a nuevos objetos como tablas introducidas dentro de una base de datos.

Hay privilegios predeterminados en cuestión cuando se trata de esto, y Postgres aplica estos privilegios a un rol cuando se crea un nuevo objeto. Sin embargo, puede modificar estos privilegios predeterminados utilizando el comando ALTERAR PRIVILEGIOS POR DEFECTO.

Por lo tanto, si solo otorga CONECTAR a una base de datos, el usuario puede conectarse a la base de datos pero no tiene otros privilegios. Aquí, debe hacer un esfuerzo adicional y otorgar USO en espacios de nombres/esquemas e individualmente SELECCIONAR en tablas y vistas.

Podemos hacer eso de la siguiente manera:

GRANT CONNECT ON DATABASE databasename TO user; -- databasename = your database name
GRANT USAGE ON SCHEMA public TO user;
GRANT SELECT ON tablename TO user;

Múltiples Tablas/Vistas para PostgreSQL Versión 9.0+

Suponga que tiene las últimas versiones de PostgreSQL descargadas. En ese caso, es posible que otorgues fácilmente permisos sobre objetos en el esquema como tablas/vistas, etc., usando solo un comando en lugar de escribirlos uno por uno de la siguiente manera:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO user;

Esta línea funciona para afectar solo a aquellas tablas que ya han sido creadas. PostgreSQL también le da el poder de asignar roles predeterminados a nuevos objetos automáticamente en el futuro de la siguiente manera:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO user;

Es esencial tener en cuenta que el usuario que emitió este comando en primer lugar será la única persona que podrá asignar estos roles predeterminados a los objetos y tablas.

Sin embargo, podemos configurarlo en cualquier función en la que el usuario emisor sea el miembro. Al mismo tiempo, no toma los privilegios predeterminados de todos los roles de los que es miembro al crear el nuevo objeto.

Puede adoptar un enfoque en el que la base de datos tenga un rol de propietario, de modo que los cambios de esquema se realicen en ese rol de propietario específico, de modo que asigne privilegios predeterminados a ese rol de propietario.

Múltiples Tablas/Vistas para versiones de PostgreSQL anteriores a la 9.0

Si bien las funciones no están disponibles en las versiones anteriores de PostgreSQL, aún es posible crear usuarios de Postgres de solo lectura.

En cambios de varias tablas de longitud, se recomienda utilizar un proceso automático para generar la SELECCIÓN DE CONCESIÓN necesaria para cada vista/tabla para evitar errores.

Se hace de la siguiente manera:

SELECT 'GRANT SELECT ON ' || relname || 'TO user; '
FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE nspname = 'public' AND relkind IN ('r' ,'v' , 'S');

Esta línea de comando, cuando se ejecuta, debe generar los comandos GRANT relevantes para GRANT SELECT en todas las secuencias, tablas y vistas en público y para copiar y pegar. Sin embargo, debemos tener en cuenta que solo lo aplicaremos a aquellas tablas ya creadas.

Crear usuario de solo lectura

Ejecute el siguiente código para crear un usuario de solo lectura:

CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'pass321.'
SUPERUSER INHERIT
NOCREATEDB NOCREATEROLE NOREPLICATION
VALID UNTIL
'infinity';

Puede proceder a otorgar permiso al usuario de solo lectura de Postgres que acaba de crear:

GRANT CONNECTION ON DATABASE databasename TO Read_Only_User;
GRANT USAGE ON SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read_Only_User;

Sin embargo, estos comandos solo funcionan en las tablas que ya se han creado en la base de datos. Para asignar permisos a todas las nuevas tablas creadas en el futuro:

ALTER DEFAULT PRIVILEGES IN SCHEMA
public GRANT SELECT ON TABLES TO Read_Only_user;

Es importante tener en cuenta que las instrucciones desde GRANT USAGE en adelante que otorgan permiso en las tablas deben ejecutarse en la base de datos deseada. De lo contrario, los permisos no funcionarán.

Para lograr esto, puede ingresar \connect databasename; para conectar la base de datos.

Conecte Postgres a Redash

Sin los comandos GRANT/USAGE, los permisos no funcionan. Aquí, USO permite el acceso a objetos contenidos en un esquema específico siempre que se cumplan los requisitos de privilegio del objeto. Primero, cree un rol de solo lectura:

CREATE ROLE myapp_readonly; 
GRANT CONNECT ON DATABASE database default TO myapp_readonly;
GRANT USAGE ON SCHEMA myapp TO myapp_readonly;

Proceda a otorgar permiso para el rol recién creado. Finalmente, cree un usuario con redash y asígnele la función de solo lectura una vez que haya terminado de conectarse a la base de datos de Postgres con el nuevo usuario de solo lectura.

La creación de un usuario de solo lectura de Postgres demuestra ser una gran ventaja al manejar la base de datos. Sin embargo, dependiendo de las versiones instaladas, la dificultad de la tarea puede variar.

Este artículo cubre todas las bases para las diferentes versiones de Postgres para asegurarse de que está bien en la creación de un usuario de solo lectura de Postgres. Eso no solo le permite crear un usuario con derechos de acceso para leer los datos, sino que evita que el usuario realice ediciones.