Blog de Amazon Web Services (AWS)

Administración de usuarios y roles de PostgreSQL

Por Yaser Raja, Consultor Senior con equipo de Servicios Profesionales en AWS

 

PostgreSQL es uno de los sistemas de bases de datos relacionales de código abierto más populares. Con más de 30 años de trabajo de desarrollo, PostgreSQL ha demostrado ser una base de datos altamente fiable y robusta que puede manejar un gran número de cargas de trabajo de datos complejas. PostgreSQL se considera la principal opción de base de datos de código abierto al migrar desde bases de datos comerciales como Oracle. Amazon Web Services (AWS) proporciona dos opciones de PostgreSQL administradas: Amazon Relational Database Service (Amazon RDS) para PostgreSQL y Amazon Aurora PostgreSQL. En esta publicación, hablo de algunas de las prácticas recomendadas para administrar usuarios y roles en PostgreSQL.

Con PostgreSQL, puede crear usuarios y roles con permisos de acceso granulares. Al nuevo usuario o rol se les debe conceder selectivamente los permisos necesarios para cada objeto de base de datos. Esto da mucho poder al usuario final, pero al mismo tiempo, dificulta potencialmente el proceso de creación de usuarios y roles con los permisos correctos.

PostgreSQL le permite conceder permisos directamente a los usuarios de la base de datos. Sin embargo, como práctica recomendada, se recomienda crear varios roles con conjuntos específicos de permisos basados en los requisitos de aplicación y acceso. Paso seguido, asigne el rol apropiado a cada usuario. Los roles deben utilizarse para aplicar un modelo de privilegios mínimos para acceder a objetos de base de datos. El usuario maestro que se crea durante la creación de instancias de Amazon RDS y Aurora PostgreSQL solo debe utilizarse para tareas de administración de bases de datos, como la creación de otros usuarios, roles y bases de datos. El usuario maestro nunca debe ser utilizado por la aplicación.

El método recomendado para configurar un control de acceso detallado en PostgreSQL es el siguiente:

  • Utilice el usuario maestro para crear roles por aplicación o caso de uso, como readonly (solo lectura) y readwrite (escritura).
  • Agregue permisos para permitir que estos roles tengan acceso a varios objetos de base de datos. Por ejemplo, el rol de readonly solo puede ejecutar consultas SELECT.
  • Conceda a los roles los menos permisos posibles necesarios para la funcionalidad.
  • Cree nuevos usuarios para cada aplicación o funcionalidad distinta, como app_user (usuario de aplicación) y reporting_user (usuario de reportes).
  • Asigne los roles aplicables a estos usuarios para otorgarles rápidamente los mismos permisos que el rol. Por ejemplo, conceda el rol readwrite a app_user y conceda el rol de readonly a reporting_user.
  • En cualquier momento, puede quitar el rol del usuario para revocar los permisos.

 

 

El siguiente diagrama presenta un resumen de estas recomendaciones:

En las siguientes secciones se describen estos pasos en detalle. Puede conectarse al extremo de RDS de la base de datos de PostgreSQL mediante un cliente como psql y ejecutar las sentencias SQL.

 

Usuarios, grupos y roles

Los usuarios, grupos y roles son lo mismo en PostgreSQL, y la única diferencia es que los usuarios tienen permiso para iniciar sesión de forma predeterminada. Las instrucciones CREATE USER y CREATE GROUP son en realidad alias de la instrucción CREATE ROLE.

 

 

En otros sistemas de administración de bases de datos relacionales (RDBMS) como Oracle, los usuarios y las funciones son dos entidades diferentes. En Oracle, no se puede utilizar un rol para iniciar sesión en la base de datos. Los roles se utilizan solo para agrupar grants y otros roles. Este rol se puede asignar a uno o más usuarios para otorgarles todos los permisos. Para obtener más información sobre cómo migrar usuarios, roles y grants de Oracle a PostgreSQL, consulte la publicación del blog de AWS Usando SQL para asignar usuarios, roles y grants de Oracle a PostgreSQL.

Para crear un usuario de PostgreSQL, utilice la siguiente instrucción SQL:

CREATE USER myuser WITH PASSWORD 'secret_passwd';

También puede crear un usuario con la siguiente instrucción SQL:

CREATE ROLE myuser WITH LOGIN PASSWORD 'secret_passwd';

Ambas sentencias crean exactamente el mismo usuario. Este nuevo usuario no tiene ningún permiso aparte de los permisos predeterminados disponibles para el rol public. Todos los nuevos usuarios y roles heredan los permisos del rol public. En la siguiente sección se proporcionan más detalles sobre el rol public.

 

Esquema public y rol public

Cuando se crea una nueva base de datos, PostgreSQL crea de forma predeterminada un esquema denominado public y concede acceso en este esquema a un rol de backend denominado public. A todos los usuarios y roles nuevos se les concede de forma predeterminada el rol public y, por lo tanto, pueden crear objetos en el esquema public.

PostgreSQL utiliza un concepto de ruta de búsqueda. La ruta de búsqueda es una lista de nombres de esquema que PostgreSQL comprueba cuando no se utiliza un nombre calificado del objeto de base de datos. Por ejemplo, cuando selecciona de una tabla denominada “mytable”, PostgreSQL busca esta tabla en los esquemas enumerados en la ruta de búsqueda. Elige la primera coincidencia que encuentra. De forma predeterminada, la ruta de búsqueda contiene los siguientes esquemas:

postgres=# show search_path;
   search_path
-----------------
 "$user", public
(1 row)

El nombre “$user” se refiere al nombre del usuario que ha iniciado sesión actualmente. De forma predeterminada, no existe ningún esquema con el mismo nombre que el nombre de usuario. Por lo tanto, el esquema public se convierte en el esquema predeterminado siempre que se utiliza un nombre de objeto no calificado. Por este motivo, cuando un usuario intenta crear una nueva tabla sin especificar el nombre del esquema, la tabla se crea en el esquema public. Como se mencionó anteriormente, de forma predeterminada, todos los usuarios tienen acceso para crear objetos en el esquema public y, por lo tanto, la tabla se ha creado correctamente.

Esto se convierte en un problema si intenta crear un usuario de solo lectura. Incluso si restringe todos los privilegios, los permisos heredados a través del rol public permiten al usuario crear objetos en el esquema public.

Para solucionarlo, se debe revocar el permiso de creación predeterminado en el esquema public desde el rol public mediante la siguiente instrucción SQL:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Asegúrese de ser el propietario del esquema public o de formar parte de un rol que le permita ejecutar esta instrucción SQL.

La siguiente declaración revoca la capacidad del rol público de conectarse a la base de datos:

REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;

Esto garantiza que los usuarios no puedan conectarse a la base de datos de forma predeterminada a menos que se conceda explícitamente este permiso.

La revocación de los permisos del rol public afecta a todos los usuarios y roles existentes. Los usuarios y roles que deberían de poder conectarse a la base de datos o crear objetos en el esquema público deben recibir los permisos explícitamente antes de revocar los permisos del rol public en el entorno de producción.

 

Crear roles de base de datos

En las siguientes secciones se documenta el proceso de creación de nuevos roles y el proceso de concesión de permisos para acceder a varios objetos de base de datos. Los permisos deben concederse a nivel de base de datos, esquema y objeto de esquema. Por ejemplo, si necesita conceder acceso a una tabla, también debe asegurarse de que el rol tenga acceso a la base de datos y al esquema en que existe la tabla. Si falta alguno de los permisos, el rol no puede acceder a la tabla.

 

 

Rol de solo lectura

El primer paso consiste en crear un nuevo rol denominado readonly mediante la siguiente instrucción SQL:

CREATE ROLE readonly;

Este es un rol simple sin permisos ni contraseña. No se puede utilizar para iniciar sesión en la base de datos.

Conceda permiso a este rol para conectarse a la base de datos de destino denominada “mydatabase”:

GRANT CONNECT ON DATABASE mydatabase TO readonly;

El siguiente paso es otorgar acceso al uso de este rol a su esquema. Supongamos que el esquema se llama myschema:

GRANT USAGE ON SCHEMA myschema TO readonly;

En este paso se concede permiso de rol de readonly para realizar alguna actividad dentro del esquema. Sin este paso, el rol readonly no puede realizar ninguna acción en los objetos de este esquema, incluso si se han concedido permisos para esos objetos.

El siguiente paso consiste en otorgar acceso al rol readonly para ejecutar las consultas en las tablas requeridas.

GRANT SELECT ON TABLE mytable1, mytable2 TO readonly;

Si el requisito es conceder acceso a todas las tablas y vistas del esquema, puede utilizar el siguiente SQL:

GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;

La instrucción SQL anterior otorga acceso SELECT al rol de solo lectura en todas las tablas y vistas existentes en el esquema llamado myschema. Tenga en cuenta que el usuario readonly no podrá acceder a las tablas nuevas que se añadan en el futuro. Para garantizar que también se pueda acceder a nuevas tablas y vistas, ejecute la siguiente instrucción para conceder permisos automáticamente:

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;

 

Rol de lectura y escritura

El proceso de agregar un rol de lectura/escritura es muy similar al proceso de rol de solo lectura que se trató anteriormente. El primer paso es crear un rol:

CREATE ROLE readwrite;

Conceda permiso a este rol para conectarse a la base de datos de destino:

GRANT CONNECT ON DATABASE mydatabase TO readwrite;

Conceda privilegio de uso de esquemas:

GRANT USAGE ON SCHEMA myschema TO readwrite;

Si desea permitir que este rol cree nuevos objetos como tablas de este esquema, utilice el siguiente SQL en lugar del anterior:

GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;

El siguiente paso es conceder acceso a las tablas. Como se mencionó en la sección anterior, la concesión puede realizarse en tablas individuales o en todas las tablas del esquema. Para tablas individuales, utilice el siguiente SQL:

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE mytable1, mytable2 TO readwrite;

Para todas las tablas y vistas del esquema, utilice el siguiente SQL:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;

Para conceder automáticamente permisos sobre tablas y vistas añadidas en el futuro:

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;

Para los roles de lectura y escritura, normalmente existe el requisito de utilizar secuencias también. Puede dar acceso selectivo de la siguiente manera:

GRANT USAGE ON SEQUENCE myseq1, myseq2 TO readwrite;

También puede conceder permiso a todas las secuencias mediante la siguiente instrucción SQL:

GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;

Para conceder permisos automáticamente a las secuencias añadidas en el futuro:

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;

Puede conceder más o menos permisos en función de los requisitos. La documentación del comando GRANT de PostgreSQL proporciona más detalles sobre los objetos en los que se pueden conceder permisos y las sentencias SQL necesarias.

 

Creación de usuarios de base de datos

Con los roles implementados, se simplifica el proceso de creación de usuarios. Simplemente crea el usuario y concédele uno de los roles existentes. Estas son las instrucciones SQL para este proceso:

CREATE USER myuser1 WITH PASSWORD 'secret_passwd';
GRANT readonly TO myuser1;

Esta instrucción SQL otorga a myuser1 los mismos permisos que el rol de solo lectura.

Del mismo modo, puede conceder acceso de lectura y escritura a un usuario otorgando el rol readwrite. La documentación CREATE USER de PostgreSQL contiene más detalles sobre los parámetros que puede establecer al crear un usuario. Por ejemplo, puede especificar un plazo de caducidad para el usuario o permitir que el usuario cree bases de datos.

 

Administración de contraseñas de usuario

Después de crear un usuario, debe proporcionar estas credenciales a la aplicación para que pueda acceder a la base de datos. Es esencial asegurarse de que estas credenciales no estén codificadas (hard-coded) en el código fuente ni se coloquen en archivos de configuración compartidos como texto plano sin encriptar. AWS proporciona una solución para ello con AWS Secrets Manager. Con Secrets Manager, puede almacenar las credenciales y, a continuación, utilizar AWS Identity and Access Management (IAM) para permitir que solo ciertos usuarios y roles de IAM lean las credenciales. Para conocer los pasos que implica esto, consulte Creación y administración de secretos con AWS Secrets Manager en la Guía del usuario de AWS Secrets Manager.

Además de almacenar las credenciales, una característica muy útil que proporciona Secrets Manager es la rotación de contraseñas de usuario de base de datos. Puede utilizar esta función para configurar una política que cambie automáticamente la contraseña a una frecuencia determinada. Para obtener más información sobre cómo configurarlo para que no haya tiempo de inactividad para las aplicaciones, consulte Rotación de los secretos de AWS Secrets Manager.

Amazon RDS y Amazon Aurora PostgreSQL proporcionan una nueva función de administración de contraseñas restringidas compatible con PostgreSQL 10.6 y versiones posteriores. Con un nuevo parámetro y un rol especial, puede limitar los cambios de contraseña de usuario de la base de datos a los miembros de este rol especial. De este modo, se habilita un mayor control sobre la administración de contraseñas en el lado del cliente (por ejemplo, requisitos de caducidad y complejidad).

 

Autenticación de base de datos con IAM

Amazon RDS y Aurora PostgreSQL se han integrado con IAM para que pueda autenticarse en su instancia de bas e de datos mediante la autenticación de bases de datos de IAM. Esta función está disponible para las versiones 9.5.14, 9.6.9 o superior de Amazon RDS PostgreSQL y 10.4 o superior. Para Aurora PostgreSQL, esta función está disponible para las versiones 9.6.9 o superior y la versión 10.4 o superior. La ventaja clave de esta función es que puede utilizar IAM para administrar de forma centralizada el acceso a los recursos de la base de datos en lugar de administrar el acceso individualmente en cada instancia de base de datos. Con este método, el administrador puede conceder o revocar fácilmente el acceso a la base de datos mediante una política de IAM.

Una vez agregados los permisos en IAM, el usuario puede solicitar una contraseña temporal mediante la CLI de AWS y, a continuación, conectarse a la base de datos mediante esta contraseña temporal. En el siguiente diagrama se describe el flujo del proceso.

 

 

Para obtener más información sobre esta función, consulte Autenticación de bases de datos de IAM para MySQL y PostgreSQL. La documentación también contiene pasos detallados para configurar la autenticación de base de datos de IAM.

Este método solo se ocupa de la parte de autenticación. La concesión de permisos a varios objetos de base de datos se realiza dentro de la base de datos, tal como se explica en esta publicación. Por ejemplo, para conceder acceso de lectura y escritura (readwrite) a este usuario, ejecute la siguiente instrucción SQL:

GRANT readwrite TO db_user;

 

Revocar o cambiar los permisos de usuario

Utilizando el método documentado anteriormente, resulta muy fácil revocar los privilegios de un usuario. Por ejemplo, puede quitar el permiso de lectura y escritura (readwrite) de myuser1 utilizando la siguiente instrucción SQL:

REVOKE readwrite FROM myuser1;

Del mismo modo, puede otorgar un nuevo rol de la siguiente manera:

GRANT readonly TO myuser1;

 

Utilización del monitoreo

Puede supervisar la actividad del usuario configurando los parámetros del log de PostgreSQL disponibles en los grupos de parámetros de RDS. Por ejemplo, puede configurar los parámetros log_connections y log_disconnections para capturar todas las conexiones y desconexiones nuevas. Después de configurar estos parámetros en el grupo de parámetros, verá los siguientes mensajes en los archivos de log:

2018-11-09 21:08:39 UTC:XX-XX-XX-XX.amazon.com(27585):myuser@mydb:[18014]:LOG: connection authorized:
user=myuser database=mydb SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)

2018-11-09 21:09:19 UTC:XX-XX-XX-XX.amazon.com(27585):myuser@mydb:[18014]:LOG: disconnection: session time:
0:00:39.649 user=myuser database=mydb host=XX-XX-XX-XX.amazon.com port=27585

Si necesita información de auditoría personalizada a nivel de sesión o objeto más detallada, puede utilizar la extensión PgAudit. Los pasos para configurar pgAudit con Amazon RDS y Aurora PostgreSQL están disponibles en Trabajando con la extensión pgaudit en la Guía del usuario de Amazon RDS.

El aumento del detalle en los logs de bases de datos afecta al tamaño del almacenamiento, al uso de I/O y el uso de la CPU. Por este motivo, es importante que pruebe estos cambios antes de implementarlos en producción.

 

Comprobación de los roles concedidos

Puede utilizar la siguiente consulta para obtener una lista de todos los usuarios y roles de la base de datos junto con una lista de roles que se les han concedido:

SELECT
      r.rolname,
      ARRAY(SELECT b.rolname
            FROM pg_catalog.pg_auth_members m
            JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
            WHERE m.member = r.oid) as memberof
FROM pg_catalog.pg_roles r
WHERE r.rolname NOT IN ('pg_signal_backend','rds_iam',
                        'rds_replication','rds_superuser',
                        'rdsadmin','rdsrepladmin')
ORDER BY 1;

A continuación, se muestra un ejemplo de salida de una instancia de RDS de prueba:

rolname     |    memberof
----------------+-----------------
app_user       | {readwrite}
postgres       | {rds_superuser}
readonly       | {}
readwrite      | {}
reporting_user | {readonly}

Tenga en cuenta que un usuario puede ser miembro de varios roles con permisos distintos o superpuestos. En este caso, el usuario obtiene una suma de todos los permisos.

También puede utilizar la tabla de catálogo pg_roles para comprobar atributos como la fecha de caducidad de la contraseña o el número de conexiones paralelas permitidas.

 

Resumen

En esta publicación, compartí algunas prácticas recomendadas para administrar usuarios y roles en PostgreSQL. Esta publicación proporciona un marco básico que puede modificar según los requisitos de la aplicación y los principios de privilegios mínimos.

Para resumir los conceptos, a continuación he proporcionado las siguientes instrucciones SQL de referencia para implementar los usuarios y las funciones mediante un escenario de ejemplo:

  1. Se ha creado una base de datos PostgreSQL con la base de datos principal denominada mydatabase.
  2. Se ha creado un nuevo esquema denominado myschema con varias tablas.
  3. Se deben crear dos usuarios de informes con los permisos para leer todas las tablas del esquema myschema.
  4. Se deben crear dos usuarios de aplicaciones con permisos para leer y escribir en todas las tablas del esquema myschema y también para crear tablas nuevas.
  5. Los usuarios deben obtener automáticamente permisos en cualquier tabla nueva que se añada en el futuro.

Para implementar este escenario, debe conectarse a la base de datos mydatabase mediante el usuario maestro y, a continuación, ejecutar las siguientes sentencias SQL utilizando cualquiera de los clientes de PostgreSQL como psql o pgAdmin:

-- Revocando privilegios del rol 'public'
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;

-- Rol solo lectura
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydatabase TO readonly;
GRANT USAGE ON SCHEMA myschema TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;

-- Rol lectura-escritura
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE mydatabase TO readwrite;
GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;

-- Creacion de usuarios
CREATE USER reporting_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER reporting_user2 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user2 WITH PASSWORD 'some_secret_passwd';

-- Concediendo permisos a usuarios
GRANT readonly TO reporting_user1;
GRANT readonly TO reporting_user2;
GRANT readwrite TO app_user1;
GRANT readwrite TO app_user2;

Puede obtener más información sobre los usuarios y roles de PostgreSQL en el sitio web de documentación de PostgreSQL.

Si tiene alguna pregunta o comentario sobre esta publicación de blog, no dude en utilizar la sección de comentarios aquí para publicar sus ideas.

 

Este artículo fue traducido del Blog de AWS en Inglés.

 

Sobre el autor

Yaser Raja es consultor senior del equipo de servicios profesionales en Amazon Web Services. Trabaja con los clientes para crear soluciones escalables, seguras y de alta disponibilidad en la nube de AWS. Su área de enfoque son las migraciones homogéneas y heterogéneas de bases de datos locales a AWS RDS y Aurora PostgreSQL.

 

 

 

Sobre el traductor

Carlos Robles es Arquitecto de Soluciones especializado en base de datos en AWS.

 

 

 

 

 

Conozca más contenidos sobre base de datos en la página de sesiones bajo demanda.

Acceda >