Cómo Estructurar la Base de Datos para el Control de Accesos y Permisos para Aplicaciones en Streamlit y Python

En el desarrollo de aplicaciones modernas con Python y Streamlit, garantizar un acceso seguro y estructurado a los módulos de la interfaz es un paso fundamental antes de desplegar en producción. Cuando un dashboard o aplicación interna crece, surge rápidamente la necesidad de diferenciar qué vistas y herramientas puede utilizar un usuario según su perfil o nivel de responsabilidad.

Se tiene que tener en consideración un enfoque robusto para resolver esta necesidad es implementar un modelo de Control de Acceso Basado en Roles (RBAC, por sus siglas en inglés) directo en la base de datos relacional (como PostgreSQL o MySQL). Mediante un esquema claro de tablas, es posible separar la autenticación (quién se conecta y cómo lo hace) de la autorización (a qué secciones o páginas tiene permiso de ingresar).

En este tutorial desarrollaremos una base de datos teniendo cuenta el modelo entidad-relación para el control de accesos para una la aplicación en Streamlit, Python con una base de datos PostgreSQL.

1. Descripción General del Modelo

El esquema gestiona la autenticación (identificación del usuario) y la autorización (control de acceso basado en roles o RBAC) de la aplicación. Permite restringir vistas o funcionalidades específicas del dashboard/app en función del rol asignado a cada usuario.

2. ¿Qué es un diagrama ER?

Un diagrama de entidad-relación (ER) es un tipo de diagrama de flujo que ilustra cómo las entidades, como personas, objetos o conceptos, se relacionan entre sí dentro de un sistema. Los diagramas ER se usan con mayor frecuencia para diseñar o depurar bases de datos relacionales en los campos de ingeniería de software, sistemas de información empresarial, educación e investigación. También conocidos como ERD o modelos ER, utilizan un conjunto definido de símbolos como rectángulos, rombos, óvalos y líneas de conexión para representar la interconexión de las entidades, las relaciones y sus atributos. Reflejan la estructura gramatical, donde las entidades son sustantivos y las relaciones son verbos.

3. Diagrama de entidad-relación (ER) para autenticación y la autorización

El diagrama modela una arquitectura de Control de Acceso Basado en Roles (RBAC) en el esquema por defecto public de PostgreSQL. Su propósito es estructurar la autenticación (identidad del usuario) y la autorización (permisos de acceso a módulos/vistas) para una aplicación desarrollada en Python con Streamlit.

El diseño en pgAdmin presenta una estructura normalizada en Tercera Forma Normal (3FN), altamente eficiente e ideal para consultas rápidas de permisos mediante sentencias JOIN, permitiendo validar en milisegundos qué elementos de la interfaz de Streamlit deben renderizarse según el rol del usuario conectado.

4. Documentación de Tablas

public.usuario

• Rol / Funcionalidad: Almacena la información de los usuarios registrados, permitiendo validar sus credenciales locales o mediante SSO (Google OAuth) y controlar el estado de la cuenta.
• Atributos:
• id (serial): Clave primaria autoincremental única del usuario.
• username (varchar(100)): Nombre de usuario único para la autenticación.
• password_hash (varchar(255)): Contraseña encriptada (hash).
• display_name (varchar(100)): Nombre completo para mostrar en la interfaz de Streamlit.
• email (varchar(100)): Correo electrónico único del usuario.
• rol_id (integer): Clave foránea que asigna el rol correspondiente al usuario.
• is_google_user (boolean): Bandera para indicar si el usuario autentica vía Google OAuth.
• registration_date (timestamp): Fecha y hora de registro.
• is_active (boolean): Estado de la cuenta (activo/inactivo) para habilitar o denegar accesos.

public.roles

• Rol / Funcionalidad: Define los perfiles o niveles de acceso dentro de la aplicación (ej. Admin, Analista, Operador).
• Atributos:
• id (serial): Clave primaria autoincremental única del rol.
• nombre (varchar(50)): Nombre descriptivo del rol (debe ser único).

public.modulos

• Rol / Funcionalidad: Almacena el catálogo de componentes, secciones o páginas que componen la aplicación en Streamlit (ej. Dashboard Ventas, Gestión de Usuarios, Reportes).
• Atributos:
• id (serial): Clave primaria autoincremental única del módulo.
• nombre (varchar(100)): Nombre de la sección o vista en la app (debe ser único).

public.roles_modulos

• Rol / Funcionalidad: Tabla intermedia o de cruce que implementa una relación Muchos a Muchos ($N:M$) entre roles y modulos. Determina exactamente a qué módulos tiene acceso cada rol.
• Atributos:
• rol_id (integer): Clave foránea hacia roles.id.
• modulo_id (integer): Clave foránea hacia modulos.id. (La combinación de ambos campos compone la clave primaria compuesta).

5. Relaciones y Cardinalidad

Tabla Origen	Tabla Destino	Relación	Cardinalidad	Descripción
roles	usuario	$1:N$ (Uno a Muchos)	1 en roles 0..N en usuario	Un rol puede estar asignado a cero o varios usuarios. Un usuario pertenece obligatoriamente a un único rol.
roles	roles_modulos	$1:N$ (Uno a Muchos)	1 en roles 0..N en roles_modulos	Un rol puede tener asignados múltiples permisos/módulos.
modulos	roles_modulos	$1:N$ (Uno a Muchos)	1 en modulos 0..N en roles_modulos	Un módulo puede estar habilitado para múltiples roles.
roles	modulos	$N:M$ (Muchos a Muchos)	A través de roles_modulos	Relación lógica entre roles y módulos resuelta mediante la tabla pivote roles_modulos.

6. Uso Sugerido en Streamlit

CREATE TABLE IF NOT EXISTS public.modulos
(
    id serial NOT NULL,
    nombre character varying(100) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT modulos_pkey PRIMARY KEY (id),
    CONSTRAINT modulos_nombre_key UNIQUE (nombre)
);

CREATE TABLE IF NOT EXISTS public.roles
(
    id serial NOT NULL,
    nombre character varying(50) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT roles_pkey PRIMARY KEY (id),
    CONSTRAINT roles_nombre_key UNIQUE (nombre)
);

CREATE TABLE IF NOT EXISTS public.roles_modulos
(
    rol_id integer NOT NULL,
    modulo_id integer NOT NULL,
    CONSTRAINT roles_modulos_pkey PRIMARY KEY (rol_id, modulo_id)
);

CREATE TABLE IF NOT EXISTS public.usuario
(
    id serial NOT NULL,
    username character varying(100) COLLATE pg_catalog."default" NOT NULL,
    password_hash character varying(255) COLLATE pg_catalog."default",
    display_name character varying(100) COLLATE pg_catalog."default",
    email character varying(100) COLLATE pg_catalog."default",
    rol_id integer,
    is_google_user boolean DEFAULT false,
    registration_date timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    is_active boolean DEFAULT true,
    CONSTRAINT usuario_pkey1 PRIMARY KEY (id),
    CONSTRAINT usuario_email_key UNIQUE (email),
    CONSTRAINT usuario_username_key1 UNIQUE (username)
);

ALTER TABLE IF EXISTS public.roles_modulos
    ADD CONSTRAINT roles_modulos_modulo_id_fkey FOREIGN KEY (modulo_id)
    REFERENCES public.modulos (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.roles_modulos
    ADD CONSTRAINT roles_modulos_rol_id_fkey FOREIGN KEY (rol_id)
    REFERENCES public.roles (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.usuario
    ADD CONSTRAINT usuario_rol_id_fkey FOREIGN KEY (rol_id)
    REFERENCES public.roles (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;

END;

Resumen del Modelo de Base de Datos

El modelo presentado se compone de 4 tablas relacionales diseñadas con una arquitectura limpia y modular:

1. usuario (Gestión de Identidad): Registra las credenciales locales (usuario/contraseña encriptada) e integración OAuth (Google), controla el estado activo/inactivo y vincula a cada usuario con un rol único.
2. roles (Niveles de Perfil): Define los perfiles funcionales de la aplicación (por ejemplo: Administrador, Analista, Operador).
3. modulos (Catálogo de Vistas/Páginas): Representa cada una de las páginas, pestañas o herramientas que integran la app en Streamlit.
4. roles_modulos (Matriz de Permisos $N:M$): Tabla intermedia que une roles con módulos, determinando de forma dinámica a qué secciones tiene acceso cada perfil.

Flujo de Trabajo en Streamlit:

• Autenticación: El usuario inicia sesión; Streamlit valida credenciales o token SSO y guarda el user_id y rol_id en st.session_state.
• Autorización Dinámica: Al navegar por la app, el script consulta la tabla roles_modulos para verificar si el rol del usuario incluye el módulo actual. Si no cuenta con permiso, Streamlit restringe la vista o deshabilita las opciones del menú lateral.