Fine-grained permission management for accounts in an ApsaraDB RDS for PostgreSQL instance - ApsaraDB RDS

ApsaraDB RDS for PostgreSQL utiliza un modelo de control de acceso basado en roles (RBAC) para gestionar los permisos de la base de datos. Este tema explica cómo configurar un modelo de permisos de tres niveles —propietario, rol de lectura-escritura y rol de solo lectura— adecuado para la gestión de accesos a nivel de proyecto o equipo.

Funcionamiento

En PostgreSQL, los roles y los usuarios son el mismo tipo de objeto. La distinción es funcional:

Los roles poseen permisos, pero no pueden iniciar sesión.
Los usuarios son roles con el atributo WITH LOGIN. Sus permisos efectivos equivalen a su propio permiso de inicio de sesión más los permisos de todos los roles que se les hayan otorgado.

Al actualizar los permisos de un rol, todos los usuarios asignados a ese rol heredan el cambio automáticamente. No es necesario volver a otorgar los permisos.

Cada instancia de RDS incluye una cuenta con privilegios llamada dbsuperuser. Esta cuenta tiene permisos completos sobre la instancia y está destinada exclusivamente a los administradores de bases de datos.

Modelo de permisos

El modelo de tres niveles recomendado para un proyecto o equipo utiliza las siguientes entidades:

Entidad	Tipo	Permisos en tablas	Permisos en procedimientos almacenados
`rdspg_owner`	Propietario (usuario con DDL completo)	DDL (CREATE, DROP, ALTER) + DQL (SELECT) + DML (UPDATE, INSERT, DELETE)	DDL (CREATE, DROP, ALTER) + DQL (SELECT) + ejecución de procedimientos almacenados
`rdspg_role_readwrite`	Rol	DQL (SELECT) + DML (UPDATE, INSERT, DELETE)	DQL (SELECT) + ejecución de procedimientos almacenados; las operaciones DDL dentro de procedimientos almacenados devuelven un error de permiso
`rdspg_role_readonly`	Rol	Solo DQL (SELECT)	DQL (SELECT) + ejecución de procedimientos almacenados; las operaciones DDL dentro de procedimientos almacenados devuelven un error de permiso

Los usuarios de la aplicación heredan los permisos mediante la asignación de roles:

rdspg_readwrite — hereda los permisos de rdspg_role_readwrite + inicio de sesión
rdspg_readonly — hereda los permisos de rdspg_role_readonly + inicio de sesión

Para un control más detallado, cree roles adicionales según sus necesidades.

No coloque tablas en el esquema public . De forma predeterminada, todos los usuarios tienen el permiso CREATE y el permiso USAGE en el esquema public .

Requisitos previos

Antes de empezar, asegúrese de contar con lo siguiente:

Una instancia de ApsaraDB RDS for PostgreSQL
Acceso a la cuenta con privilegios dbsuperuser
Una conexión a la instancia mediante una herramienta de línea de comandos (consulte Connect to an ApsaraDB RDS for PostgreSQL instance)

Configuración de permisos para un proyecto

El siguiente ejemplo configura los permisos para un proyecto llamado rdspg con dos esquemas: rdspg y rdspg_1. Ejecute todos los comandos como dbsuperuser.

Paso 1: Crear el propietario y los roles

-- Create the owner. Replace the password with a strong password.
CREATE USER rdspg_owner WITH LOGIN PASSWORD 'asdfy181BASDfadasdbfas';

-- Create the read-write and read-only roles (no login).
CREATE ROLE rdspg_role_readwrite;
CREATE ROLE rdspg_role_readonly;

-- Grant DQL + DML permissions on tables created by rdspg_owner to rdspg_role_readwrite.
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT ALL ON TABLES TO rdspg_role_readwrite;

-- Grant DQL + DML permissions on sequences created by rdspg_owner to rdspg_role_readwrite.
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT ALL ON SEQUENCES TO rdspg_role_readwrite;

-- Grant DQL-only permission on tables created by rdspg_owner to rdspg_role_readonly.
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT SELECT ON TABLES TO rdspg_role_readonly;

ALTER DEFAULT PRIVILEGES se aplica a todos los objetos que rdspg_owner cree en el futuro. Los objetos existentes requieren una sentencia GRANT por separado.

Paso 2: Crear usuarios de aplicación

-- Read-write user: inherits DQL + DML permissions from rdspg_role_readwrite.
CREATE USER rdspg_readwrite WITH LOGIN PASSWORD 'dfandfnapSDhf23hbEfabf';
GRANT rdspg_role_readwrite TO rdspg_readwrite;

-- Read-only user: inherits DQL-only permission from rdspg_role_readonly.
CREATE USER rdspg_readonly WITH LOGIN PASSWORD 'F89h912badSHfadsd01zlk';
GRANT rdspg_role_readonly TO rdspg_readonly;

Paso 3: Crear un esquema y otorgar acceso

-- Set rdspg_owner as the owner of the rdspg schema.
CREATE SCHEMA rdspg AUTHORIZATION rdspg_owner;

-- Grant schema access to both roles.
GRANT USAGE ON SCHEMA rdspg TO rdspg_role_readwrite;
GRANT USAGE ON SCHEMA rdspg TO rdspg_role_readonly;

rdspg_readwrite y rdspg_readonly heredan el acceso al esquema a través de sus roles. No es necesario otorgar permisos directos a estos usuarios.

Aplicación del principio de menor privilegio

Utilice rdspg_readonly como usuario predeterminado para las conexiones de la aplicación. Cambie a rdspg_readwrite solo cuando se requiera una operación de escritura. Este enfoque permite la separación de lectura y escritura en la capa de aplicación sin la sobrecarga de un middleware de proxy.

Si no hay ninguna instancia de RDS de solo lectura vinculada a su instancia de RDS, se recomienda otorgar permisos de lectura a un cliente y permisos de lectura y escritura al otro. Configure sus conexiones de Java Database Connectivity (JDBC) de la siguiente manera:

Cliente	Usuario	URL JDBC
Cliente de solo lectura	`rdspg_readonly`	`the endpoint of read-only RDS instance 1,the endpoint of read-only RDS instance 2,the endpoint of your RDS instance`
Cliente de lectura-escritura	`rdspg_readwrite`	`the endpoint of your RDS instance`

Extensión del modelo de permisos

Agregar más esquemas

Para agregar un segundo esquema (rdspg_1) al mismo proyecto:

CREATE SCHEMA rdspg_1 AUTHORIZATION rdspg_owner;

-- Grant the access permission on the rdspg_2 schema to roles.
-- Grant the permissions to perform DDL CREATE, DROP, and ALTER operations on tables in the rdspg_1 schema.
GRANT USAGE ON SCHEMA rdspg_1 TO rdspg_role_readwrite;
GRANT USAGE ON SCHEMA rdspg_1 TO rdspg_role_readonly;

Todos los usuarios asignados a estos roles heredan automáticamente el acceso al nuevo esquema.

Otorgar acceso entre proyectos

Para dar a employee_readwrite (un usuario del proyecto employee) acceso de lectura al proyecto rdspg:

-- Grant rdspg_role_readonly permissions to employee_readwrite.
GRANT rdspg_role_readonly TO employee_readwrite;

Verificación de permisos

Uso de \du

Conéctese a su instancia y ejecute \du para listar los roles y sus membresías:

postgres=> \du
                         List of roles
 Role name              | Attributes    | Member of
------------------------+---------------+----------------------------------------------
 rdspg_owner            | ...           | {}
 rdspg_role_readwrite   | Cannot login  | {}
 rdspg_role_readonly    | Cannot login  | {}
 rdspg_readwrite        | ...           | {rdspg_role_readwrite}
 rdspg_readonly         | ...           | {rdspg_role_readonly}
 employee_readwrite      | ...           | {rdspg_role_readonly,employee_role_readwrite}

La columna Member of para employee_readwrite muestra tanto rdspg_role_readonly como employee_role_readwrite, lo que confirma que el acceso de lectura entre proyectos está activo.

$Run the \du command to query permissions$

Uso de SQL

Para una auditoría completa de todos los roles y sus membresías:

SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  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
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;

Ejemplos de permisos

Operaciones DDL por rdspg_owner

rdspg_owner posee permisos DDL completos en cualquier esquema del que sea propietario:

CREATE TABLE rdspg.test(id bigserial primary key, name text);
CREATE INDEX idx_test_name on rdspg.test(name);

DML y DQL por rdspg_readwrite

rdspg_readwrite puede leer y escribir datos, pero no puede modificar el esquema:

INSERT INTO rdspg.test (name) VALUES('name0'),('name1');
SELECT id,name FROM rdspg.test LIMIT 1;

-- DDL is blocked:
CREATE TABLE rdspg.test2(id int);
ERROR:  permission denied for schema rdspg

DROP TABLE rdspg.test;
ERROR:  must be owner of table test

ALTER TABLE rdspg.test ADD id2 int;
ERROR:  must be owner of table test

CREATE INDEX idx_test_name on rdspg.test(name);
ERROR:  must be owner of table test

Solo DQL por rdspg_readonly

rdspg_readonly puede consultar datos, pero no modificarlos:

INSERT INTO rdspg.test (name) VALUES('name0'),('name1');
ERROR:  permission denied for table test

SELECT id,name FROM rdspg.test LIMIT 1;
 id | name
----+-------
  1 | name0
(1 row)

Pasos siguientes

Connect to an ApsaraDB RDS for PostgreSQL instance