PostgreSQL: Usuários, Roles e Gerenciamento de Privilégios…

-- Roles de grupo (não podem fazer login)
CREATE ROLE dev_team NOINHERIT;
CREATE ROLE analytics_team NOINHERIT;
CREATE ROLE ops_team NOINHERIT;
CREATE ROLE audit_team NOINHERIT;

-- Usuários de login
-- ⚠️ Não codifique senhas em produção; use variáveis de ambiente ou um gerenciador de segredos
CREATE USER alice_dev PASSWORD 'SecureP@ss1' IN ROLE dev_team;
CREATE USER bob_dev PASSWORD 'SecureP@ss2' IN ROLE dev_team;
CREATE USER charlie_analyst PASSWORD 'SecureP@ss3' IN ROLE analytics_team;
CREATE USER diana_ops PASSWORD 'SecureP@ss4' IN ROLE ops_team INHERIT;

CREATE TABLE

-- Adicionar privilégio CREATEDB ao líder de operações
ALTER ROLE diana_ops CREATEDB;

-- Definir expiração de senha
ALTER ROLE charlie_analyst VALID UNTIL '2025-12-31';

-- Renomear uma role
ALTER ROLE dev_team RENAME TO engineering_team;

-- Desabilitar login temporariamente
ALTER ROLE bob_dev NOLOGIN;

CREATE TABLE

-- Equipe dev: CRUD completo nas tabelas de negócio
GRANT SELECT, INSERT, UPDATE, DELETE ON products TO dev_team;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO dev_team;
GRANT SELECT, INSERT, UPDATE, DELETE ON customers TO dev_team;
GRANT SELECT, INSERT, UPDATE, DELETE ON order_items TO dev_team;

-- Equipe analytics: somente leitura
GRANT SELECT ON products TO analytics_team;
GRANT SELECT ON orders TO analytics_team;
GRANT SELECT ON customers TO analytics_team;

-- Equipe audit: apenas logs de auditoria
GRANT SELECT ON price_audit_log TO audit_team;
GRANT SELECT ON ddl_audit_log TO audit_team;

INSERT 0 1

-- Equipe dev pode criar objetos no esquema dev
GRANT CREATE, USAGE ON SCHEMA dev TO dev_team;

-- Equipe analytics pode usar mas não criar no esquema public
GRANT USAGE ON SCHEMA public TO analytics_team;

-- Permitir analytics criar views materializadas em seu próprio esquema
GRANT CREATE, USAGE ON SCHEMA analytics TO analytics_team;

-- Serviço de aplicação conecta ao banco de dados
GRANT CONNECT ON DATABASE shop_db TO app_service;

CREATE TABLE

-- Revogar DELETE da equipe dev em products
REVOKE DELETE ON products FROM dev_team;

-- Revogar todos os privilégios em uma tabela
REVOKE ALL PRIVILEGES ON orders FROM public;

-- Revogar direito de criação de esquema
REVOKE CREATE ON SCHEMA dev FROM dev_team;

-- Cascata: revogar e concessões dependentes
REVOKE SELECT ON products FROM analytics_team CASCADE;

DELETE 2

-- Todas as tabelas futuras no esquema public são legíveis por analytics_team
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO analytics_team;

-- Todas as tabelas futuras criadas por app_service são graváveis por dev_team
ALTER DEFAULT PRIVILEGES FOR ROLE app_service IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE ON TABLES TO dev_team;

-- Todas as sequences futuras no esquema public
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO dev_team;

-- Todas as funções futuras
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT EXECUTE ON FUNCTIONS TO analytics_team;

INSERT 0 1

SELECT
  pg_get_userbyid(defaclrole) AS grantor,
  pg_get_userbyid(defaclnamespace) AS namespace_owner,
  n.nspname AS schema,
  defaclobjtype AS object_type,
  defaclacl AS acl
FROM pg_default_acl d
JOIN pg_namespace n ON n.oid = d.defaclnamespace;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Habilitar RLS na tabela orders
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Analistas só podem ver pedidos concluídos
CREATE POLICY pol_analytics_completed
  ON orders FOR SELECT
  TO analytics_team
  USING (order_status = 'completed');

-- Equipe dev pode ver todas as linhas
CREATE POLICY pol_dev_all
  ON orders FOR ALL
  TO dev_team
  USING (true)
  WITH CHECK (true);

CREATE TABLE

-- Habilitar RLS na tabela customers
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;

-- Cada usuário de atendimento vê apenas sua região atribuída
CREATE POLICY pol_region_access
  ON customers FOR SELECT
  USING (region = current_setting('app.region', true));

-- Forçar o proprietário da tabela a também cumprir RLS
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

CREATE TABLE

-- Equipe de vendas só pode inserir pedidos em sua região
CREATE POLICY pol_sales_insert
  ON orders FOR INSERT
  TO dev_team
  WITH CHECK (region = current_setting('app.region', true));

-- Equipe de vendas só pode atualizar pedidos em sua região
CREATE POLICY pol_sales_update
  ON orders FOR UPDATE
  TO dev_team
  USING (region = current_setting('app.region', true))
  WITH CHECK (region = current_setting('app.region', true));

INSERT 0 1

-- Criar uma role poderosa com NOINHERIT
CREATE ROLE admin_role NOINHERIT;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin_role;

-- Diana está em admin_role mas não pode usar privilégios de admin automaticamente
CREATE USER diana PASSWORD 'SecureP@ss5' IN ROLE admin_role NOINHERIT;

-- Diana deve alternar explicitamente para usar direitos de admin
SET ROLE admin_role;
-- Agora diana pode realizar operações de admin
RESET ROLE;
-- De volta aos privilégios normais

CREATE TABLE

-- Listar todas as roles e seus atributos
SELECT rolname, rolsuper, rolcreatedb, rolcanlogin, rolinherit
FROM pg_roles
WHERE rolname NOT LIKE 'pg_%'
ORDER BY rolname;

-- Listar associação de roles
SELECT
  r1.rolname AS member,
  r2.rolname AS role
FROM pg_auth_members m
JOIN pg_roles r1 ON r1.oid = m.member
JOIN pg_roles r2 ON r2.oid = m.roleid
ORDER BY r2.rolname, r1.rolname;

-- Verificar privilégios de tabela para uma role
SELECT table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'analytics_team'
ORDER BY table_name, privilege_type;

CREATE TABLE

-- Passo 1: Criar roles de grupo
CREATE ROLE dev_team NOINHERIT;
CREATE ROLE analytics_team NOINHERIT;
CREATE ROLE ops_team INHERIT;
CREATE ROLE audit_team NOINHERIT;
CREATE ROLE app_service LOGIN PASSWORD 'AppSecRet!';

-- Passo 2: Conceder permissões de esquema
GRANT CREATE, USAGE ON SCHEMA public TO dev_team;
GRANT USAGE ON SCHEMA public TO analytics_team;
GRANT CREATE, USAGE ON SCHEMA analytics TO analytics_team;
GRANT USAGE ON SCHEMA public TO ops_team;
GRANT USAGE ON SCHEMA audit TO audit_team;

-- Passo 3: Conceder permissões de tabela
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO dev_team;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_team;
GRANT SELECT ON ALL TABLES IN SCHEMA audit TO audit_team;
GRANT SELECT, INSERT, UPDATE, DELETE ON products, orders, order_items, customers TO app_service;

-- Passo 4: Privilégios padrão para tabelas futuras
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO analytics_team;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dev_team;
ALTER DEFAULT PRIVILEGES IN SCHEMA audit
  GRANT SELECT ON TABLES TO audit_team;

-- Passo 5: Permissões de sequence e função
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO dev_team;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_service;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO dev_team;

-- Passo 6: RLS - analistas só veem pedidos concluídos
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY pol_analytics_orders
  ON orders FOR SELECT
  TO analytics_team
  USING (order_status = 'completed');

-- Passo 7: RLS - equipe de auditoria só vê o esquema audit
ALTER TABLE price_audit_log ENABLE ROW LEVEL SECURITY;
CREATE POLICY pol_audit_read
  ON price_audit_log FOR SELECT
  TO audit_team
  USING (true);

-- Passo 8: Criar usuários de login e atribuir roles
CREATE USER alice_dev PASSWORD 'DevP@ss1' IN ROLE dev_team;
CREATE USER bob_dev PASSWORD 'DevP@ss2' IN ROLE dev_team;
CREATE USER charlie_analyst PASSWORD 'AnlP@ss3' IN ROLE analytics_team;
CREATE USER diana_ops PASSWORD 'OpsP@ss4' IN ROLE ops_team CREATEROLE;
CREATE USER eve_audit PASSWORD 'AudP@ss5' IN ROLE audit_team;