Roles de la base de datos y control de acceso

Diseño de bases de datos

Lis Sulmont

Curriculum Manager

Conceder y revocar el acceso a una vista

GRANT privilege(s) o REVOKE privilege(s)

ON object

TO role o FROM role

Privilegios: SELECT, INSERT, UPDATE, DELETE, etc.
Objetos: tabla, vista, esquema, etc.
Roles: un usuario de la base de datos o un grupo de usuarios de la base de datos.

GRANT UPDATE ON ratings TO PUBLIC; 
REVOKE INSERT ON films FROM db_user;

Roles de la base de datos

Gestionar los permisos de acceso a la base de datos
Un rol de base de datos es una entidad que contiene información que:
- Define los privilegios del rol.
  - ¿Puedes iniciar sesión?
  - ¿Puedes crear bases de datos?
  - ¿Puedes escribir en las tablas?
- Interactúa con el sistema de autenticación del cliente.
  - Contraseña
Los roles pueden asignarse a uno o varios usuarios.
Los roles son globales en toda la instalación del clúster de bases de datos.

Crear un rol

Rol vacío

CREATE ROLE data_analyst;

Roles con algunos atributos establecidos

CREATE ROLE intern WITH PASSWORD 'PasswordForIntern' VALID UNTIL '2020-01-01';

CREATE ROLE admin CREATEDB;

ALTER ROLE admin CREATEROLE;

¹ http://bit.ly/postgresql_attributes

GRANT y REVOKE privilegios de los roles

GRANT UPDATE ON ratings TO data_analyst;

REVOKE UPDATE ON ratings FROM data_analyst;

Los privilegios disponibles en PostgreSQL son:

SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, y USAGE

¹ http://bit.ly/postgresql_privileges

Usuarios y grupos (ambos son roles)

Un rol es una entidad que puede funcionar como usuario y/o grupo.
- Roles de usuario
- Roles de grupo

Gráfico de roles en el que el círculo exterior representa un rol de grupo y los tres círculos interiores dentro del círculo exterior representan roles de usuario

Usuarios y grupos (ambos son roles)

Rol de grupo

CREATE ROLE data_analyst;

Rol de usuario

CREATE ROLE intern WITH PASSWORD 'PasswordForIntern' VALID UNTIL '2020-01-01';

Usuarios y grupos (ambos son roles)

Rol de grupo

CREATE ROLE data_analyst;

Rol de usuario

CREATE ROLE alex WITH PASSWORD 'PasswordForIntern' VALID UNTIL '2020-01-01';

GRANT data_analyst TO alex;

REVOKE data_analyst FROM alex;

Roles comunes de PostgreSQL

Función	Acceso permitido
pg_read_all_settings	Lee todas las variables de configuración, incluso aquellas que normalmente solo son visibles para los superusuarios.
pg_read_all_stats	Lee todas las vistas pg_stat_* y utiliza diversas extensiones relacionadas con estadísticas, incluso aquellas que normalmente solo son visibles para los superusuarios.
pg_signal_backend	Envía señales a otros backends (por ejemplo: cancelar consulta, terminar).
Más...	Más...

¹ http://bit.ly/default_roles_postgresql

Ventajas y desventajas de los roles

Ventajas

Las funciones siguen vigentes después de eliminar a los usuarios.
Los roles se pueden crear antes que las cuentas de usuario.
Ahorra tiempo a los administradores de bases de datos

Escollos

A veces, un rol otorga demasiado acceso a un usuario específico.
- Es importante prestar atención.

¡Vamos a practicar!

Diseño de bases de datos