Gerenciamento de permissões granulares para contas em uma instância do ApsaraDB RDS for PostgreSQL - ApsaraDB RDS: Central de Documentação do Alibaba Cloud

Como funciona

No PostgreSQL, roles e usuários são do mesmo tipo de objeto. A distinção é funcional:

Roles armazenam permissões, mas não permitem login.
Usuários são roles com o atributo WITH LOGIN. Suas permissões efetivas correspondem à própria permissão de login somada às permissões de todas as roles concedidas a eles.

Ao atualizar as permissões de uma role, todos os usuários atribuídos a ela herdam a alteração automaticamente. Não é necessário conceder as permissões novamente.

Toda instância RDS inclui uma conta privilegiada chamada dbsuperuser. Essa conta possui permissões totais na instância e destina-se exclusivamente a administradores de banco de dados.

Modelo de permissões

O modelo recomendado de três níveis para um projeto ou equipe utiliza as seguintes entidades:

Entidade	Tipo	Permissões em tabelas	Permissões em stored procedures
`rdspg_owner`	Owner (usuário com DDL completo)	DDL (CREATE, DROP, ALTER) + DQL (SELECT) + DML (UPDATE, INSERT, DELETE)	DDL (CREATE, DROP, ALTER) + DQL (SELECT) + execução de stored procedures
`rdspg_role_readwrite`	Role	DQL (SELECT) + DML (UPDATE, INSERT, DELETE)	DQL (SELECT) + execução de stored procedures; operações DDL dentro de stored procedures retornam erro de permissão
`rdspg_role_readonly`	Role	Apenas DQL (SELECT)	DQL (SELECT) + execução de stored procedures; operações DDL dentro de stored procedures retornam erro de permissão

Os usuários da aplicação herdam permissões por meio da atribuição de roles:

rdspg_readwrite — herda as permissões de rdspg_role_readwrite + login
rdspg_readonly — herda as permissões de rdspg_role_readonly + login

Para um controle mais granular, crie roles adicionais conforme suas necessidades.

Não armazene tabelas no schema public . Por padrão, todos os usuários possuem a permissão CREATE e a permissão USAGE no schema public .

Pré-requisitos

Antes de começar, verifique se você tem:

Uma instância do ApsaraDB RDS for PostgreSQL
Acesso à conta privilegiada dbsuperuser
Uma conexão com a instância via ferramenta de linha de comando (consulte Conectar-se a uma instância do ApsaraDB RDS for PostgreSQL)

Configure permissões para um projeto

O exemplo a seguir configura permissões para um projeto chamado rdspg com dois schemas: rdspg e rdspg_1. Execute todos os comandos como dbsuperuser.

Etapa 1: Crie o owner e as 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;

O comando ALTER DEFAULT PRIVILEGES aplica-se a todos os objetos que rdspg_owner criar no futuro. Objetos já existentes exigem um comando GRANT separado.

Etapa 2: Crie usuários da aplicação

-- 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;

Etapa 3: Crie um schema e conceder acesso

-- 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;

Os usuários rdspg_readwrite e rdspg_readonly herdam o acesso ao schema por meio de suas roles. Não é necessária uma concessão direta para esses usuários.

Aplicar o princípio do menor privilégio

Utilize rdspg_readonly como usuário padrão para conexões da aplicação. Mude para rdspg_readwrite apenas quando uma operação de escrita for necessária. Essa abordagem permite a separação de leitura e escrita na camada da aplicação sem a sobrecarga de middleware de proxy.

Caso não haja uma instância RDS somente leitura anexada à sua instância RDS, recomendamos conceder permissões de leitura a um cliente e permissões de leitura e escrita ao outro. Configure suas conexões Java Database Connectivity (JDBC) da seguinte forma:

Cliente	Usuário	URL JDBC
Cliente somente leitura	`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 leitura e escrita	`rdspg_readwrite`	`the endpoint of your RDS instance`

Estender o modelo de permissões

Adicionar mais schemas

Para adicionar um segundo schema (rdspg_1) ao mesmo projeto:

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 os usuários atribuídos a essas roles herdam o acesso ao novo schema automaticamente.

Conceder acesso entre projetos

Para dar a employee_readwrite (um usuário no projeto employee) acesso de leitura ao projeto rdspg:

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

Verifique permissões

Usando \du

Conecte-se à sua instância e execute \du para listar as roles e as associações de membros:

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}

A coluna Member of para employee_readwrite exibe tanto rdspg_role_readonly quanto employee_role_readwrite, confirmando que o acesso de leitura entre projetos está ativo.

$Execute o comando \du para consultar permissões$

Usando SQL

Para uma auditoria completa de todas as roles e seus membros:

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;

Exemplos de permissões

Operações DDL por rdspg_owner

rdspg_owner possui permissões DDL completas em qualquer schema de sua propriedade:

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

DML e DQL por rdspg_readwrite

rdspg_readwrite pode ler e gravar dados, mas não pode modificar o schema:

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

Apenas DQL por rdspg_readonly

rdspg_readonly pode consultar, mas não modificar dados:

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)

Próximos passos

Conectar-se a uma instância do ApsaraDB RDS for PostgreSQL