Comandos esenciales para gestionar usuarios, roles y permisos en PostgreSQL

Los usuarios, roles y permisos en PostgreSQL determinan quién puede conectarse, qué puede leer y qué puede modificar. Si esos permisos crecen sin un plan claro, tu base de datos se vuelve difícil de auditar y fácil de sobreexponer.

PostgreSQL utiliza un solo concepto, el rol, tanto para usuarios de inicio de sesión como para grupos. Un rol con LOGIN puede conectarse. Un rol sin LOGIN se usa a menudo como grupo que agrupa privilegios compartidos.

Crear y modificar roles

Crea un rol de inicio de sesión para una aplicación con contraseña:

CREATE ROLE app_user WITH LOGIN PASSWORD 'contraseña_segura_aqui';

Crea un rol de grupo para acceso de solo lectura compartido:

CREATE ROLE reporting_readonly NOLOGIN;

Los atributos comunes de los roles incluyen:

Atributo	Qué permite
LOGIN	Conectarse al servidor de base de datos.
CREATEDB	Crear bases de datos.
CREATEROLE	Crear, modificar y eliminar muchos roles, con límites importantes en roles de superusuario.
REPLICATION	Usar conexiones de replicación.
SUPERUSER	Omitir las comprobaciones normales de permisos. Usar solo para roles de administración estrictamente controlados.
INHERIT	Usar privilegios de los roles de los que es miembro. Es el valor predeterminado.

Cambia una contraseña o deshabilita el inicio de sesión con ALTER ROLE:

ALTER ROLE app_user WITH PASSWORD 'nueva_contraseña_segura';
ALTER ROLE old_employee NOLOGIN;

Antes de eliminar un rol, gestiona todo lo que posee y cualquier privilegio que se le haya concedido:

REASSIGN OWNED BY old_employee TO postgres;
DROP OWNED BY old_employee;
DROP ROLE old_employee;

REASSIGN OWNED transfiere los objetos propiedad del rol. DROP OWNED elimina los privilegios concedidos al rol y puede eliminar objetos que aún posea en la base de datos actual, así que revísalo con cuidado antes de ejecutarlo.

Usa roles de grupo para un acceso más limpio

Concede permisos a roles de grupo, luego agrega personas o roles de aplicación a esos grupos.

GRANT reporting_readonly TO alice, bob;
REVOKE reporting_readonly FROM bob;

WITH ADMIN OPTION permite al miembro conceder y revocar esa membresía de rol para otros. Por sí solo no convierte al miembro en superusuario ni le da poder ilimitado sobre la base de datos.

GRANT reporting_readonly TO team_lead WITH ADMIN OPTION;

Este patrón facilita la auditoría del acceso. En lugar de verificar 50 usuarios para permisos de tabla, puedes verificar el rol de grupo.

Concede permisos sobre objetos

Usa GRANT para permitir acciones específicas sobre objetos específicos.

GRANT SELECT ON TABLE production.orders TO reporting_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE app.orders TO app_user;

Los privilegios comunes de tabla son SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES y TRIGGER.

Los permisos de esquema son independientes de los permisos de tabla. Un rol a menudo necesita USAGE en el esquema y privilegios en las tablas dentro de él:

GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_user;

Para inserciones que usan secuencias, concede también acceso a la secuencia:

GRANT USAGE, SELECT ON SEQUENCE app.orders_id_seq TO app_user;

Para funciones, concede ejecución explícitamente:

GRANT EXECUTE ON FUNCTION app.audit_log_insert(text) TO app_user;

Revocar permisos

REVOKE elimina una concesión directa.

REVOKE INSERT ON TABLE production.orders FROM app_user;
REVOKE ALL PRIVILEGES ON TABLE production.orders FROM reporting_readonly;

Si un usuario aún tiene acceso después de una revocación, verifica la membresía del grupo. El usuario puede heredar el mismo privilegio a través de otro rol.

Establecer permisos para tablas futuras

GRANT ON ALL TABLES afecta solo a las tablas existentes. Para tablas creadas posteriormente, usa ALTER DEFAULT PRIVILEGES desde el rol que creará esas tablas.

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO reporting_readonly;

Este es uno de los pasos que más se omiten en la configuración de permisos de PostgreSQL. Un usuario puede leer las tablas de hoy pero se le niega el acceso a las tablas de mañana porque nunca se configuraron los privilegios predeterminados.

Auditar el acceso actual

En psql, estos comandos son verificaciones rápidas:

\du
\du+ app_user
\dp app.orders
\dn+

Las vistas del catálogo son mejores para informes repetibles:

SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin
FROM pg_roles
ORDER BY rolname;

Para permisos de tabla:

SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'app'
ORDER BY grantee, table_name, privilege_type;

Hábitos de seguridad que valen la pena

Asigna roles dedicados a las aplicaciones en lugar de usar una cuenta de DBA humana. Concede el conjunto más pequeño de privilegios que la aplicación necesita. Evita SUPERUSER y concesiones amplias de ALL PRIVILEGES para el trabajo diario.

Usa roles de grupo para el acceso compartido y revisa las membresías regularmente. Cuando alguien cambia de equipo, eliminar una membresía de grupo es más seguro que buscar concesiones a nivel de tabla.

El siguiente paso práctico es elegir un esquema de producción y listar sus roles, membresías, concesiones de tabla y privilegios predeterminados. Corrige las brechas allí antes de expandir el mismo patrón al resto de tus bases de datos.