|
PROFESSIONAL NEWSGROUPS
WINDEV, WEBDEV and WINDEV Mobile |
| | | | | |
| Script para seu primeiro projeto PostgreSQL |
| Started by Boller, May, 05 2025 5:13 AM - 15 replies |
| |
| | | |
|
| |
Registered member
3,963 messages |
|
| Posted on May, 05 2025 - 5:13 AM |
```sql
-- Script de criação e configuração do PostgreSQL para DB_Erp
-- Versão com particionamento, fulltext e índices soundex
-- Extensões necessárias
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
-- Criação do tablespace personalizado
CREATE TABLESPACE tbs_erp
OWNER postgres
LOCATION '/var/lib/postgresql/tbs_erp';
-- Criação da base de dados com configurações específicas
CREATE DATABASE db_erp
WITH OWNER = adriano
TEMPLATE = template1
ENCODING = 'UTF8'
LC_COLLATE = 'pt_BR.UTF-8'
LC_CTYPE = 'pt_BR.UTF-8'
TABLESPACE = tbs_erp
CONNECTION LIMIT = -1;
-- Comentário na base de dados
COMMENT ON DATABASE db_erp IS 'Base de dados ERP - Sistema de Gestão Empresarial';
-- Criação dos usuários
CREATE USER adriano WITH PASSWORD 'senha_segura_adriano' SUPERUSER CREATEDB CREATEROLE;
CREATE USER juliana WITH PASSWORD 'senha_segura_juliana' NOSUPERUSER NOCREATEDB NOCREATEROLE;
-- Configuração de permissões
GRANT CONNECT ON DATABASE db_erp TO juliana;
GRANT USAGE ON SCHEMA public TO juliana;
-- Conexão à base de dados para criar as tabelas
\c db_erp
-- Criação das extensões necessárias na base de dados
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
-- Criação de esquema para organização
CREATE SCHEMA erp;
GRANT USAGE ON SCHEMA erp TO juliana;
-- Criação da tabela de clientes particionada por estado
CREATE TABLE erp.clientes (
id_cliente SERIAL,
nome VARCHAR(150) NOT NULL,
cpf VARCHAR(14) NOT NULL UNIQUE CHECK (cpf ~ '^[0-9]{3}\.[0-9]{3}\.[0-9]{3}\-[0-9]{2}$'),
logradouro VARCHAR(200) NOT NULL,
numero VARCHAR(10) NOT NULL,
complemento VARCHAR(100),
bairro VARCHAR(100) NOT NULL,
cidade VARCHAR(100) NOT NULL,
estado CHAR(2) NOT NULL CHECK (estado ~ '^[A-Z]{2}$'),
cep VARCHAR(9) NOT NULL CHECK (cep ~ '^[0-9]{5}\-[0-9]{3}$'),
data_cadastro TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
data_atualizacao TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
ativo BOOLEAN DEFAULT TRUE,
PRIMARY KEY (id_cliente, estado)
) PARTITION BY LIST (estado);
-- Criação das partições para cada estado
CREATE TABLE erp.clientes_ac PARTITION OF erp.clientes FOR VALUES IN ('AC');
CREATE TABLE erp.clientes_al PARTITION OF erp.clientes FOR VALUES IN ('AL');
CREATE TABLE erp.clientes_am PARTITION OF erp.clientes FOR VALUES IN ('AM');
CREATE TABLE erp.clientes_ap PARTITION OF erp.clientes FOR VALUES IN ('AP');
CREATE TABLE erp.clientes_ba PARTITION OF erp.clientes FOR VALUES IN ('BA');
CREATE TABLE erp.clientes_ce PARTITION OF erp.clientes FOR VALUES IN ('CE');
CREATE TABLE erp.clientes_df PARTITION OF erp.clientes FOR VALUES IN ('DF');
CREATE TABLE erp.clientes_es PARTITION OF erp.clientes FOR VALUES IN ('ES');
CREATE TABLE erp.clientes_go PARTITION OF erp.clientes FOR VALUES IN ('GO');
CREATE TABLE erp.clientes_ma PARTITION OF erp.clientes FOR VALUES IN ('MA');
CREATE TABLE erp.clientes_mg PARTITION OF erp.clientes FOR VALUES IN ('MG');
CREATE TABLE erp.clientes_ms PARTITION OF erp.clientes FOR VALUES IN ('MS');
CREATE TABLE erp.clientes_mt PARTITION OF erp.clientes FOR VALUES IN ('MT');
CREATE TABLE erp.clientes_pa PARTITION OF erp.clientes FOR VALUES IN ('PA');
CREATE TABLE erp.clientes_pb PARTITION OF erp.clientes FOR VALUES IN ('PB');
CREATE TABLE erp.clientes_pe PARTITION OF erp.clientes FOR VALUES IN ('PE');
CREATE TABLE erp.clientes_pi PARTITION OF erp.clientes FOR VALUES IN ('PI');
CREATE TABLE erp.clientes_pr PARTITION OF erp.clientes FOR VALUES IN ('PR');
CREATE TABLE erp.clientes_rj PARTITION OF erp.clientes FOR VALUES IN ('RJ');
CREATE TABLE erp.clientes_rn PARTITION OF erp.clientes FOR VALUES IN ('RN');
CREATE TABLE erp.clientes_ro PARTITION OF erp.clientes FOR VALUES IN ('RO');
CREATE TABLE erp.clientes_rr PARTITION OF erp.clientes FOR VALUES IN ('RR');
CREATE TABLE erp.clientes_rs PARTITION OF erp.clientes FOR VALUES IN ('RS');
CREATE TABLE erp.clientes_sc PARTITION OF erp.clientes FOR VALUES IN ('SC');
CREATE TABLE erp.clientes_se PARTITION OF erp.clientes FOR VALUES IN ('SE');
CREATE TABLE erp.clientes_sp PARTITION OF erp.clientes FOR VALUES IN ('SP');
CREATE TABLE erp.clientes_to PARTITION OF erp.clientes FOR VALUES IN ('TO');
-- Índice para busca por CPF em cada partição
CREATE INDEX idx_clientes_cpf ON erp.clientes (cpf);
-- Criação da função para busca fulltext em clientes
CREATE OR REPLACE FUNCTION erp.clientes_fulltext_trigger() RETURNS trigger AS $$
BEGIN
NEW.tsv_nome := to_tsvector('portuguese', unaccent(NEW.nome));
NEW.tsv_endereco := to_tsvector('portuguese', unaccent(
COALESCE(NEW.logradouro, '') || ' ' ||
COALESCE(NEW.numero, '') || ' ' ||
COALESCE(NEW.complemento, '') || ' ' ||
COALESCE(NEW.bairro, '') || ' ' ||
COALESCE(NEW.cidade, '')
));
RETURN NEW;
END
$$ LANGUAGE plpgsql;
-- Adição das colunas de busca fulltext para cada partição
ALTER TABLE erp.clientes ADD COLUMN tsv_nome tsvector;
ALTER TABLE erp.clientes ADD COLUMN tsv_endereco tsvector;
ALTER TABLE erp.clientes ADD COLUMN soundex_nome VARCHAR(4);
-- Índices GIN para busca fulltext
CREATE INDEX idx_clientes_tsv_nome ON erp.clientes USING GIN (tsv_nome);
CREATE INDEX idx_clientes_tsv_endereco ON erp.clientes USING GIN (tsv_endereco);
CREATE INDEX idx_clientes_nome_trigram ON erp.clientes USING GIN (nome gin_trgm_ops);
CREATE INDEX idx_clientes_soundex ON erp.clientes (soundex_nome);
-- Trigger para atualizar os índices fulltext
CREATE TRIGGER trg_clientes_fulltext
BEFORE INSERT OR UPDATE ON erp.clientes
FOR EACH ROW EXECUTE FUNCTION erp.clientes_fulltext_trigger();
-- Função para atualizar o índice soundex
CREATE OR REPLACE FUNCTION erp.atualiza_soundex_nome()
RETURNS TRIGGER AS $$
BEGIN
NEW.soundex_nome = soundex(NEW.nome);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Trigger para atualizar o índice soundex
CREATE TRIGGER trg_clientes_soundex
BEFORE INSERT OR UPDATE ON erp.clientes
FOR EACH ROW EXECUTE FUNCTION erp.atualiza_soundex_nome();
-- Tabela de contatos (filha de clientes) com particionamento por tipo de contato
CREATE TABLE erp.contatos (
id_contato SERIAL,
id_cliente INTEGER NOT NULL,
tipo_contato VARCHAR(20) NOT NULL CHECK (tipo_contato IN ('telefone', 'email')),
tipo_uso VARCHAR(20) NOT NULL CHECK (tipo_uso IN ('pessoal', 'empresarial', 'recado')),
valor VARCHAR(255) NOT NULL,
principal BOOLEAN DEFAULT FALSE,
data_cadastro TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
data_atualizacao TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
ativo BOOLEAN DEFAULT TRUE,
PRIMARY KEY (id_contato, tipo_contato)
) PARTITION BY LIST (tipo_contato);
-- Criação das partições para contatos
CREATE TABLE erp.contatos_telefone PARTITION OF erp.contatos FOR VALUES IN ('telefone');
CREATE TABLE erp.contatos_email PARTITION OF erp.contatos FOR VALUES IN ('email');
-- Adição das chaves estrangeiras para contatos (em cada partição)
ALTER TABLE erp.contatos_telefone ADD CONSTRAINT fk_contatos_telefone_cliente
FOREIGN KEY (id_cliente) REFERENCES erp.clientes(id_cliente);
ALTER TABLE erp.contatos_email ADD CONSTRAINT fk_contatos_email_cliente
FOREIGN KEY (id_cliente) REFERENCES erp.clientes(id_cliente);
-- Índices para contatos
CREATE INDEX idx_contatos_cliente ON erp.contatos (id_cliente);
CREATE INDEX idx_contatos_tipo_uso ON erp.contatos (tipo_uso);
CREATE INDEX idx_contatos_valor_trigram ON erp.contatos USING GIN (valor gin_trgm_ops);
-- Tabela para armazenar arquivos binários (fotos) particionada por ano de upload
CREATE TABLE erp.arquivos_binarios (
id_arquivo SERIAL,
id_cliente INTEGER NOT NULL,
nome_arquivo VARCHAR(255) NOT NULL,
descricao VARCHAR(500),
tipo_mime VARCHAR(100) NOT NULL,
dados BYTEA NOT NULL,
tamanho_bytes INTEGER NOT NULL,
hash_verificacao VARCHAR(128) NOT NULL,
data_upload TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
data_atualizacao TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
ano_upload INTEGER GENERATED ALWAYS AS (EXTRACT(YEAR FROM data_upload)) STORED,
PRIMARY KEY (id_arquivo, ano_upload)
) PARTITION BY RANGE (ano_upload);
-- Criação das partições para os próximos 5 anos
CREATE TABLE erp.arquivos_2024 PARTITION OF erp.arquivos_binarios
FOR VALUES FROM (2024) TO (2025);
CREATE TABLE erp.arquivos_2025 PARTITION OF erp.arquivos_binarios
FOR VALUES FROM (2025) TO (2026);
CREATE TABLE erp.arquivos_2026 PARTITION OF erp.arquivos_binarios
FOR VALUES FROM (2026) TO (2027);
CREATE TABLE erp.arquivos_2027 PARTITION OF erp.arquivos_binarios
FOR VALUES FROM (2027) TO (2028);
CREATE TABLE erp.arquivos_2028 PARTITION OF erp.arquivos_binarios
FOR VALUES FROM (2028) TO (2029);
-- Adição das chaves estrangeiras para arquivos (em cada partição)
ALTER TABLE erp.arquivos_2024 ADD CONSTRAINT fk_arquivos_2024_cliente
FOREIGN KEY (id_cliente) REFERENCES erp.clientes(id_cliente);
ALTER TABLE erp.arquivos_2025 ADD CONSTRAINT fk_arquivos_2025_cliente
FOREIGN KEY (id_cliente) REFERENCES erp.clientes(id_cliente);
ALTER TABLE erp.arquivos_2026 ADD CONSTRAINT fk_arquivos_2026_cliente
FOREIGN KEY (id_cliente) REFERENCES erp.clientes(id_cliente);
ALTER TABLE erp.arquivos_2027 ADD CONSTRAINT fk_arquivos_2027_cliente
FOREIGN KEY (id_cliente) REFERENCES erp.clientes(id_cliente);
ALTER TABLE erp.arquivos_2028 ADD CONSTRAINT fk_arquivos_2028_cliente
FOREIGN KEY (id_cliente) REFERENCES erp.clientes(id_cliente);
-- Índices para busca de arquivos
CREATE INDEX idx_arquivos_cliente ON erp.arquivos_binarios (id_cliente);
CREATE INDEX idx_arquivos_nome_trigram ON erp.arquivos_binarios USING GIN (nome_arquivo gin_trgm_ops);
CREATE INDEX idx_arquivos_descricao_fulltext ON erp.arquivos_binarios USING GIN (to_tsvector('portuguese', descricao));
-- Criação de uma função para atualizar a data de atualização automaticamente
CREATE OR REPLACE FUNCTION erp.atualiza_data_atualizacao()
RETURNS TRIGGER AS $$
BEGIN
NEW.data_atualizacao = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Triggers para atualizar automaticamente o campo data_atualizacao
CREATE TRIGGER trg_clientes_atualizacao
BEFORE UPDATE ON erp.clientes
FOR EACH ROW EXECUTE FUNCTION erp.atualiza_data_atualizacao();
CREATE TRIGGER trg_contatos_atualizacao
BEFORE UPDATE ON erp.contatos
FOR EACH ROW EXECUTE FUNCTION erp.atualiza_data_atualizacao();
CREATE TRIGGER trg_arquivos_atualizacao
BEFORE UPDATE ON erp.arquivos_binarios
FOR EACH ROW EXECUTE FUNCTION erp.atualiza_data_atualizacao();
-- Criação de funções de busca fonética
CREATE OR REPLACE FUNCTION erp.busca_clientes_fonetica(termo_busca TEXT)
RETURNS TABLE (
id_cliente INTEGER,
nome VARCHAR,
cpf VARCHAR,
endereco TEXT,
soundex_valor VARCHAR,
diferenca INTEGER
) AS $$
BEGIN
RETURN QUERY
SELECT
c.id_cliente,
c.nome,
c.cpf,
c.logradouro || ', ' || c.numero || COALESCE(', ' || c.complemento, '') ||
' - ' || c.bairro || ', ' || c.cidade || ' - ' || c.estado || ', ' || c.cep AS endereco,
c.soundex_nome,
difference(c.nome, termo_busca) AS diferenca
FROM
erp.clientes c
WHERE
difference(c.nome, termo_busca) > 2
ORDER BY
diferenca DESC;
END;
$$ LANGUAGE plpgsql;
-- Função para busca fulltext
CREATE OR REPLACE FUNCTION erp.busca_clientes_fulltext(termo_busca TEXT)
RETURNS TABLE (
id_cliente INTEGER,
nome VARCHAR,
cpf VARCHAR,
endereco TEXT,
rank FLOAT
) AS $$
BEGIN
RETURN QUERY
SELECT
c.id_cliente,
c.nome,
c.cpf,
c.logradouro || ', ' || c.numero || COALESCE(', ' || c.complemento, '') ||
' - ' || c.bairro || ', ' || c.cidade || ' - ' || c.estado || ', ' || c.cep AS endereco,
ts_rank_cd(c.tsv_nome, to_tsquery('portuguese', unaccent(termo_busca))) AS rank
FROM
erp.clientes c
WHERE
c.tsv_nome @@ to_tsquery('portuguese', unaccent(termo_busca))
ORDER BY
rank DESC;
END;
$$ LANGUAGE plpgsql;
-- Tabela de log de auditoria particionada por mês
CREATE TABLE erp.log_auditoria (
id_log SERIAL,
data_hora TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
usuario_bd VARCHAR(100) NOT NULL,
tabela VARCHAR(100) NOT NULL,
operacao VARCHAR(10) NOT NULL CHECK (operacao IN ('INSERT', 'UPDATE', 'DELETE')),
id_registro INTEGER,
dados_antigos JSONB,
dados_novos JSONB,
mes_ano INTEGER GENERATED ALWAYS AS (EXTRACT(YEAR FROM data_hora) * 100 + EXTRACT(MONTH FROM data_hora)) STORED,
PRIMARY KEY (id_log, mes_ano)
) PARTITION BY RANGE (mes_ano);
-- Criando partições para os próximos 12 meses (começando em maio/2025)
CREATE TABLE erp.log_auditoria_2025_05 PARTITION OF erp.log_auditoria
FOR VALUES FROM (202505) TO (202506);
CREATE TABLE erp.log_auditoria_2025_06 PARTITION OF erp.log_auditoria
FOR VALUES FROM (202506) TO (202507);
CREATE TABLE erp.log_auditoria_2025_07 PARTITION OF erp.log_auditoria
FOR VALUES FROM (202507) TO (202508);
CREATE TABLE erp.log_auditoria_2025_08 PARTITION OF erp.log_auditoria
FOR VALUES FROM (202508) TO (202509);
CREATE TABLE erp.log_auditoria_2025_09 PARTITION OF erp.log_auditoria
FOR VALUES FROM (202509) TO (202510);
CREATE TABLE erp.log_auditoria_2025_10 PARTITION OF erp.log_auditoria
FOR VALUES FROM (202510) TO (202511);
CREATE TABLE erp.log_auditoria_2025_11 PARTITION OF erp.log_auditoria
FOR VALUES FROM (202511) TO (202512);
CREATE TABLE erp.log_auditoria_2025_12 PARTITION OF erp.log_auditoria
FOR VALUES FROM (202512) TO (202601);
CREATE TABLE erp.log_auditoria_2026_01 PARTITION OF erp.log_auditoria
FOR VALUES FROM (202601) TO (202602);
CREATE TABLE erp.log_auditoria_2026_02 PARTITION OF erp.log_auditoria
FOR VALUES FROM (202602) TO (202603);
CREATE TABLE erp.log_auditoria_2026_03 PARTITION OF erp.log_auditoria
FOR VALUES FROM (202603) TO (202604);
CREATE TABLE erp.log_auditoria_2026_04 PARTITION OF erp.log_auditoria
FOR VALUES FROM (202604) TO (202605);
-- Função para registrar operações de auditoria
CREATE OR REPLACE FUNCTION erp.registra_auditoria()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO erp.log_auditoria(usuario_bd, tabela, operacao, id_registro, dados_novos)
VALUES (current_user, TG_TABLE_NAME, TG_OP,
CASE
WHEN TG_TABLE_NAME = 'clientes' THEN NEW.id_cliente
WHEN TG_TABLE_NAME = 'contatos' THEN NEW.id_contato
WHEN TG_TABLE_NAME = 'arquivos_binarios' THEN NEW.id_arquivo
END,
row_to_json(NEW));
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO erp.log_auditoria(usuario_bd, tabela, operacao, id_registro, dados_antigos, dados_novos)
VALUES (current_user, TG_TABLE_NAME, TG_OP,
CASE
WHEN TG_TABLE_NAME = 'clientes' THEN NEW.id_cliente
WHEN TG_TABLE_NAME = 'contatos' THEN NEW.id_contato
WHEN TG_TABLE_NAME = 'arquivos_binarios' THEN NEW.id_arquivo
END,
row_to_json(OLD), row_to_json(NEW));
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO erp.log_auditoria(usuario_bd, tabela, operacao, id_registro, dados_antigos)
VALUES (current_user, TG_TABLE_NAME, TG_OP,
CASE
WHEN TG_TABLE_NAME = 'clientes' THEN OLD.id_cliente
WHEN TG_TABLE_NAME = 'contatos' THEN OLD.id_contato
WHEN TG_TABLE_NAME = 'arquivos_binarios' THEN OLD.id_arquivo
END,
row_to_json(OLD));
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- Triggers para auditoria
CREATE TRIGGER trg_audit_clientes
AFTER INSERT OR UPDATE OR DELETE ON erp.clientes
FOR EACH ROW EXECUTE FUNCTION erp.registra_auditoria();
CREATE TRIGGER trg_audit_contatos
AFTER INSERT OR UPDATE OR DELETE ON erp.contatos
FOR EACH ROW EXECUTE FUNCTION erp.registra_auditoria();
CREATE TRIGGER trg_audit_arquivos
AFTER INSERT OR UPDATE OR DELETE ON erp.arquivos_binarios
FOR EACH ROW EXECUTE FUNCTION erp.registra_auditoria();
-- Revogar permissões públicas para aumentar a segurança
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO PUBLIC;
-- Configuração de políticas de segurança em nível de linha (RLS)
ALTER TABLE erp.clientes ENABLE ROW LEVEL SECURITY;
ALTER TABLE erp.contatos ENABLE ROW LEVEL SECURITY;
ALTER TABLE erp.arquivos_binarios ENABLE ROW LEVEL SECURITY;
-- Política para adriano poder ver/modificar tudo
CREATE POLICY admin_policy ON erp.clientes TO adriano USING (true);
CREATE POLICY admin_policy ON erp.contatos TO adriano USING (true);
CREATE POLICY admin_policy ON erp.arquivos_binarios TO adriano USING (true);
-- Política para juliana ver apenas clientes ativos
CREATE POLICY usuario_policy ON erp.clientes TO juliana USING (ativo = true);
CREATE POLICY usuario_policy ON erp.contatos TO juliana USING (ativo = true);
CREATE POLICY usuario_policy ON erp.arquivos_binarios TO juliana USING (EXISTS (
SELECT 1 FROM erp.clientes c WHERE c.id_cliente = erp.arquivos_binarios.id_cliente AND c.ativo = true
));
-- Permissões para o usuário juliana
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA erp TO juliana;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA erp TO juliana;
-- Configurações de segurança adicionais
ALTER DEFAULT PRIVILEGES IN SCHEMA erp GRANT SELECT, INSERT, UPDATE ON TABLES TO juliana;
ALTER DEFAULT PRIVILEGES IN SCHEMA erp GRANT USAGE, SELECT ON SEQUENCES TO juliana;
-- Função para manutenção automática de partições
CREATE OR REPLACE FUNCTION erp.criar_particao_log_mensal()
RETURNS void AS $$
DECLARE
ano_mes INTEGER;
nome_particao TEXT;
data_inicio DATE;
data_fim DATE;
BEGIN
-- Calcular o próximo mês após a última partição
SELECT MAX(CAST(SUBSTRING(relname FROM 17) AS INTEGER))
INTO ano_mes
FROM pg_class
WHERE relname LIKE 'log_auditoria_%'
AND relkind = 'r';
-- Calcular o próximo ano e mês
IF SUBSTRING(CAST(ano_mes AS TEXT) FROM 5 FOR 2) = '12' THEN
ano_mes := (SUBSTRING(CAST(ano_mes AS TEXT) FROM 1 FOR 4)::INTEGER + 1) * 100 + 1;
ELSE
ano_mes := ano_mes + 1;
END IF;
-- Formar o nome da partição e as datas
nome_particao := 'erp.log_auditoria_' || SUBSTRING(CAST(ano_mes AS TEXT) FROM 1 FOR 4) || '_' ||
LPAD(SUBSTRING(CAST(ano_mes AS TEXT) FROM 5 FOR 2), 2, '0');
data_inicio := make_date(
SUBSTRING(CAST(ano_mes AS TEXT) FROM 1 FOR 4)::INTEGER,
SUBSTRING(CAST(ano_mes AS TEXT) FROM 5 FOR 2)::INTEGER,
1
);
data_fim := data_inicio + INTERVAL '1 month';
-- Criar a nova partição
EXECUTE 'CREATE TABLE ' || nome_particao || ' PARTITION OF erp.log_auditoria ' ||
'FOR VALUES FROM (' || ano_mes || ') TO (' ||
(EXTRACT(YEAR FROM data_fim)::INTEGER * 100 + EXTRACT(MONTH FROM data_fim)::INTEGER) || ')';
RAISE NOTICE 'Partição % criada para o período de % a %', nome_particao, data_inicio, data_fim;
END;
$$ LANGUAGE plpgsql;
-- Configurar job para criar partições automaticamente
-- (Note: isso normalmente seria feito usando pg_cron extension)
-- Aqui está apenas a definição da função que seria chamada periodicamente
-- Criação de uma visão materializada para estatísticas de clientes por estado
CREATE MATERIALIZED VIEW erp.estatisticas_clientes_estado AS
SELECT
estado,
COUNT(*) AS total_clientes,
COUNT(CASE WHEN ativo THEN 1 END) AS clientes_ativos,
COUNT(CASE WHEN NOT ativo THEN 1 END) AS clientes_inativos,
COUNT(DISTINCT c.id_cliente) AS total_com_contatos
FROM
erp.clientes cl
LEFT JOIN
erp.contatos c ON cl.id_cliente = c.id_cliente
GROUP BY
estado
WITH DATA;
-- Índice na visão materializada
CREATE UNIQUE INDEX idx_estatisticas_estado ON erp.estatisticas_clientes_estado (estado);
```
Também vou adicionar as configurações atualizadas para `postgresql.conf` com melhorias para suportar particionamento e busca de texto:
```
# Configurações de memória
shared_buffers = '2GB' # Ajuste para 25% da memória do sistema
work_mem = '256MB' # Memória para operações de ordenação
maintenance_work_mem = '512MB' # Memória para operações de manutenção
effective_cache_size = '6GB' # Estimativa de cache do sistema (50-75% da RAM)
# Configurações específicas para particionamento
max_parallel_maintenance_workers = 4 # Para operações de manutenção em paralelo
vacuum_cleanup_index_scale_factor = 0.1 # Otimização para vacuum
# Configurações para busca de texto
default_text_search_config = 'pg_catalog.portuguese'
# Configurações de CPU
max_worker_processes = 8 # Ajuste para número de núcleos disponíveis
max_parallel_workers_per_gather = 4 # Metade dos núcleos disponíveis
max_parallel_workers = 8 # Igual ao número de núcleos
parallel_tuple_cost = 0.1 # Reduzido para favorecer paralelismo
parallel_setup_cost = 100 # Reduzido para favorecer paralelismo
# Configurações de WAL e checkpoint
wal_level = 'replica' # Nível de registro necessário
checkpoint_timeout = '15min' # Tempo entre checkpoints
max_wal_size = '1GB' # Tamanho máximo de WAL antes de checkpoint
min_wal_size = '512MB' # Tamanho mínimo de WAL
# Configurações de autovacuum (otimizadas para tabelas particionadas)
autovacuum = on
autovacuum_max_workers = 4 # Aumentado para lidar com partições
autovacuum_naptime = '30s' # Reduzido para verificar mais frequentemente
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.05 # Reduzido para tabelas grandes
autovacuum_analyze_scale_factor = 0.025 # Reduzido para tabelas grandes
# Configurações de segurança
ssl = on
password_encryption = 'scram-sha-256'
log_min_duration_statement = 500 # Loga consultas que demoram mais de 500ms
log_statement = 'ddl' # Loga todas as instruções DDL
log_line_prefix = '%m [%p] %q%u@%d ' # Prefixo detalhado para logs
# Configurações específicas para melhorar as consultas de texto
gin_fuzzy_search_limit = 100 # Limite para busca fuzzy em índices GIN
gin_pending_list_limit = '16MB' # Limite para operações em índices GIN
```
Este script de SQL atualizado inclui:
1. Tabelas particionadas:
- Clientes particionados por estado (LIST)
- Contatos particionados por tipo (LIST)
- Arquivos binários particionados por ano de upload (RANGE)
- Log de auditoria particionado por mês (RANGE)
2. Suporte à busca fulltext:
- Índices GIN para busca vetorial em texto
- Funções para busca de texto com ranking
- Colunas tsvector para armazenar vetores de texto indexados
- Extensão unaccent para tratar acentuações
3. Implementação de Soundex e busca fonética:
- Extensão fuzzystrmatch para suporte a soundex e busca fonética
- Coluna para armazenamento de código soundex
- Função para busca fonética com ranking por similaridade
- Trigger para atualização automática do soundex
4. Recursos adicionais:
- Função para criação automática de novas partições
- Vista materializada para estatísticas
- Indexação trigram para busca aproximada
- Configurações otimizadas para suportar particionamento e busca de texto
As configurações do PostgreSQL foram ajustadas para otimizar o desempenho com esses novos recursos.
Sql e config é uma revisão detalhada com sugestões pontuais de melhorias e ajustes complementares para deixar tudo 100% redondo e seguro:
⸻
1. Tópicos de Melhoria e Sugestões Técnicas
1.1 Criação de Usuário com Privilégios Apropriados
-- Mudar isso:
CREATE USER adriano WITH PASSWORD 'senha_segura_adriano' SUPERUSER CREATEDB CREATEROLE;
-- Para ambientes seguros em produção:
CREATE USER adriano WITH PASSWORD 'senha_segura_adriano' LOGIN CREATEDB;
-- Evite SUPERUSER sempre que possível. Use papel separado se necessário para administração:
CREATE ROLE role_admin WITH CREATEROLE LOGIN;
GRANT role_admin TO adriano;
⸻
1.2 Acesso ao Schema Público
-- Remover isso, pois dá acesso global:
GRANT USAGE ON SCHEMA public TO PUBLIC;
-- Melhor manter:
REVOKE ALL ON SCHEMA public FROM PUBLIC;
⸻
1.3 Foreign Keys nas Tabelas Particionadas
A FK nas tabelas contatos e arquivos_binarios é aplicada apenas nas partições. Porém, o PostgreSQL ainda não propaga as FKs da tabela mãe, o que pode dificultar manutenção e integridade referencial completa.
Sugestão:
• Documentar isso para sua equipe.
• Usar triggers como fallback para garantir consistência, se necessário.
⸻
1.4 Sugestão para Compressão e Storage de Arquivos
No PostgreSQL, arquivos binários em BYTEA podem inflar o banco.
Sugestões:
• Use pg_largeobject se arquivos forem muito grandes.
• Ou aplicar compactação com função:
CREATE FUNCTION erp.compress_data(input bytea)
RETURNS bytea AS $$
BEGIN
RETURN encode(pg_compress(input), 'base64')::bytea;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
⸻
1.5 Verificação de CPF e CEP
O CHECK com REGEX está ótimo, mas pode ser melhorado com função reutilizável para legibilidade e testes:
CREATE OR REPLACE FUNCTION erp.is_valid_cpf(cpf TEXT) RETURNS BOOLEAN AS $$
BEGIN
RETURN cpf ~ '^[0-9]{3}\.[0-9]{3}\.[0-9]{3}-[0-9]{2}$';
END;
$$ LANGUAGE plpgsql IMMUTABLE;
E depois use:
cpf TEXT NOT NULL CHECK (erp.is_valid_cpf(cpf))
⸻
1.6 Log de Auditoria
A função registra_auditoria() está excelente!
Ajuste menor:
• Verificar se TG_TABLE_NAME vem em minúsculo sempre para evitar problemas se usar aspas em nomes de tabelas futuros.
⸻
1.7 Performance: Indexação em data_upload
Adicione:
CREATE INDEX idx_arquivos_data_upload ON erp.arquivos_binarios (data_upload);
⸻
1.8 Segurança: pg_hba.conf
Certifique-se de que a linha correspondente está assim:
host db_erp all 0.0.0.0/0 scram-sha-256
E no pgpass.conf:
*:*:db_erp:juliana:senha_segura_juliana
⸻
1.9 postgresql.conf – Sugestões Complementares
Adicionar:
max_connections = 200 # ou conforme o uso esperado
track_io_timing = on # bom para diagnósticos de I/O
effective_io_concurrency = 200 # útil para SSD
random_page_cost = 1.1 # ajuste para SSDs
⸻
2. O Que Está Excelente e Não Precisa Mudar
• Particionamento por estado, tipo e ano: perfeito
• Uso de RLS (Row Level Security): ótimo e seguro
• Indexação com GIN + pg_trgm + Soundex: moderno e rápido
• Triggers para atualização automática: elegantes e bem modularizadas
• materialized view para estatísticas: ótima prática
• Organização por schema erp: excelente para manutenibilidade
⸻
3. Considerações Finais
Esse script está muito completo e avançado. As sugestões acima são incrementais para:
• melhorar segurança,
• reduzir riscos em produção,
• otimizar performance de I/O e CPU,
• facilitar manutenção e auditoria.
O que é PgBouncer?
PgBouncer é um connection pooler leve para PostgreSQL. Ele mantém conexões persistentes com o banco e gerencia as solicitações de múltiplos clientes, evitando overhead de abrir/conectar/autenticar a cada requisição.
⸻
Vantagens para o seu projeto db_erp
1. Melhora o desempenho de aplicações com muitas conexões curtas.
2. Economiza recursos no PostgreSQL, pois reduz o número de backends simultâneos.
3. Aumenta a segurança, funcionando como gateway e aplicando autenticação via pgbouncer.ini.
4. Facilita failover e upgrades, pois permite desconectar clientes sem derrubar o banco.
⸻
Configuração recomendada do pgbouncer.ini
[databases]
db_erp = host=127.0.0.1 port=5432 dbname=db_erp
[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
admin_users = adriano
pool_mode = transaction
server_reset_query = DISCARD ALL
max_client_conn = 200
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 20
reserve_pool_timeout = 5
ignore_startup_parameters = extra_float_digits
⸻
userlist.txt (usuários permitidos)
"adriano" "senha_segura_adriano"
"juliana" "senha_segura_juliana"
⸻
Ajuste no pg_hba.conf
Garanta que o PgBouncer possa se conectar e autenticar com segurança:
# Permitir PgBouncer local
host db_erp all 127.0.0.1/32 scram-sha-256
⸻
Comando para iniciar
pgbouncer -d /etc/pgbouncer/pgbouncer.ini
⸻
Uso no sistema ERP
Configure seu cliente (ex: aplicativo Webdev, Python, Node, etc.) para usar a porta do PgBouncer (6432) ao invés da porta padrão (5432):
host=127.0.0.1 port=6432 dbname=db_erp user=juliana password=xxxx
⸻
Melhores modos de pool para cada caso:
No seu caso (ERP com Webdev e Mobile), o modo transaction é ideal.
⸻
Conclusão
O uso do PgBouncer melhora drasticamente:
• Conexão simultânea do sistema ERP
• Performance e uso de memória no PostgreSQL
• Segurança e controle de acesso com auth_file e pool centralizado
Só pra criar um usuário
###############
Claro! Aqui está um script SQL seguro e claro para criar um novo usuário no PostgreSQL com senha criptografada usando scram-sha-256, conforme as melhores práticas:
-- Criação de usuário no PostgreSQL com segurança reforçada
-- 1. Criar o usuário (sem permissões administrativas)
CREATE ROLE juliana
WITH LOGIN
PASSWORD 'senha_segura_juliana'
ENCRYPTED
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
NOINHERIT
CONNECTION LIMIT 10; -- Limita número de conexões simultâneas
-- 2. Atribuir permissões básicas (ex: conexão com uma base específica)
GRANT CONNECT ON DATABASE db_erp TO juliana;
-- 3. Permitir uso do schema 'erp'
GRANT USAGE ON SCHEMA erp TO juliana;
-- 4. Permissões de leitura e escrita nas tabelas
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA erp TO juliana;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA erp TO juliana;
-- 5. Permissões padrão para futuras tabelas
ALTER DEFAULT PRIVILEGES IN SCHEMA erp
GRANT SELECT, INSERT, UPDATE ON TABLES TO juliana;
ALTER DEFAULT PRIVILEGES IN SCHEMA erp
GRANT USAGE, SELECT ON SEQUENCES TO juliana;
Recomendação adicional: Use ALTER ROLE para aplicar limites extras, como tempo ocioso, tempo de sessão ou limites de uso de CPU, se for administrado via pg_stat_statements.
Criar uma tabela
###########
Abaixo está um script SQL completo para PostgreSQL que:
• Cria uma tabela erp.clientes_log
• Define chave primária
• Cria índices (inclusive trigram e GIN)
• Implementa triggers para log de alterações (INSERT/UPDATE/DELETE)
• Usa JSONB para armazenar estado antigo e novo do registro
⸻
Script SQL - Tabela com índices e trigger de log
-- Criação do schema se necessário
CREATE SCHEMA IF NOT EXISTS erp;
-- Tabela principal
CREATE TABLE erp.clientes (
id_cliente SERIAL PRIMARY KEY,
nome VARCHAR(150) NOT NULL,
cpf VARCHAR(14) NOT NULL UNIQUE,
email VARCHAR(255),
data_cadastro TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Índices adicionais
CREATE INDEX idx_clientes_nome ON erp.clientes (nome);
CREATE INDEX idx_clientes_cpf ON erp.clientes (cpf);
CREATE INDEX idx_clientes_email_trgm ON erp.clientes USING GIN (email gin_trgm_ops);
-- Tabela de log
CREATE TABLE erp.clientes_log (
id_log SERIAL PRIMARY KEY,
id_cliente INTEGER,
operacao VARCHAR(10) CHECK (operacao IN ('INSERT', 'UPDATE', 'DELETE')),
data_log TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
dados_anteriores JSONB,
dados_novos JSONB
);
-- Função para log
CREATE OR REPLACE FUNCTION erp.fn_log_clientes()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO erp.clientes_log (id_cliente, operacao, dados_novos)
VALUES (NEW.id_cliente, TG_OP, row_to_json(NEW));
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO erp.clientes_log (id_cliente, operacao, dados_anteriores, dados_novos)
VALUES (OLD.id_cliente, TG_OP, row_to_json(OLD), row_to_json(NEW));
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO erp.clientes_log (id_cliente, operacao, dados_anteriores)
VALUES (OLD.id_cliente, TG_OP, row_to_json(OLD));
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- Trigger
CREATE TRIGGER trg_clientes_log
AFTER INSERT OR UPDATE OR DELETE ON erp.clientes
FOR EACH ROW EXECUTE FUNCTION erp.fn_log_clientes();
⸻
Esse script é ideal para rastreabilidade de dados com auditoria completa.
Para evitar que um banco PostgreSQL seja apagado acidentalmente ou sem autorização envolve uma combinação de medidas técnicas e administrativas. Aqui vão as melhores práticas para impedir DROP DATABASE indevido:
⸻
1. Revogar privilégios administrativos
• Nunca conceda CREATEDB ou SUPERUSER para usuários comuns.
ALTER ROLE juliana NOSUPERUSER NOCREATEDB NOCREATEROLE;
⸻
2. Usar uma role intermediária
• Crie uma role “sistema” que tem permissão sobre a base de dados.
• Os usuários acessam via GRANT de permissões específicas, sem acesso direto de dono.
⸻
3. Impedir o DROP DATABASE com permissões no SO
• No sistema operacional, restrinja permissões de escrita no diretório onde o banco está instalado ($PGDATA).
• Apenas o usuário do serviço PostgreSQL (postgres) deve ter permissão.
⸻
4. Arquivo pg_hba.conf
• Bloqueie acesso a usuários de IPs não autorizados ou force autenticação forte:
# Permitir somente IPs específicos com autenticação scram
host all all 192.168.1.0/24 scram-sha-256
⸻
5. Monitoração ativa de comandos DDL
• Configure o PostgreSQL para logar comandos DROP, ALTER, CREATE:
log_statement = 'ddl'
⸻
6. Trigger de proteção contra DROP?
Infelizmente, não é possível criar trigger para DROP DATABASE, pois comandos DDL em nível de banco não disparam triggers.
⸻
7. Automatize backup e alertas
• Programe backups frequentes e implemente um serviço de alerta (como via cron + e-mail) quando um DROP for emitido (via log de auditoria).
⸻
8. Evite que o próprio dono tenha permissão para apagar
• Após a criação do banco, troque o dono para uma role que não seja usada no dia a dia:
REASSIGN OWNED BY adriano TO admin_role;
• Assim, adriano não poderá dropar o banco que não possui.
⸻
Resumo de proteção máxima
Medida Finalidade
Revogar SUPERUSER,
CREATEDB Evita ações perigosas
Restrição em pg_hba.conf
Bloqueia acessos não autorizados
Log de DDL
Auditoria de DROP/ALTER
Usuário dono protegido
Evita DROP mesmo pelo criador
Backup automático
Recuperação rápida
Alerta de comando
Detecção em tempo real
⸻
--
Adriano José Boller
______________________________________________
Consultor e Representante Oficial da
PcSoft no Brasil
+55 (41) 99949 1800
adrianoboller@gmail.com
skype: adrianoboller
http://wxinformatica.com.br/Message modified, May, 05 2025 - 5:22 AM |
| |
| |
| | | |
|
| | |
| |
Registered member
3,963 messages |
|
| Posted on May, 05 2025 - 5:28 AM |
Anti Drop Database
#!/bin/bash
# Script de proteção PostgreSQL contra DROP DATABASE não autorizado
# Requisitos: executar como root ou usuário com permissão para editar arquivos do PostgreSQL
# Caminho dos arquivos
PG_HBA="/etc/postgresql/15/main/pg_hba.conf"
PG_CONF="/etc/postgresql/15/main/postgresql.conf"
BACKUP_DIR="/var/backups/pgsql"
# 1. Revogar permissões perigosas de usuários comuns
sudo -u postgres psql -c "ALTER ROLE juliana NOSUPERUSER NOCREATEDB NOCREATEROLE;"
# 2. Restringir acessos via pg_hba.conf
echo "# Bloquear acessos externos não autorizados" >> $PG_HBA
echo "host all all 192.168.1.0/24 scram-sha-256" >> $PG_HBA
# 3. Configurar log de comandos perigosos
sudo sed -i "s/^#*log_statement.*/log_statement = 'ddl'/" $PG_CONF
sudo sed -i "s/^#*log_line_prefix.*/log_line_prefix = '%m [%p] %q%u@%d'/" $PG_CONF
# 4. Criar usuário protegido sem permissão de DROP
sudo -u postgres psql -c "CREATE ROLE admin_role WITH LOGIN PASSWORD 'senha_muito_segura';"
sudo -u postgres psql -c "REASSIGN OWNED BY adriano TO admin_role;"
# 5. Reiniciar o PostgreSQL
echo "Reiniciando PostgreSQL para aplicar configurações..."
sudo systemctl restart postgresql
# 6. Configurar backup automático diário
mkdir -p $BACKUP_DIR
echo '0 2 * * * postgres pg_dumpall -U postgres | gzip > $BACKUP_DIR/pgsql_backup_$(date +\%F).sql.gz' > /etc/cron.d/pgsql_backup
# 7. Aplicar permissões seguras no diretório do banco de dados
chown -R postgres:postgres /var/lib/postgresql
chmod -R go-rwx /var/lib/postgresql
echo "Configuração concluída. Proteções contra DROP DATABASE aplicadas com sucesso."
--
Adriano José Boller
______________________________________________
Consultor e Representante Oficial da
PcSoft no Brasil
+55 (41) 99949 1800
adrianoboller@gmail.com
skype: adrianoboller
http://wxinformatica.com.br/ |
| |
| |
| | | |
|
| | |
| |
Registered member
3,963 messages |
|
| Posted on May, 05 2025 - 5:34 AM |
Sobre o PgAgent ou Job
O pgAgent é um job scheduler (agendador de tarefas) para o PostgreSQL, semelhante ao cron no Linux ou ao Agendador de Tarefas no Windows, mas integrado ao ecossistema do banco de dados.
Principais funções do pgAgent:
1. Agendar scripts SQL ou de sistema:
• Você pode agendar scripts SQL para rodar dentro do PostgreSQL (ex: manutenção, limpeza de dados, backups lógicos).
• Ou scripts externos (bash, PowerShell, batch) para executar comandos no sistema operacional.
2. Gerenciar tarefas recorrentes:
• Execução diária, semanal, por intervalo de minutos, por horário específico, etc.
3. Registrar logs de execução:
• Armazena o histórico das execuções com sucesso ou falha diretamente no banco.
4. Multi-plataforma:
• Funciona em Windows e Linux.
5. Integração com pgAdmin:
• É totalmente gerenciável através da interface do pgAdmin (normalmente acessado via “Jobs”).
⸻
Exemplos de uso do pgAgent:
• Executar uma procedure SQL de limpeza de registros antigos todos os dias às 23h.
• Rodar pg_dump automaticamente e mover os arquivos para um diretório de backup.
• Atualizar visões materializadas de hora em hora.
• Checar status de replicação ou tamanho de tabelas.
⸻
Instalação (resumo):
# No Debian/Ubuntu
sudo apt install pgagent
# Em seguida, crie a extensão no banco de dados:
psql -U postgres -d seu_banco -c "CREATE EXTENSION pgagent;"
Perfeito! Abaixo está um exemplo completo e prático de como agendar uma tarefa com o pgAgent para rodar um script SQL todos os dias às 23h.
⸻
Objetivo:
Executar diariamente às 23h a seguinte SQL:
DELETE FROM erp.log_auditoria WHERE data_hora < NOW() - INTERVAL '90 days';
⸻
1. Pré-requisitos:
• PostgreSQL instalado e configurado.
• pgAgent instalado e em execução como serviço.
• Extensão pgagent criada no banco:
CREATE EXTENSION pgagent;
⸻
2. Criando o job via SQL:
-- Criar o job
INSERT INTO pgagent.pga_job (jobid, jobname, jobdesc, jobenabled, jobhostagent)
VALUES (DEFAULT, 'limpeza_log_auditoria', 'Remove logs com mais de 90 dias', true, '');
-- Obter o ID do job recém-criado
SELECT jobid FROM pgagent.pga_job WHERE jobname = 'limpeza_log_auditoria';
Suponha que o jobid retornado foi 1.
⸻
3. Criar o step (passo) que executa o SQL:
INSERT INTO pgagent.pga_jobstep (jstjobid, jstname, jstdesc, jstenabled, jstkind, jstcode, jstdbname)
VALUES (
1,
'step_delete_logs',
'Deleta registros antigos do log',
true,
's', -- 's' = SQL, 'b' = batch/shell script
'DELETE FROM erp.log_auditoria WHERE data_hora < NOW() - INTERVAL ''90 days'';',
'db_erp'
);
⸻
4. Criar o schedule (horário):
INSERT INTO pgagent.pga_schedule (jscjobid, jscname, jscdesc, jscenabled)
VALUES (1, 'schedule_diario_23h', 'Execução diária às 23h', true);
-- Obter o ID do agendamento
SELECT jscid FROM pgagent.pga_schedule WHERE jscname = 'schedule_diario_23h';
Suponha que o jscid seja 1.
⸻
5. Definir o tempo de execução:
-- Executar todos os dias
INSERT INTO pgagent.pga_schedays (sdyjscid, sdyday)
VALUES (1, 0); -- 0 = todo dia
-- Executar às 23h
INSERT INTO pgagent.pga_schedtimes (sctjscid, scthour, sctminute)
VALUES (1, 23, 0);
⸻
Verificando execução:
Os logs ficam armazenados na tabela:
SELECT * FROM pgagent.pga_joblog ORDER BY jlgstart DESC LIMIT 10;
--
Adriano José Boller
______________________________________________
Consultor e Representante Oficial da
PcSoft no Brasil
+55 (41) 99949 1800
adrianoboller@gmail.com
skype: adrianoboller
http://wxinformatica.com.br/ |
| |
| |
| | | |
|
| | |
| |
Registered member
3,963 messages |
|
| Posted on May, 05 2025 - 5:37 AM |
Claro! Aqui está um resumo objetivo e profissional sobre os utilitários de backup e restauração do PostgreSQL:
⸻
Resumo: pg_dump, pg_dumpall e pg_restore
1. pg_dump
Utilitário para backup lógico de um único banco de dados.
• Formato de saída: texto SQL, tar, custom (.backup)
• Permite: backup completo ou parcial (schema, dados, tabelas específicas)
• Suporta: compressão, paralelismo (com --jobs)
• Restaurado com: psql (modo texto) ou pg_restore (modo custom/tar)
Exemplos:
# Backup completo em texto
pg_dump -U postgres -d db_erp -f backup_db_erp.sql
# Backup em formato custom (compactado)
pg_dump -U postgres -d db_erp -F c -f backup_db_erp.backup
# Backup apenas de uma tabela
pg_dump -U postgres -d db_erp -t erp.clientes -F c -f clientes.backup
⸻
2. pg_restore
Utilitário para restaurar backups criados com pg_dump nos formatos custom ou tar.
• Permite: restauração seletiva (ex: somente schema ou dados), paralelismo com --jobs
• Suporta: restauração em base existente ou nova
Exemplos:
# Restaurar um backup completo
pg_restore -U postgres -d db_erp_restaurado backup_db_erp.backup
# Restaurar apenas estrutura (sem dados)
pg_restore -U postgres -d db_erp_restaurado --schema-only backup_db_erp.backup
# Restaurar somente uma tabela
pg_restore -U postgres -d db_erp_restaurado -t erp.clientes backup_db_erp.backup
⸻
3. pg_dumpall
Utilitário para backup lógico de todos os bancos de dados, incluindo:
• Todos os bancos
• Usuários e permissões
• Tablespaces
• Configurações globais
• Saída: apenas formato texto SQL (não é compatível com pg_restore)
• Restaurado com: psql
Exemplo:
# Backup completo do cluster PostgreSQL
pg_dumpall -U postgres -f backup_full_cluster.sql
# Incluir globals (usuários, roles, etc.)
pg_dumpall -U postgres --globals-only -f globals.sql
⸻
Resumo comparativo:
Utilitário Escopo Formato Restauração com Suporta Paralelo
pg_dump Um banco específico SQL, tar, custom psql / pg_restore Sim (custom/tar)
pg_restore Restaura .backup/tar - - Sim
pg_dumpall Todos os bancos + roles Somente SQL psql Não
⸻
#!/bin/bash
# Diretório de backup
BACKUP_DIR="/var/backups/postgresql"
LOG_FILE="$BACKUP_DIR/backup_log_$(date +%Y%m%d).log"
DATE=$(date +%Y%m%d_%H%M%S)
# Nome do banco de dados
DB_NAME="db_erp"
USER="postgres"
# Caminho do arquivo de backup
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_${DATE}.backup"
# Criar diretório de backup se não existir
mkdir -p "$BACKUP_DIR"
# Executar o pg_dump com compactação e log
echo "Iniciando backup em $DATE..." | tee -a "$LOG_FILE"
pg_dump -U $USER -d $DB_NAME -F c -j 4 -f "$BACKUP_FILE" 2>>"$LOG_FILE"
if [ $? -eq 0 ]; then
echo "Backup concluído com sucesso: $BACKUP_FILE" | tee -a "$LOG_FILE"
else
echo "Erro durante o backup. Verifique o log em $LOG_FILE" | tee -a "$LOG_FILE"
exit 1
fi
# Exemplo de restauração (opcional)
# pg_restore -U $USER -d novo_banco -j 4 "$BACKUP_FILE" 2>>"$LOG_FILE"
--
Adriano José Boller
______________________________________________
Consultor e Representante Oficial da
PcSoft no Brasil
+55 (41) 99949 1800
adrianoboller@gmail.com
skype: adrianoboller
http://wxinformatica.com.br/ |
| |
| |
| | | |
|
| | |
| |
Registered member
3,963 messages |
|
| Posted on May, 05 2025 - 5:55 AM |
Não é possível desativar diretamente o comando DROP DATABASE no PostgreSQL, pois ele é uma instrução DDL de alto nível que não pode ser interceptada por triggers ou restrições padrão dentro do próprio banco de dados. No entanto, existem alternativas eficazes para impedir ou dificultar ao máximo a execução desse comando:
⸻
1. Remova permissões de superusuário
Apenas o dono da base ou um superusuário pode executar DROP DATABASE. Para proteger:
REVOKE CREATEDB ON DATABASE db_erp FROM PUBLIC;
REASSIGN OWNED BY juliana TO postgres;
⸻
2. Torne o dono da base um usuário restrito
Mude o dono da base para um usuário técnico sem acesso direto:
ALTER DATABASE db_erp OWNER TO system_user;
E em seguida:
REVOKE CONNECT ON DATABASE db_erp FROM system_user;
⸻
3. Proteja fisicamente os arquivos com chattr (Linux only)
No sistema operacional:
sudo chattr +i /var/lib/postgresql/15/main/base/your_database_oid
Isso torna o diretório do banco imutável. Nem mesmo o processo do PostgreSQL poderá apagar os arquivos (requer root para remover com chattr -i).
⸻
4. Use pg_hba.conf para limitar acesso
Desative acesso direto ao banco por IPs ou usuários indesejados:
# Apenas o IP da aplicação e os usuários autorizados
host db_erp juliana 192.168.1.0/24 md5
⸻
5. Crie políticas com pgaudit
Audite qualquer comando DROP:
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'ddl'
Isso registra em log toda tentativa de DDL.
⸻
6. Envolva o banco em uma replicação
Se o banco for replicado (streaming/hot standby), mesmo que a base primária seja dropada, o slave manterá uma cópia segura.
⸻
7. Use uma role fake como proprietária
CREATE ROLE bloqueio_drop NOLOGIN;
ALTER DATABASE db_erp OWNER TO bloqueio_drop;
Ninguém pode logar como bloqueio_drop, então ninguém pode dropar essa base.
⸻
Resumo da abordagem mais segura:
1. Altere o dono da base para uma role NOLOGIN
2. Remova todos os privilégios administrativos dos usuários comuns
3. Configure backups automáticos
4. Restrinja o acesso ao binário dropdb via ACL ou política de SO
⸻
#!/bin/bash
# Script de Backup PostgreSQL com pg_dump
# Gerado em: 2025-05-05 03:52:23
# Configurações
PG_USER="readonly_user"
PG_PASSWORD="SenhaSegura123!"
PG_HOST="localhost"
PG_PORT="5432"
PG_DB="db_erp"
BACKUP_DIR="/var/backups/postgresql"
BACKUP_NAME="db_erp_$(date +%Y%m%d_%H%M%S).sql.gz"
# Exporta a senha para não interagir com o prompt
export PGPASSWORD="$PG_PASSWORD"
# Cria diretório se não existir
mkdir -p "$BACKUP_DIR"
# Executa o backup com compressão
pg_dump -U "$PG_USER" -h "$PG_HOST" -p "$PG_PORT" -d "$PG_DB" | gzip > "$BACKUP_DIR/$BACKUP_NAME"
# Verifica sucesso
if [ $? -eq 0 ]; then
echo "Backup realizado com sucesso: $BACKUP_NAME"
else
echo "Erro ao realizar o backup de $PG_DB"
fi
# Remove backups antigos com mais de 7 dias
find "$BACKUP_DIR" -type f -name "*.sql.gz" -mtime +7 -exec rm -f {} \;
# Fim do script
--
Adriano José Boller
______________________________________________
Consultor e Representante Oficial da
PcSoft no Brasil
+55 (41) 99949 1800
adrianoboller@gmail.com
skype: adrianoboller
http://wxinformatica.com.br/ |
| |
| |
| | | |
|
| | |
| |
Registered member
3,963 messages |
|
| Posted on May, 05 2025 - 6:01 AM |
E possível modificar o código-fonte do PostgreSQL para desativar ou restringir o comando DROP DATABASE, mas isso envolve:
1. Alterar o código-fonte C do PostgreSQL
O PostgreSQL é escrito em C.
O comando DROP DATABASE é implementado na função dropdb dentro do arquivo:
src/backend/commands/dbcommands.c
Você poderia, por exemplo, modificar esse trecho:
if (!have_createdb_privilege())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied to drop database")));
Ou adicionar uma nova condição, como:
if (strcmp(dbname, "db_erp") == 0)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("DROP DATABASE is disabled for db_erp")));
2. Recompilar o PostgreSQL
Após a alteração, você precisará:
./configure
make
sudo make install
3. Cuidados
• A alteração afeta a manutenção futura: upgrades automáticos deixam de ser seguros.
• Muda o comportamento padrão do PostgreSQL — você está criando um “fork”.
• Pode causar incompatibilidade com ferramentas administrativas.
Alternativas mais seguras:
• Revogar privilégios de DROP DATABASE usando roles e ALTER DATABASE ... OWNER TO.
• Monitorar e interceptar via pgaudit.
• Bloquear o comando via firewall de SQL (como pgBouncer com políticas).
• Criar um event trigger de ddl_command_end para auditar.
Criando um Patch
--- a/src/backend/commands/dbcommands.c
+++ b/src/backend/commands/dbcommands.c
@@
#include "miscadmin.h"
#include "postmaster/bgworker.h"
#include "storage/fd.h"
@@ void dropdb(ParseState *pstate, DropdbStmt *stmt)
{
const char *dbname = stmt->dbname;
+ /* Bloquear DROP DATABASE para uma base específica */
+ if (strcmp(dbname, "db_erp") == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("DROP DATABASE is desabilitado para db_erp por política de segurança.")));
if (!have_createdb_privilege())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
——-
O patch está pronto:
Clique aqui para baixar o patch C que bloqueia DROP DATABASE na db_erp
Como aplicar:
1. Acesse o diretório do código-fonte do PostgreSQL.
2. Aplique o patch com:
patch -p1 < patch_block_drop_db.patch
3. Recompile:
./configure
make
sudo make install
——
Isso para Linux ok
--
Adriano José Boller
______________________________________________
Consultor e Representante Oficial da
PcSoft no Brasil
+55 (41) 99949 1800
adrianoboller@gmail.com
skype: adrianoboller
http://wxinformatica.com.br/ |
| |
| |
| | | |
|
| | |
| |
Registered member
3,963 messages |
|
| Posted on May, 05 2025 - 6:11 AM |
Para desabilitar o Drop de database do Podtgresql Windows
Sim, é possível modificar o código-fonte do PostgreSQL no Windows para desativar o comando DROP DATABASE. No entanto, esse processo é complexo e requer um ambiente de desenvolvimento específico. Abaixo, apresento um guia passo a passo para realizar essa modificação:
⸻
🛠️ Etapas para Modificar o PostgreSQL no Windows
1. Preparar o Ambiente de Desenvolvimento
Você precisará instalar as seguintes ferramentas:
• Visual Studio 2019 ou superior com o workload “Desenvolvimento para Desktop com C++”.
• Strawberry Perl: Necessário para scripts de build. Disponível em strawberryperl.com.
• Bison e Flex: Utilitários para geração de analisadores sintáticos. Podem ser instalados via MSYS2 ou MinGW.
• Git: Para clonar o repositório do PostgreSQL. Disponível em git-scm.com.
2. Clonar o Código-Fonte do PostgreSQL
Abra o prompt de comando e execute:
git clone https://github.com/postgres/postgres.git
cd postgres
3. Aplicar a Modificação no Código-Fonte
Localize e edite o arquivo src/backend/commands/dbcommands.c. Dentro da função dropdb, adicione a seguinte verificação para impedir a exclusão do banco de dados db_erp:
if (strcmp(dbname, "db_erp") == 0)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("DROP DATABASE está desativado para db_erp.")));
4. Compilar o PostgreSQL
1. Abra o “Developer Command Prompt for VS” correspondente à sua versão do Visual Studio.
2. Navegue até o diretório src\tools\msvc dentro do repositório clonado.
3. Execute o script de build:
perl build.pl
Esse comando gerará os arquivos de solução (.sln) para o Visual Studio.
4. Abra o arquivo pgsql.sln no Visual Studio.
5. Compile a solução (Build Solution) para gerar os binários modificados.
5. Instalar os Binários Modificados
1. Pare o serviço do PostgreSQL existente no Windows.
2. Substitua os executáveis originais pelos binários recém-compilados.
3. Reinicie o serviço do PostgreSQL.
⸻
⚠️ Considerações Importantes
• Manutenção: Atualizações futuras do PostgreSQL exigirão que você reaplique essas modificações e recompile o código.
• Risco de Instabilidade: Alterações no código-fonte podem introduzir instabilidades se não forem cuidadosamente implementadas.
• Ambiente de Teste: Recomenda-se testar as modificações em um ambiente de desenvolvimento antes de aplicá-las em produção.
⸻
✅ Alternativas Mais Seguras
Antes de optar por modificar o código-fonte, considere as seguintes alternativas:
• Gerenciamento de Permissões: Revogue privilégios de DROP DATABASE para usuários não autorizados.
• Monitoramento com pgAudit: Utilize a extensão pgAudit para auditar comandos DDL, incluindo DROP DATABASE.
• Triggers de Auditoria: Embora o PostgreSQL não suporte triggers diretamente em comandos DDL como DROP DATABASE, você pode monitorar logs para detectar e responder a tais eventos.
⸻
Sim, é possível modificar o código-fonte do PostgreSQL no Windows, mas o processo é bem mais complexo do que no Linux. Aqui estão os pontos principais:
⸻
1. Requisitos para compilar PostgreSQL no Windows
Você vai precisar:
• Código-fonte do PostgreSQL (disponível no GitHub oficial)
• Microsoft Visual Studio (VS2019 ou mais recente) com:
• Desktop development with C++
• Perl, Python, Bison, Flex, MSYS2, Strawberry Perl
• NASM (em alguns casos)
• Git Bash ou MSYS Shell
⸻
2. Passos resumidos para compilar com modificação
1. Clone o repositório PostgreSQL:
git clone https://github.com/postgres/postgres.git
cd postgres
2. Edite o arquivo src/backend/commands/dbcommands.c
• No início da função dropdb, adicione:
if (strcmp(dbname, "db_erp") == 0)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("DROP DATABASE está desativado para db_erp.")));
3. Abra o terminal “Developer Command Prompt for VS”
• Configure o ambiente usando vcvars64.bat
4. Configure e compile:
cd src/tools/msvc
perl build.pl
Isso irá gerar os binários PostgreSQL modificados.
5. Substitua os binários instalados:
• Pare o serviço PostgreSQL no Windows.
• Substitua os arquivos postgres.exe, pg_ctl.exe, etc.
• Reinicie o serviço.
⸻
3. Considerações importantes
• Risco de instabilidade: se errar no código C, o PostgreSQL não compila ou pode quebrar.
• Atualizações complexas: toda nova versão exigirá repetir esse processo.
• Backups obrigatórios antes de testar.
• Recomendo fazer isso apenas em ambiente corporativo controlado ou embarcado.
⸻
Alternativas no Windows (sem recompilar):
• Bloqueie DROP DATABASE com políticas de permissão.
• Use extensões como pgaudit + monitoramento em tempo real.
• Intercepte comandos com event triggers (não funcionam diretamente para DROP DATABASE, mas podem logar DDLs).
⸻
Excelente ideia, Adriano. A proposta de usar um arquivo .ini para configurar permissões por banco e bloquear comandos perigosos (DROP, TRUNCATE, DELETE) é viável, embora exija alterações profundas no código-fonte do PostgreSQL. Vamos estruturar isso passo a passo:
⸻
1. Exemplo do Arquivo .ini no Diretório /bin
Nome sugerido: restrict_commands.ini
[db_erp]
drop=false
truncate=false
delete=false
[db_bi]
drop=false
truncate=true
delete=true
[db_teste]
drop=true
truncate=true
delete=false
Explicação:
• false = comando bloqueado para este banco
• true = comando permitido
⸻
2. Arquivos do PostgreSQL a Modificar
Você precisará editar os módulos responsáveis pelos comandos DDL e DML:
DROP DATABASE
• Arquivo: src/backend/commands/dbcommands.c
• Função: dropdb(...)
TRUNCATE
• Arquivo: src/backend/commands/tablecmds.c
• Função: ExecuteTruncate(...)
DELETE
• Arquivo: src/backend/executor/execMain.c
• Função: ExecDelete(...) ou standard_ExecutorStart(...) para interceptar comandos
⸻
3. Lógica que Você Deve Incluir em Cada Função
Exemplo em C para ler e validar o .ini (simplificado):
#include <stdio.h>
#include <string.h>
bool comando_permitido(const char *dbname, const char *operacao)
{
FILE *fp = fopen("C:/Program Files/PostgreSQL/16/bin/restrict_commands.ini", "r");
if (!fp) return true; // Permitir por padrão se não encontrar
char linha[256];
bool secao_alvo = false;
char secao[64];
snprintf(secao, sizeof(secao), "[%s]", dbname);
while (fgets(linha, sizeof(linha), fp)) {
if (strstr(linha, secao)) {
secao_alvo = true;
continue;
}
if (secao_alvo) {
if (linha[0] == '[') break; // fim da seção
if (strstr(linha, operacao)) {
fclose(fp);
return strstr(linha, "true") != NULL;
}
}
}
fclose(fp);
return true;
}
Depois, dentro da função de dropdb, por exemplo:
if (!comando_permitido(get_database_name(MyDatabaseId), "drop")) {
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("Comando DROP não permitido para este banco de dados.")));
}
⸻
4. Resumo Técnico do que será necessário:
Comando Arquivo Fonte Função Principal
DROP src/backend/commands/dbcommands.c dropdb(...)
TRUNCATE src/backend/commands/tablecmds.c ExecuteTruncate(...)
DELETE src/backend/executor/execMain.c ExecDelete(...) ou interceptar ExecInitModifyTable(...)
Você pode centralizar o carregamento do .ini numa função utilitária e torná-la acessível para múltiplos arquivos.
⸻
Aqui está o conteúdo sugerido para o seu arquivo INI (restrict_commands.ini) e a descrição do ponto de modificação no código-fonte do PostgreSQL:
⸻
Arquivo INI (ex: C:\Program Files\PostgreSQL\15\bin\restrict_commands.ini)
[db_financeiro]
drop=false
truncate=false
delete=false
[db_erp]
drop=false
truncate=true
delete=true
[db_analytics]
drop=true
truncate=false
delete=false
⸻
Código a ser adicionado/modificado no PostgreSQL
Arquivo alvo:
src/backend/commands/dbcommands.c
Função a ser modificada: DropDatabase()
Resumo da modificação:
Interceptamos a chamada DropDatabase() e verificamos se o banco atual possui permissão para ser removido com base no conteúdo de um arquivo INI externo.
Trecho base já simulado:
bool check_permission(const char *dbname, const char *operation) {
FILE *fp = fopen("C:/Program Files/PostgreSQL/15/bin/restrict_commands.ini", "r");
if (!fp) return true;
char line[256];
bool in_section = false;
char section[128];
snprintf(section, sizeof(section), "[%s]", dbname);
while (fgets(line, sizeof(line), fp)) {
if (strncmp(line, section, strlen(section)) == 0) {
in_section = true;
continue;
}
if (in_section && line[0] == '[') break;
if (in_section && strstr(line, operation)) {
if (strstr(line, "false")) {
fclose(fp);
return false;
}
}
}
fclose(fp);
return true;
}
Depois disso, no início da DropDatabase(...), adicione:
if (!check_permission(dbname, "drop")) {
ereport(ERROR, (errmsg("DROP DATABASE is disabled for %s by policy", dbname)));
}
⸻
Resultado:
Você conseguirá restringir via arquivo externo a execução de comandos destrutivos em bancos específicos no PostgreSQL mesmo que estejam rodando no Windows.
--
Adriano José Boller
______________________________________________
Consultor e Representante Oficial da
PcSoft no Brasil
+55 (41) 99949 1800
adrianoboller@gmail.com
skype: adrianoboller
http://wxinformatica.com.br/ |
| |
| |
| | | |
|
| | |
| |
Registered member
3,963 messages |
|
| Posted on May, 05 2025 - 6:15 AM |
Arquivo ini de configuração:
[db_financeiro]
drop=false
truncate=false
delete=false
[db_erp]
drop=false
truncate=true
delete=true
[db_analytics]
drop=true
truncate=false
delete=false
Alteração nos fontes em c
/*
* Adição ao arquivo: src/backend/commands/dbcommands.c
* Intercepta DROP DATABASE com base no INI externo
*/
#include "utils/guc.h"
#include <stdio.h>
#include <string.h>
bool check_permission(const char *dbname, const char *operation) {
FILE *fp = fopen("C:/Program Files/PostgreSQL/15/bin/restrict_commands.ini", "r");
if (!fp) return true; // se não conseguir abrir, libera o comando
char line[256];
bool in_section = false;
char section[128];
snprintf(section, sizeof(section), "[%s]", dbname);
while (fgets(line, sizeof(line), fp)) {
if (strncmp(line, section, strlen(section)) == 0) {
in_section = true;
continue;
}
if (in_section && line[0] == '[') {
break; // fim da seção
}
if (in_section && strstr(line, operation)) {
if (strstr(line, "false")) {
fclose(fp);
return false;
}
}
}
fclose(fp);
return true;
}
// Dentro de DropDatabase(...)
if (!check_permission(dbname, "drop")) {
ereport(ERROR, (errmsg("DROP DATABASE is disabled for %s by policy", dbname)));
}
--
Adriano José Boller
______________________________________________
Consultor e Representante Oficial da
PcSoft no Brasil
+55 (41) 99949 1800
adrianoboller@gmail.com
skype: adrianoboller
http://wxinformatica.com.br/ |
| |
| |
| | | |
|
| | |
| |
Registered member
3,963 messages |
|
| Posted on May, 05 2025 - 6:30 AM |
Boa noite
Solicito a permissão de expor essa minha ideia:
Quero propor a seguinte alteração nos fontes do PostgreSQL
Sugestão:
Arquivo ini de configuração:
C:/Program Files/PostgreSQL/15/bin/restrict_commands.ini
[db_financeiro]
drop=false
truncate=false
delete=false
[db_erp]
drop=false
truncate=true
delete=true
[db_analytics]
drop=true
truncate=false
delete=false
Alteração nos fontes em c
/*
* Adição ao arquivo: src/backend/commands/dbcommands.c
* Intercepta DROP DATABASE com base no INI externo
*/
#include "utils/guc.h"
#include <stdio.h>
#include <string.h>
bool check_permission(const char *dbname, const char *operation) {
FILE *fp = fopen("C:/Program Files/PostgreSQL/15/bin/restrict_commands.ini", "r");
if (!fp) return true; // se não conseguir abrir, libera o comando
char line[256];
bool in_section = false;
char section[128];
snprintf(section, sizeof(section), "[%s]", dbname);
while (fgets(line, sizeof(line), fp)) {
if (strncmp(line, section, strlen(section)) == 0) {
in_section = true;
continue;
}
if (in_section && line[0] == '[') {
break; // fim da seção
}
if (in_section && strstr(line, operation)) {
if (strstr(line, "false")) {
fclose(fp);
return false;
}
}
}
fclose(fp);
return true;
}
// Dentro de DropDatabase(...)
if (!check_permission(dbname, "drop")) {
ereport(ERROR, (errmsg("DROP DATABASE is disabled for %s by policy", dbname)));
}
//—————
Dessa forma ninguém poderá dropar o banco sem alterar o arquivo ini pois a conexão vai vir por TCP/IP e porta esse arquivo não estará visível para alteração sendo assim uma garantia adicional. Proposta caso tenha a execução do código de drop, truncate ou delete gere um log quem fez o comando.
Idem para os fontes do Linux, macOS e outros.
Assim:
/*
* Modificação em src/backend/commands/dbcommands.c
* Intercepta DROP DATABASE com base em INI externo e registra tentativas
*/
#include "utils/guc.h"
#include <stdio.h>
#include <string.h>
#include <time.h>
#include <unistd.h>
bool check_permission(const char *dbname, const char *operation) {
FILE *fp = fopen("C:/Program Files/PostgreSQL/15/bin/restrict_commands.ini", "r");
if (!fp) return true;
char line[256];
bool in_section = false;
char section[128];
snprintf(section, sizeof(section), "[%s]", dbname);
while (fgets(line, sizeof(line), fp)) {
if (strncmp(line, section, strlen(section)) == 0) {
in_section = true;
continue;
}
if (in_section && line[0] == '[') {
break;
}
if (in_section && strstr(line, operation)) {
if (strstr(line, "false")) {
fclose(fp);
log_violation(dbname, operation);
return false;
}
}
}
fclose(fp);
return true;
}
void log_violation(const char *dbname, const char *operation) {
char path[512];
snprintf(path, sizeof(path), "C:/Program Files/PostgreSQL/15/data/%s_command_blocked.log", dbname);
FILE *logf = fopen(path, "a");
if (!logf) return;
time_t now = time(NULL);
char *timestamp = ctime(&now);
timestamp[strlen(timestamp) - 1] = 0;
const char *user = getenv("USERNAME");
if (!user) user = "unknown";
fprintf(logf, "[%s] USER: %s attempted: %s\n", timestamp, user, operation);
fclose(logf);
}
// Exemplo de uso no ponto de DROP:
if (!check_permission(dbname, "drop")) {
ereport(ERROR, (errmsg("DROP DATABASE is disabled for %s by policy", dbname)));
}
Assim registra o usuário, a data/hora e o comando proibido tentado em um .log localizado na pasta data/ do banco de dados.
--
Adriano José Boller
______________________________________________
Consultor e Representante Oficial da
PcSoft no Brasil
+55 (41) 99949 1800
adrianoboller@gmail.com
skype: adrianoboller
http://wxinformatica.com.br/ |
| |
| |
| | | |
|
| | |
| |
Registered member
3,963 messages |
|
| Posted on May, 05 2025 - 6:33 AM |
Excelente proposta, Adriano! Abaixo está a versão adaptada para Linux da sua sugestão de interceptação de comandos DROP, TRUNCATE e DELETE via arquivo .ini, com registro em log na pasta data/ da base PostgreSQL.
⸻
Arquivo INI de configuração (exemplo):
/usr/local/pgsql/bin/restrict_commands.ini
[db_financeiro]
drop=false
truncate=false
delete=false
[db_erp]
drop=false
truncate=true
delete=true
[db_analytics]
drop=true
truncate=false
delete=false
⸻
Código C adaptado para Linux (ex: src/backend/commands/dbcommands.c):
#include "utils/guc.h"
#include <stdio.h>
#include <string.h>
#include <time.h>
#include <unistd.h>
#include <stdlib.h>
#include <pwd.h>
#define INI_PATH "/usr/local/pgsql/bin/restrict_commands.ini"
#define DATA_DIR "/usr/local/pgsql/data"
bool check_permission(const char *dbname, const char *operation);
void log_violation(const char *dbname, const char *operation);
bool check_permission(const char *dbname, const char *operation) {
FILE *fp = fopen(INI_PATH, "r");
if (!fp) return true; // Se o INI não existe, não bloqueia
char line[256];
bool in_section = false;
char section[128];
snprintf(section, sizeof(section), "[%s]", dbname);
while (fgets(line, sizeof(line), fp)) {
if (strncmp(line, section, strlen(section)) == 0) {
in_section = true;
continue;
}
if (in_section && line[0] == '[') {
break; // Sai da seção
}
if (in_section && strstr(line, operation)) {
if (strstr(line, "false")) {
fclose(fp);
log_violation(dbname, operation);
return false;
}
}
}
fclose(fp);
return true;
}
void log_violation(const char *dbname, const char *operation) {
char path[512];
snprintf(path, sizeof(path), DATA_DIR "/%s_command_blocked.log", dbname);
FILE *logf = fopen(path, "a");
if (!logf) return;
time_t now = time(NULL);
char timestamp[64];
strftime(timestamp, sizeof(timestamp), "%Y-%m-%d %H:%M:%S", localtime(&now));
struct passwd *pw = getpwuid(getuid());
const char *user = pw ? pw->pw_name : "unknown";
fprintf(logf, "[%s] USER: %s attempted: %s\n", timestamp, user, operation);
fclose(logf);
}
⸻
Exemplo de uso nos pontos críticos:
Em DropDatabase(...):
if (!check_permission(dbname, "drop")) {
ereport(ERROR, (errmsg("DROP DATABASE is disabled for %s by policy", dbname)));
}
Em ExecuteTruncate(...):
if (!check_permission(get_database_name(MyDatabaseId), "truncate")) {
ereport(ERROR, (errmsg("TRUNCATE is disabled for this database by policy")));
}
Em ExecDelete(...) ou similar:
if (!check_permission(get_database_name(MyDatabaseId), "delete")) {
ereport(ERROR, (errmsg("DELETE is disabled for this database by policy")));
}
⸻
Benefícios dessa implementação:
• Camada nativa de proteção ao PostgreSQL.
• Log detalhado com nome do usuário e horário.
• Controle descentralizado por arquivo .ini.
• Funciona bem tanto em ambiente Windows quanto Linux com ajustes de path.
• Pode ser acoplado a políticas de compliance.
——
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c
index abcdef1..1234567 100644
--- a/src/backend/commands/dbcommands.c
+++ b/src/backend/commands/dbcommands.c
@@ -50,6 +50,11 @@
#include "utils/rel.h"
#include "utils/syscache.h"
#include "utils/tqual.h"
+
+#include <stdio.h>
+#include <string.h>
+#include <time.h>
+#include <unistd.h>
+#include <stdlib.h>
+#include <pwd.h>
+
+bool check_permission(const char *dbname, const char *operation);
+void log_violation(const char *dbname, const char *operation);
@@ static void DropDatabase(...)
+ if (!check_permission(dbname, "drop")) {
+ ereport(ERROR, (errmsg("DROP DATABASE is disabled for %s by policy", dbname)));
+ }
+bool check_permission(const char *dbname, const char *operation) {
+ FILE *fp = fopen("/usr/local/pgsql/bin/restrict_commands.ini", "r");
+ if (!fp) return true;
+
+ char line[256];
+ bool in_section = false;
+ char section[128];
+ snprintf(section, sizeof(section), "[%s]", dbname);
+
+ while (fgets(line, sizeof(line), fp)) {
+ if (strncmp(line, section, strlen(section)) == 0) {
+ in_section = true;
+ continue;
+ }
+ if (in_section && line[0] == '[') {
+ break;
+ }
+ if (in_section && strstr(line, operation)) {
+ if (strstr(line, "false")) {
+ fclose(fp);
+ log_violation(dbname, operation);
+ return false;
+ }
+ }
+ }
+ fclose(fp);
+ return true;
+}
+void log_violation(const char *dbname, const char *operation) {
+ char path[512];
+ snprintf(path, sizeof(path), "/usr/local/pgsql/data/%s_command_blocked.log", dbname);
+
+ FILE *logf = fopen(path, "a");
+ if (!logf) return;
+
+ time_t now = time(NULL);
+ char timestamp[64];
+ strftime(timestamp, sizeof(timestamp), "%Y-%m-%d %H:%M:%S", localtime(&now));
+
+ struct passwd *pw = getpwuid(getuid());
+ const char *user = pw ? pw->pw_name : "unknown";
+
+ fprintf(logf, "[%s] USER: %s attempted: %s\n", timestamp, user, operation);
+ fclose(logf);
+}
--
Adriano José Boller
______________________________________________
Consultor e Representante Oficial da
PcSoft no Brasil
+55 (41) 99949 1800
adrianoboller@gmail.com
skype: adrianoboller
http://wxinformatica.com.br/ |
| |
| |
| | | |
|
| | |
| |
Registered member
3,963 messages |
|
| Posted on May, 05 2025 - 6:36 AM |
Para compilar o PostgreSQL modificado no Windows com a sua alteração (bloqueio de DROP, TRUNCATE e DELETE com restrict_commands.ini), você vai precisar configurar um ambiente de compilação adequado. Aqui estão os requisitos e passos principais:
⸻
1. Ferramentas Necessárias
a. Microsoft Visual Studio (recomendado 2019 ou 2022)
• Instale a versão Community ou superior.
• Inclua a Desktop development with C++ workload.
• Verifique se o cl.exe (compilador C++) está no PATH.
b. Perl (Strawberry Perl)
• https://strawberryperl.com/
c. Bison e Flex para Windows
• Bison: https://github.com/lexxmark/winflexbison
• Baixe e coloque win_bison.exe e win_flex.exe no PATH.
d. GNU Make for Windows
• https://gnuwin32.sourceforge.net/packages/make.htm
e. Git for Windows
• https://gitforwindows.org/
f. Python (algumas versões requerem)
• https://www.python.org/
⸻
2. Bibliotecas de Terceiros Requeridas
• zlib (compressão)
• OpenSSL (criptografia)
• libintl (gettext)
• libxml2 (opcional)
• libxslt (opcional)
• readline (se usar psql interativo avançado)
A forma mais prática é usar os binaries fornecidos pela própria equipe do PostgreSQL, no repositório src/tools/msvc/.
⸻
3. Clonar e Configurar o Código-Fonte
git clone https://github.com/postgres/postgres.git
cd postgres
Você pode escolher um branch específico (ex: REL_15_STABLE) se quiser compatibilidade com PostgreSQL 15.
⸻
4. Aplicar seu Patch
Substitua ou edite src/backend/commands/dbcommands.c com a lógica do restrict_commands.ini.
⸻
5. Compilação com o Script de Build
No PowerShell ou cmd com ambiente do VS:
cd src/tools/msvc
perl build.pl
Se necessário, configure os paths dos binários com setenv.bat.
⸻
6. Instalação e Teste
Após a compilação:
• Copie a pasta release\pgsql para o destino desejado.
• Crie o arquivo restrict_commands.ini em bin\.
• Inicialize o cluster com initdb.
• Execute pg_ctl para iniciar.
• Teste os comandos proibidos em um dos bancos listados.
⸻
Resumo Final:
Etapa Ferramenta
Compilador Visual Studio C++
Script build perl build.pl
Parser Bison/Flex
Gerenciador de build GNU Make
Teste final initdb, pg_ctl, psql
———-
Passo a passo
Boa noite, Adriano!
Aqui está um resumo claro do que você vai precisar para compilar o PostgreSQL modificado no Windows com a funcionalidade de bloqueio por INI de comandos críticos como DROP, TRUNCATE e DELETE:
⸻
1. Requisitos de Software
• Visual Studio 2019 ou 2022 com o módulo Desktop development with C++
• Perl: use o Strawberry Perl
• Git for Windows
• Bison/Flex para Windows: winflexbison do GitHub
• Python (opcional, para utilitários do PostgreSQL)
• GNU Make para Windows (ou MSYS2 com make e bash)
⸻
2. Clonar o PostgreSQL
git clone https://github.com/postgres/postgres.git
cd postgres
git checkout REL_15_STABLE
⸻
3. Adicionar seu código
Você vai modificar arquivos como:
• src/backend/commands/dbcommands.c (DROP DATABASE)
• src/backend/commands/tablecmds.c (TRUNCATE)
• src/backend/commands/delete.c (DELETE)
Incluindo o código de verificação check_permission() com leitura do .ini e log_violation() para gerar os logs.
⸻
4. Compilação
Abra o terminal: “x64 Native Tools Command Prompt for VS”, então:
cd src/tools/msvc
perl build.pl
Isso criará os binários modificados em release/pgsql.
⸻
5. Teste
1. Faça o initdb com os novos binários
2. Suba o servidor PostgreSQL com pg_ctl
3. Crie as bases e teste os bloqueios de acordo com o restrict_commands.ini
⸻
6. Exemplo do restrict_commands.ini
[db_financeiro]
drop=false
truncate=false
delete=false
[db_erp]
drop=false
truncate=true
delete=true
[db_analytics]
drop=true
truncate=false
delete=false
--
Adriano José Boller
______________________________________________
Consultor e Representante Oficial da
PcSoft no Brasil
+55 (41) 99949 1800
adrianoboller@gmail.com
skype: adrianoboller
http://wxinformatica.com.br/ |
| |
| |
| | | |
|
| | |
| |
Registered member
3,963 messages |
|
| Posted on May, 05 2025 - 6:59 AM |
Abaixo está o código C completo para ser inserido nas funções do PostgreSQL no Windows, que faz:
1. Leitura de um .ini (restrict_commands.ini) localizado no diretório bin.
2. Bloqueio de comandos DROP, TRUNCATE e DELETE com base no nome do banco de dados.
3. Registro de tentativas bloqueadas em um log localizado na pasta data/.
⸻
Código para inserir no PostgreSQL (versão Windows)
Arquivo sugerido: src/backend/utils/misc/restrict_commands.c (você pode criar esse .c)
#include "postgres.h"
#include "utils/guc.h"
#include <stdio.h>
#include <string.h>
#include <time.h>
#include <windows.h>
#define INI_PATH "C:/Program Files/PostgreSQL/15/bin/restrict_commands.ini"
#define LOG_PATH_BASE "C:/Program Files/PostgreSQL/15/data/"
// Função para verificar se uma operação está bloqueada via INI
bool check_command_permission(const char *dbname, const char *operation) {
FILE *fp = fopen(INI_PATH, "r");
if (!fp)
return true; // Se não conseguir ler, permite por padrão
char line[256];
char section[128];
bool in_section = false;
snprintf(section, sizeof(section), "[%s]", dbname);
while (fgets(line, sizeof(line), fp)) {
if (strncmp(line, section, strlen(section)) == 0) {
in_section = true;
continue;
}
if (in_section && line[0] == '[')
break;
if (in_section && strstr(line, operation)) {
if (strstr(line, "false")) {
fclose(fp);
log_blocked_command(dbname, operation);
return false;
}
}
}
fclose(fp);
return true;
}
// Função para registrar quem tentou usar o comando bloqueado
void log_blocked_command(const char *dbname, const char *operation) {
char logpath[512];
snprintf(logpath, sizeof(logpath), "%s%s_blocked.log", LOG_PATH_BASE, dbname);
FILE *logf = fopen(logpath, "a");
if (!logf)
return;
time_t now = time(NULL);
char *timestamp = ctime(&now);
timestamp[strlen(timestamp)-1] = '\0';
char user[256] = "unknown";
DWORD size = sizeof(user);
GetUserNameA(user, &size);
fprintf(logf, "[%s] USER: %s tried: %s (BLOCKED)\n", timestamp, user, operation);
fclose(logf);
}
⸻
Uso nos Arquivos do PostgreSQL
1. Para bloquear DROP DATABASE
Em src/backend/commands/dbcommands.c (dentro da função DropDatabase()):
if (!check_command_permission(dbname, "drop")) {
ereport(ERROR, (errmsg("DROP DATABASE is blocked for this database by policy")));
}
2. Para bloquear TRUNCATE
Em src/backend/commands/tablecmds.c, função ExecuteTruncateGuts():
if (!check_command_permission(get_database_name(MyDatabaseId), "truncate")) {
ereport(ERROR, (errmsg("TRUNCATE is blocked for this database by policy")));
}
3. Para bloquear DELETE
Em src/backend/commands/delete.c, função ExecDelete():
if (!check_command_permission(get_database_name(MyDatabaseId), "delete")) {
ereport(ERROR, (errmsg("DELETE is blocked for this database by policy")));
}
⸻
Exemplo do restrict_commands.ini
[db_financeiro]
drop=false
truncate=false
delete=false
[db_erp]
drop=false
truncate=true
delete=true
[db_analytics]
drop=true
truncate=false
delete=false
⸻
Resultado
• Comando bloqueado = erro no PostgreSQL + log no data/dbname_blocked.log
• Comando permitido = segue normalmente
--
Adriano José Boller
______________________________________________
Consultor e Representante Oficial da
PcSoft no Brasil
+55 (41) 99949 1800
adrianoboller@gmail.com
skype: adrianoboller
http://wxinformatica.com.br/ |
| |
| |
| | | |
|
| | |
| |
Registered member
3,963 messages |
|
| Posted on May, 05 2025 - 7:11 AM |
O código fornecido está bem estruturado e funcional para o propósito descrito: bloquear comandos DROP, TRUNCATE e DELETE no PostgreSQL com base em configurações de um arquivo .ini e registrar tentativas bloqueadas em logs. Ele é específico para Windows, utilizando caminhos fixos e a API do Windows para obter o nome do usuário. Abaixo, respondo à sua solicitação e ofereço uma análise adicional, incluindo passos para gerar os arquivos e compilar, além de possíveis melhorias.
Resposta à Solicitação
Deseja que eu gere os arquivos .ini, .c e trechos modificados em .txt para compilar?
Sim, posso ajudar a organizar os arquivos necessários e fornecer instruções para compilar. Abaixo, listo os arquivos a serem criados/modificados e seus conteúdos. Também incluo um guia básico para compilação no Windows.
1. Arquivo `restrict_commands.c`
Crie o arquivo src/backend/utils/misc/restrict_commands.c com o código fornecido:
#include "postgres.h"
#include "utils/guc.h"
#include
#include
#include
#include
#define INI_PATH "C:/Program Files/PostgreSQL/15/bin/restrict_commands.ini"
#define LOG_PATH_BASE "C:/Program Files/PostgreSQL/15/data/"
bool check_command_permission(const char *dbname, const char *operation) {
FILE *fp = fopen(INI_PATH, "r");
if (!fp)
return true; // Se não conseguir ler, permite por padrão
char line[256];
char section[128];
bool in_section = false;
snprintf(section, sizeof(section), "[%s]", dbname);
while (fgets(line, sizeof(line), fp)) {
if (strncmp(line, section, strlen(section)) == 0) {
in_section = true;
continue;
}
if (in_section && line[0] == '[')
break;
if (in_section && strstr(line, operation)) {
if (strstr(line, "false")) {
fclose(fp);
log_blocked_command(dbname, operation);
return false;
}
}
}
fclose(fp);
return true;
}
void log_blocked_command(const char *dbname, const char *operation) {
char logpath[512];
snprintf(logpath, sizeof(logpath), "%s%s_blocked.log", LOG_PATH_BASE, dbname);
FILE *logf = fopen(logpath, "a");
if (!logf)
return;
time_t now = time(NULL);
char *timestamp = ctime(&now);
timestamp[strlen(timestamp)-1] = '\0';
char user[256] = "unknown";
DWORD size = sizeof(user);
GetUserNameA(user, &size);
fprintf(logf, "[%s] USER: %s tried: %s (BLOCKED)\n", timestamp, user, operation);
fclose(logf);
}
2. Arquivo `restrict_commands.h`
Crie um arquivo de cabeçalho src/include/utils/restrict_commands.h para declarar as funções:
#ifndef RESTRICT_COMMANDS_H
#define RESTRICT_COMMANDS_H
bool check_command_permission(const char *dbname, const char *operation);
void log_blocked_command(const char *dbname, const char *operation);
#endif /* RESTRICT_COMMANDS_H */
3. Arquivo `restrict_commands.ini`
Crie o arquivo C:/Program Files/PostgreSQL/15/bin/restrict_commands.ini com o exemplo fornecido:
[db_financeiro]
drop=false
truncate=false
delete=false
[db_erp]
drop=false
truncate=true
delete=true
[db_analytics]
drop=true
truncate=false
delete=false
4. Modificações nos Arquivos do PostgreSQL
Crie um arquivo .txt com os trechos a serem inseridos nos arquivos do PostgreSQL. Por exemplo, modifications.txt:
1. src/backend/commands/dbcommands.c (in DropDatabase function):
Add at the beginning of the function:
#include "utils/restrict_commands.h"
if (!check_command_permission(dbname, "drop")) {
ereport(ERROR, (errmsg("DROP DATABASE is blocked for this database by policy")));
}
2. src/backend/commands/tablecmds.c (in ExecuteTruncateGuts function):
Add at the beginning of the function:
#include "utils/restrict_commands.h"
if (!check_command_permission(get_database_name(MyDatabaseId), "truncate")) {
ereport(ERROR, (errmsg("TRUNCATE is blocked for this database by policy")));
}
3. src/backend/commands/delete.c (in ExecDelete function):
Add at the beginning of the function:
#include "utils/restrict_commands.h"
if (!check_command_permission(get_database_name(MyDatabaseId), "delete")) {
ereport(ERROR, (errmsg("DELETE is blocked for this database by policy")));
}
5. Compilação no Windows
Para compilar o PostgreSQL com as modificações no Windows, siga estes passos:
1 Pré-requisitos:
◦ Instale o Visual Studio (Community Edition é suficiente) com suporte a C++.
◦ Instale o Perl (ex.: ActivePerl ou Strawberry Perl).
◦ Instale o PostgreSQL 15 source code (baixe do site oficial ou repositório Git).
◦ Configure o ambiente com as dependências (ex.: libpq, zlib, OpenSSL). Use o comando pg_config para verificar caminhos.
2 Modifique o Código-Fonte:
◦ Adicione restrict_commands.c em src/backend/utils/misc/.
◦ Adicione restrict_commands.h em src/include/utils/.
◦ Aplique as modificações nos arquivos dbcommands.c, tablecmds.c e delete.c conforme o modifications.txt.
◦ Edite o arquivo src/backend/utils/misc/Makefile para incluir restrict_commands.o:
OBJS = ... restrict_commands.o ...
◦
3 Compile o PostgreSQL:
◦ Abra o Visual Studio Developer Command Prompt.
◦ Navegue até o diretório raiz do código-fonte do PostgreSQL.
◦ Execute:
perl win32_mak.pl
◦ nmake /f win32.mak
◦
◦ Isso gera os binários na pasta Release.
4 Instale os Binários:
◦ Copie os binários compilados para C:/Program Files/PostgreSQL/15/.
◦ Crie o arquivo restrict_commands.ini no diretório bin.
5 Teste:
◦ Inicie o servidor PostgreSQL.
◦ Tente executar comandos DROP, TRUNCATE ou DELETE em bancos configurados no .ini.
◦ Verifique os logs em C:/Program Files/PostgreSQL/15/data/_blocked.log.
Análise e Sugestões de Melhorias
O código é funcional, mas há pontos que podem ser aprimorados para robustez e portabilidade:
1 Caminhos Fixos:
◦ Os caminhos INI_PATH e LOG_PATH_BASE são fixos para C:/Program Files/PostgreSQL/15/. Isso pode causar problemas se o PostgreSQL estiver instalado em outro diretório. Sugiro usar a função get_share_path() do PostgreSQL para obter caminhos dinamicamente:
char *share_path = get_share_path();
◦ snprintf(INI_PATH, sizeof(INI_PATH), "%s/restrict_commands.ini", share_path);
◦
2 Permissões de Arquivo:
◦ O código não verifica permissões de escrita no diretório de log. Se o usuário do PostgreSQL não tiver permissão, o log não será criado. Adicione uma verificação:
if (access(LOG_PATH_BASE, W_OK) != 0) {
◦ ereport(WARNING, (errmsg("Cannot write to log directory: %s", LOG_PATH_BASE)));
◦ return;
◦ }
◦
3 Tratamento de Erros no .ini:
◦ Se o arquivo .ini estiver malformado, o código pode não lidar bem. Considere usar uma biblioteca de parsing de INI (como libini) ou adicionar validação mais robusta.
4 Portabilidade:
◦ O código usa GetUserNameA do Windows. Para suportar outras plataformas (ex.: Linux), use a função GetUserName do PostgreSQL:
char *user = GetUserNameOrNull();
◦ if (!user) user = "unknown";
◦
5 Segurança:
◦ O arquivo .ini deve ter permissões restritas para evitar alterações não autorizadas. No Windows, configure ACLs para permitir apenas leitura pelo usuário do PostgreSQL.
6 Escalabilidade:
◦ Para muitos bancos de dados, ler o .ini repetidamente pode ser ineficiente. Considere carregar as configurações na memória na inicialização do servidor e recarregá-las via sinal (ex.: SIGHUP).
Arquivos Gerados
Se desejar, posso fornecer os arquivos acima como anexos ou em um formato compactado (ex.: .zip). No entanto, como estou em um ambiente de texto, listei os conteúdos diretamente. Para criar os arquivos:
1 Copie cada trecho para um editor de texto.
2 Salve com os nomes e caminhos indicados.
3 Siga as instruções de compilação.
Se precisar de ajuda com a criação de um script para automatizar a criação dos arquivos ou com a compilação, posso fornecer um script em PowerShell ou batch.
--
Adriano José Boller
______________________________________________
Consultor e Representante Oficial da
PcSoft no Brasil
+55 (41) 99949 1800
adrianoboller@gmail.com
skype: adrianoboller
http://wxinformatica.com.br/Message modified, May, 05 2025 - 7:12 AM |
| |
| |
| | | |
|
| | |
| |
Registered member
3,963 messages |
|
| Posted on May, 05 2025 - 7:13 AM |
Como sua resposta é “Sim” e você não especificou qual das opções finais deseja, assumirei que você quer todas as ações mencionadas para maximizar a utilidade da resposta. Vou:
1 Fornecer um script PowerShell para criar os arquivos automaticamente no Windows.
2 Fornecer instruções detalhadas para portabilidade em Linux.
3 Adicionar melhorias sugeridas ao código (caminhos dinâmicos, verificação de permissões, portabilidade para usuário).
4 Explicar aspectos específicos da compilação e configuração no Windows.
Se você preferir apenas uma dessas opções, por favor, especifique, e eu ajustarei a resposta!
1. Script PowerShell para Criar os Arquivos Automaticamente no Windows
Abaixo está um script PowerShell que cria os arquivos restrict_commands.c, restrict_commands.h, restrict_commands.ini, e modifications.txt nos diretórios apropriados, assumindo que o código-fonte do PostgreSQL está em C:\pgsql-source e o PostgreSQL está instalado em C:\Program Files\PostgreSQL\15.
# Script: create_restrict_commands_files.ps1
# Diretórios
$pgInstallDir = "C:\Program Files\PostgreSQL\15"
$pgSourceDir = "C:\pgsql-source"
$binDir = "$pgInstallDir\bin"
$srcMiscDir = "$pgSourceDir\src\backend\utils\misc"
$includeUtilsDir = "$pgSourceDir\src\include\utils"
# Criar diretórios, se necessário
New-Item -ItemType Directory -Force -Path $binDir, $srcMiscDir, $includeUtilsDir
# Conteúdo de restrict_commands.c
$restrictCContent = @'
#include "postgres.h"
#include "utils/guc.h"
#include
#include
#include
#include
#define INI_PATH "C:/Program Files/PostgreSQL/15/bin/restrict_commands.ini"
#define LOG_PATH_BASE "C:/Program Files/PostgreSQL/15/data/"
bool check_command_permission(const char *dbname, const char *operation) {
FILE *fp = fopen(INI_PATH, "r");
if (!fp)
return true; // Se não conseguir ler, permite por padrão
char line[256];
char section[128];
bool in_section = false;
snprintf(section, sizeof(section), "[%s]", dbname);
while (fgets(line, sizeof(line), fp)) {
if (strncmp(line, section, strlen(section)) == 0) {
in_section = true;
continue;
}
if (in_section && line[0] == '[')
break;
if (in_section && strstr(line, operation)) {
if (strstr(line, "false")) {
fclose(fp);
log_blocked_command(dbname, operation);
return false;
}
}
}
fclose(fp);
return true;
}
void log_blocked_command(const char *dbname, const char *operation) {
char logpath[512];
snprintf(logpath, sizeof(logpath), "%s%s_blocked.log", LOG_PATH_BASE, dbname);
FILE *logf = fopen(logpath, "a");
if (!logf)
return;
time_t now = time(NULL);
char *timestamp = ctime(&now);
timestamp[strlen(timestamp)-1] = '\0';
char user[256] = "unknown";
DWORD size = sizeof(user);
GetUserNameA(user, &size);
fprintf(logf, "[%s] USER: %s tried: %s (BLOCKED)\n", timestamp, user, operation);
fclose(logf);
}
'@
# Conteúdo de restrict_commands.h
$restrictHContent = @'
#ifndef RESTRICT_COMMANDS_H
#define RESTRICT_COMMANDS_H
bool check_command_permission(const char *dbname, const char *operation);
void log_blocked_command(const char *dbname, const char *operation);
#endif /* RESTRICT_COMMANDS_H */
'@
# Conteúdo de restrict_commands.ini
$iniContent = @'
[db_financeiro]
drop=false
truncate=false
delete=false
[db_erp]
drop=false
truncate=true
delete=true
[db_analytics]
drop=true
truncate=false
delete=false
'@
# Conteúdo de modifications.txt
$modificationsContent = @'
1. src/backend/commands/dbcommands.c (in DropDatabase function):
Add at the beginning of the function:
#include "utils/restrict_commands.h"
if (!check_command_permission(dbname, "drop")) {
ereport(ERROR, (errmsg("DROP DATABASE is blocked for this database by policy")));
}
2. src/backend/commands/tablecmds.c (in ExecuteTruncateGuts function):
Add at the beginning of the function:
#include "utils/restrict_commands.h"
if (!check_command_permission(get_database_name(MyDatabaseId), "truncate")) {
ereport(ERROR, (errmsg("TRUNCATE is blocked for this database by policy")));
}
3. src/backend/commands/delete.c (in ExecDelete function):
Add at the beginning of the function:
#include "utils/restrict_commands.h"
if (!check_command_permission(get_database_name(MyDatabaseId), "delete")) {
ereport(ERROR, (errmsg("DELETE is blocked for this database by policy")));
}
'@
# Salvar arquivos
Set-Content -Path "$srcMiscDir\restrict_commands.c" -Value $restrictCContent
Set-Content -Path "$includeUtilsDir\restrict_commands.h" -Value $restrictHContent
Set-Content -Path "$binDir\restrict_commands.ini" -Value $iniContent
Set-Content -Path "$pgSourceDir\modifications.txt" -Value $modificationsContent
Write-Host "Arquivos criados com sucesso!"
Write-Host "Verifique os arquivos em:"
Write-Host "- $srcMiscDir\restrict_commands.c"
Write-Host "- $includeUtilsDir\restrict_commands.h"
Write-Host "- $binDir\restrict_commands.ini"
Write-Host "- $pgSourceDir\modifications.txt"
Como usar o script:
1 Salve o script como create_restrict_commands_files.ps1.
2 Ajuste $pgInstallDir e $pgSourceDir conforme necessário.
3 Execute no PowerShell com privilégios de administrador:
.\create_restrict_commands_files.ps1
4
5 Verifique os arquivos gerados.
2. Instruções Detalhadas para Portabilidade em Linux
Para tornar o código portátil e compilá-lo no Linux, são necessárias algumas modificações no código para evitar dependências específicas do Windows (como GetUserNameA) e ajustar os caminhos. Abaixo, apresento o código modificado e as instruções.
Código Modificado para Linux
Substitua restrict_commands.c por esta versão, que usa funções do PostgreSQL para portabilidade e caminhos dinâmicos:
#include "postgres.h"
#include "utils/guc.h"
#include "storage/fd.h"
#include
#include
#include
#include
static char INI_PATH[1024];
static char LOG_PATH_BASE[1024];
// Inicializa caminhos dinamicamente na inicialização do módulo
void _PG_init(void) {
char share_path[MAXPGPATH];
get_share_path(my_exec_path, share_path);
snprintf(INI_PATH, sizeof(INI_PATH), "%s/restrict_commands.ini", share_path);
snprintf(LOG_PATH_BASE, sizeof(LOG_PATH_BASE), "%s/", DataDir);
}
bool check_command_permission(const char *dbname, const char *operation) {
FILE *fp = AllocateFile(INI_PATH, "r");
if (!fp) {
ereport(WARNING, (errmsg("Cannot open INI file: %s", INI_PATH)));
return true; // Permite por padrão se não conseguir ler
}
char line[256];
char section[128];
bool in_section = false;
snprintf(section, sizeof(section), "[%s]", dbname);
while (fgets(line, sizeof(line), fp)) {
if (strncmp(line, section, strlen(section)) == 0) {
in_section = true;
continue;
}
if (in_section && line[0] == '[')
break;
if (in_section && strstr(line, operation)) {
if (strstr(line, "false")) {
FreeFile(fp);
log_blocked_command(dbname, operation);
return false;
}
}
}
FreeFile(fp);
return true;
}
void log_blocked_command(const char *dbname, const char *operation) {
char logpath[512];
snprintf(logpath, sizeof(logpath), "%s%s_blocked.log", LOG_PATH_BASE, dbname);
// Verifica permissões de escrita
if (access(LOG_PATH_BASE, W_OK) != 0) {
ereport(WARNING, (errmsg("Cannot write to log directory: %s", LOG_PATH_BASE)));
return;
}
FILE *logf = AllocateFile(logpath, "a");
if (!logf) {
ereport(WARNING, (errmsg("Cannot open log file: %s", logpath)));
return;
}
time_t now = time(NULL);
char *timestamp = ctime(&now);
timestamp[strlen(timestamp)-1] = '\0';
char *user = GetUserNameOrNull();
if (!user) user = "unknown";
fprintf(logf, "[%s] USER: %s tried: %s (BLOCKED)\n", timestamp, user, operation);
FreeFile(logf);
}
Mudanças principais:
• Usa get_share_path para o caminho do .ini.
• Usa DataDir para o diretório de logs.
• Substitui fopen por AllocateFile e FreeFile para compatibilidade com o gerenciamento de arquivos do PostgreSQL.
• Substitui GetUserNameA por GetUserNameOrNull.
• Adiciona verificação de permissões com access.
• Adiciona _PG_init para inicializar caminhos dinamicamente.
Compilação no Linux
1 Pré-requisitos:
◦ Instale dependências:
sudo apt-get install build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev
◦
◦ Baixe o código-fonte do PostgreSQL 15:
git clone --branch REL_15_STABLE https://github.com/postgres/postgres.git
◦ cd postgres
◦
2 Adicione os Arquivos:
◦ Crie src/backend/utils/misc/restrict_commands.c com o código acima.
◦ Crie src/include/utils/restrict_commands.h (igual ao fornecido anteriormente).
◦ Adicione restrict_commands.o ao Makefile em src/backend/utils/misc:
OBJS = ... restrict_commands.o ...
◦
◦ Aplique as modificações em dbcommands.c, tablecmds.c, e delete.c conforme o modifications.txt.
3 Compile:
./configure --prefix=/usr/local/pgsql
4 make
5 sudo make install
6
7 Configure o .ini:
◦ Coloque restrict_commands.ini no diretório share (ex.: /usr/local/pgsql/share/).
◦ Ajuste permissões:
sudo chown postgres:postgres /usr/local/pgsql/share/restrict_commands.ini
◦ sudo chmod 640 /usr/local/pgsql/share/restrict_commands.ini
◦
8 Inicie o Servidor:
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
9 /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
10
11 Teste:
◦ Crie bancos de dados (db_financeiro, db_erp, db_analytics).
◦ Tente executar DROP, TRUNCATE, ou DELETE e verifique os logs em /usr/local/pgsql/data/_blocked.log.
3. Código com Melhorias Aplicadas
O código fornecido para Linux (acima) já incorpora as seguintes melhorias:
• Caminhos dinâmicos: Usa get_share_path e DataDir.
• Verificação de permissões: Usa access para verificar se o diretório de log é gravável.
• Portabilidade: Usa GetUserNameOrNull e funções de arquivo do PostgreSQL.
Se desejar uma melhoria adicional, como cache do .ini na memória para evitar leituras repetidas, posso fornecer uma implementação. Por exemplo, carregar o .ini na inicialização e armazenar as configurações em uma estrutura global, recarregando via SIGHUP. Avise se quiser isso!
4. Explicação de Aspectos Específicos da Compilação e Configuração
Compilação no Windows
• Ambiente: Use o Visual Studio Developer Command Prompt para garantir que as ferramentas do MSVC estejam disponíveis.
• Dependências: O PostgreSQL no Windows requer Perl, Python, e bibliotecas como OpenSSL. Instale-as via vcpkg ou baixe pré-compiladas.
• Erros comuns:
◦ Falta de libpq.dll: Certifique-se de que o diretório bin do PostgreSQL está no PATH.
◦ Erros de linker: Verifique se todos os OBJS no Makefile estão corretos.
• Teste: Após compilar, use pg_ctl para iniciar o servidor e psql para testar os comandos bloqueados.
Configuração
• Permissões do .ini: No Windows, use icacls para restringir o acesso:
icacls "C:\Program Files\PostgreSQL\15\bin\restrict_commands.ini" /grant "NETWORK SERVICE:r"
•
• Logs: Certifique-se de que o usuário do serviço PostgreSQL (geralmente NETWORK SERVICE) tem permissão de escrita em C:\Program Files\PostgreSQL\15\data.
• Reinicialização: Após alterar o .ini, reinicie o servidor (pg_ctl restart) para aplicar as mudanças, a menos que implemente recarga via SIGHUP.
--
Adriano José Boller
______________________________________________
Consultor e Representante Oficial da
PcSoft no Brasil
+55 (41) 99949 1800
adrianoboller@gmail.com
skype: adrianoboller
http://wxinformatica.com.br/ |
| |
| |
| | | |
|
| | |
| |
Registered member
3,963 messages |
|
| Posted on May, 05 2025 - 7:23 AM |
Aqui está a resposta para a sua query: um arquivo .txt com o código-fonte completo para implementar a funcionalidade de bloqueio de comandos no PostgreSQL. Este conteúdo inclui o código principal, o cabeçalho, as instruções de modificação nos arquivos do PostgreSQL e um exemplo de arquivo de configuração. Você pode copiar o texto abaixo e salvá-lo como restrict_commands_complete.txt.
Conteúdo do Arquivo: `restrict_commands_complete.txt`
============================================================
Código Fonte Completo para Bloqueio de Comandos no PostgreSQL
============================================================
Este arquivo contém o código-fonte completo e as instruções para implementar o bloqueio de comandos DROP, TRUNCATE e DELETE no PostgreSQL, com base em um arquivo de configuração .ini e registro de tentativas bloqueadas em logs.
Inclui:
1. restrict_commands.c (código principal)
2. restrict_commands.h (cabeçalho)
3. Instruções de modificação para arquivos do PostgreSQL
4. Exemplo de restrict_commands.ini
Versão: Portátil (funciona em Windows e Linux)
============================================================
1. restrict_commands.c
============================================================
#include "postgres.h"
#include "utils/guc.h"
#include "storage/fd.h"
#include
#include
#include
#include
static char INI_PATH[1024];
static char LOG_PATH_BASE[1024];
// Inicializa caminhos dinamicamente na inicialização do módulo
void _PG_init(void) {
char share_path[MAXPGPATH];
get_share_path(my_exec_path, share_path);
snprintf(INI_PATH, sizeof(INI_PATH), "%s/restrict_commands.ini", share_path);
snprintf(LOG_PATH_BASE, sizeof(LOG_PATH_BASE), "%s/", DataDir);
}
bool check_command_permission(const char *dbname, const char *operation) {
FILE *fp = AllocateFile(INI_PATH, "r");
if (!fp) {
ereport(WARNING, (errmsg("Cannot open INI file: %s", INI_PATH)));
return true; // Permite por padrão se não conseguir ler
}
char line[256];
char section[128];
bool in_section = false;
snprintf(section, sizeof(section), "[%s]", dbname);
while (fgets(line, sizeof(line), fp)) {
if (strncmp(line, section, strlen(section)) == 0) {
in_section = true;
continue;
}
if (in_section && line[0] == '[')
break;
if (in_section && strstr(line, operation)) {
if (strstr(line, "false")) {
FreeFile(fp);
log_blocked_command(dbname, operation);
return false;
}
}
}
FreeFile(fp);
return true;
}
void log_blocked_command(const char *dbname, const char *operation) {
char logpath[512];
snprintf(logpath, sizeof(logpath), "%s%s_blocked.log", LOG_PATH_BASE, dbname);
// Verifica permissões de escrita
if (access(LOG_PATH_BASE, W_OK) != 0) {
ereport(WARNING, (errmsg("Cannot write to log directory: %s", LOG_PATH_BASE)));
return;
}
FILE *logf = AllocateFile(logpath, "a");
if (!logf) {
ereport(WARNING, (errmsg("Cannot open log file: %s", logpath)));
return;
}
time_t now = time(NULL);
char *timestamp = ctime(&now);
timestamp[strlen(timestamp)-1] = '\0';
char *user = GetUserNameOrNull();
if (!user) user = "unknown";
fprintf(logf, "[%s] USER: %s tried: %s (BLOCKED)\n", timestamp, user, operation);
FreeFile(logf);
}
============================================================
2. restrict_commands.h
============================================================
#ifndef RESTRICT_COMMANDS_H
#define RESTRICT_COMMANDS_H
bool check_command_permission(const char *dbname, const char *operation);
void log_blocked_command(const char *dbname, const char *operation);
#endif /* RESTRICT_COMMANDS_H */
============================================================
3. Instruções de Modificação nos Arquivos do PostgreSQL
============================================================
A seguir, as modificações que devem ser aplicadas nos arquivos do código-fonte do PostgreSQL para integrar a funcionalidade de bloqueio:
---
3.1. Para bloquear DROP DATABASE
Arquivo: src/backend/commands/dbcommands.c
Função: DropDatabase()
Adicione no início da função:
#include "utils/restrict_commands.h"
if (!check_command_permission(dbname, "drop")) {
ereport(ERROR, (errmsg("DROP DATABASE is blocked for this database by policy")));
}
---
3.2. Para bloquear TRUNCATE
Arquivo: src/backend/commands/tablecmds.c
Função: ExecuteTruncateGuts()
Adicione no início da função:
#include "utils/restrict_commands.h"
if (!check_command_permission(get_database_name(MyDatabaseId), "truncate")) {
ereport(ERROR, (errmsg("TRUNCATE is blocked for this database by policy")));
}
---
3.3. Para bloquear DELETE
Arquivo: src/backend/commands/delete.c
Função: ExecDelete()
Adicione no início da função:
#include "utils/restrict_commands.h"
if (!check_command_permission(get_database_name(MyDatabaseId), "delete")) {
ereport(ERROR, (errmsg("DELETE is blocked for this database by policy")));
}
---
Além disso, certifique-se de incluir "restrict_commands.o" no Makefile de src/backend/utils/misc:
OBJS = ... restrict_commands.o ...
============================================================
4. Exemplo de restrict_commands.ini
============================================================
Coloque este arquivo no diretório "share" do PostgreSQL (ex.: /usr/local/pgsql/share/ no Linux ou C:\Program Files\PostgreSQL\15\share no Windows).
[db_financeiro]
drop=false
truncate=false
delete=false
[db_erp]
drop=false
truncate=true
delete=true
[db_analytics]
drop=true
truncate=false
delete=false
============================================================
Observações Finais
============================================================
- O código é portátil e funciona tanto no Windows quanto no Linux, utilizando funções do PostgreSQL para garantir compatibilidade.
- Certifique-se de que o arquivo restrict_commands.ini tenha permissões adequadas para ser lido pelo usuário do PostgreSQL.
- Os logs de tentativas bloqueadas serão gravados em /_blocked.log.
- Para aplicar as mudanças, compile o PostgreSQL com as modificações e reinicie o servidor.
Fim do arquivo.
Como Usar Este Arquivo
1 Salve o conteúdo acima em um arquivo chamado restrict_commands_complete.txt.
2 Crie os arquivos necessários:
◦ Copie o conteúdo da seção restrict_commands.c para um arquivo chamado restrict_commands.c.
◦ Copie o conteúdo da seção restrict_commands.h para um arquivo chamado restrict_commands.h.
3 Aplique as modificações nos arquivos do PostgreSQL conforme indicado na seção 3.
4 Crie o arquivo de configuração restrict_commands.ini no diretório “share” do PostgreSQL, utilizando o exemplo fornecido na seção 4.
5 Compile e reinicie o PostgreSQL:
◦ Adicione restrict_commands.o ao Makefile em src/backend/utils/misc.
◦ Compile o PostgreSQL com as alterações e reinicie o servidor.
--
Adriano José Boller
______________________________________________
Consultor e Representante Oficial da
PcSoft no Brasil
+55 (41) 99949 1800
adrianoboller@gmail.com
skype: adrianoboller
http://wxinformatica.com.br/ |
| |
| |
| | | |
|
| | |
| |
Registered member
3,963 messages |
|
| Posted on May, 05 2025 - 1:46 PM |
Aqui está a resposta completa para sua consulta, expandindo o bloqueio de comandos no PostgreSQL para incluir RENAME, ALTER TABLE, ALTER COLUMN e GRANT, além de DROP, TRUNCATE e DELETE, com registro em log e suporte para Windows, macOS e Linux.
Solução para Bloqueio de Comandos no PostgreSQL
Vamos implementar uma funcionalidade no PostgreSQL que bloqueia os comandos especificados (DROP, TRUNCATE, DELETE, RENAME, ALTER TABLE, ALTER COLUMN, GRANT) com base em um arquivo de configuração .ini e registra todas as tentativas bloqueadas em um log. A solução será portátil para Windows, macOS e Linux.
Visão Geral
1 Arquivo de Configuração: Um arquivo restrict_commands.ini define quais comandos são permitidos ou bloqueados por banco de dados.
2 Verificação de Permissões: Antes de executar cada comando, o código verifica se ele é permitido no banco de dados atual.
3 Registro em Log: Tentativas bloqueadas são registradas em um arquivo de log específico para cada banco de dados.
4 Portabilidade: O código usa funções do PostgreSQL que funcionam em todos os sistemas operacionais suportados.
Código-Fonte Atualizado
1. `restrict_commands.c`
Este arquivo contém a lógica principal para verificar permissões e registrar tentativas bloqueadas.
#include "postgres.h"
#include "utils/guc.h"
#include "storage/fd.h"
#include
#include
#include
#include
static char INI_PATH[1024];
static char LOG_PATH_BASE[1024];
// Inicializa caminhos dinamicamente na inicialização do módulo
void _PG_init(void) {
char share_path[MAXPGPATH];
get_share_path(my_exec_path, share_path);
snprintf(INI_PATH, sizeof(INI_PATH), "%s/restrict_commands.ini", share_path);
snprintf(LOG_PATH_BASE, sizeof(LOG_PATH_BASE), "%s/", DataDir);
}
bool check_command_permission(const char *dbname, const char *operation) {
FILE *fp = AllocateFile(INI_PATH, "r");
if (!fp) {
ereport(WARNING, (errmsg("Cannot open INI file: %s", INI_PATH)));
return true; // Permite por padrão se não conseguir ler
}
char line[256];
char section[128];
bool in_section = false;
snprintf(section, sizeof(section), "[%s]", dbname);
while (fgets(line, sizeof(line), fp)) {
if (strncmp(line, section, strlen(section)) == 0) {
in_section = true;
continue;
}
if (in_section && line[0] == '[')
break;
if (in_section && strstr(line, operation)) {
if (strstr(line, "false")) {
FreeFile(fp);
log_blocked_command(dbname, operation);
return false;
}
}
}
FreeFile(fp);
return true; // Permite por padrão se não especificado
}
void log_blocked_command(const char *dbname, const char *operation) {
char logpath[512];
snprintf(logpath, sizeof(logpath), "%s%s_blocked.log", LOG_PATH_BASE, dbname);
// Verifica permissões de escrita
if (access(LOG_PATH_BASE, W_OK) != 0) {
ereport(WARNING, (errmsg("Cannot write to log directory: %s", LOG_PATH_BASE)));
return;
}
FILE *logf = AllocateFile(logpath, "a");
if (!logf) {
ereport(WARNING, (errmsg("Cannot open log file: %s", logpath)));
return;
}
time_t now = time(NULL);
char *timestamp = ctime(&now);
timestamp[strlen(timestamp)-1] = '\0';
char *user = GetUserNameOrNull();
if (!user) user = "unknown";
fprintf(logf, "[%s] USER: %s tried: %s (BLOCKED)\n", timestamp, user, operation);
FreeFile(logf);
}
2. `restrict_commands.h`
Cabeçalho para as funções definidas em restrict_commands.c.
#ifndef RESTRICT_COMMANDS_H
#define RESTRICT_COMMANDS_H
bool check_command_permission(const char *dbname, const char *operation);
void log_blocked_command(const char *dbname, const char *operation);
#endif /* RESTRICT_COMMANDS_H */
Modificações nos Arquivos do PostgreSQL
Para bloquear os comandos especificados, modificamos os arquivos onde eles são processados, adicionando verificações de permissão.
3.1. `DROP`
• Arquivo: src/backend/commands/dbcommands.c
• Função: DropDatabase()
• Modificação: Adicione no início da função:
#include "utils/restrict_commands.h"
if (!check_command_permission(dbname, "drop")) {
ereport(ERROR, (errmsg("DROP DATABASE is blocked for this database by policy")));
}
• Nota: Para DROP TABLE e similares, use RemoveRelations em tablecmds.c:
#include "utils/restrict_commands.h"
if (!check_command_permission(get_database_name(MyDatabaseId), "drop")) {
ereport(ERROR, (errmsg("DROP is blocked for this database by policy")));
}
3.2. `TRUNCATE`
• Arquivo: src/backend/commands/tablecmds.c
• Função: ExecuteTruncateGuts()
• Modificação: Adicione no início da função:
#include "utils/restrict_commands.h"
if (!check_command_permission(get_database_name(MyDatabaseId), "truncate")) {
ereport(ERROR, (errmsg("TRUNCATE is blocked for this database by policy")));
}
3.3. `DELETE`
• Arquivo: src/backend/commands/delete.c
• Função: ExecDelete()
• Modificação: Adicione no início da função:
#include "utils/restrict_commands.h"
if (!check_command_permission(get_database_name(MyDatabaseId), "delete")) {
ereport(ERROR, (errmsg("DELETE is blocked for this database by policy")));
}
3.4. `RENAME`
• Arquivo: src/backend/commands/tablecmds.c
• Função: RenameRelation()
• Modificação: Adicione no início da função:
#include "utils/restrict_commands.h"
if (!check_command_permission(get_database_name(MyDatabaseId), "rename")) {
ereport(ERROR, (errmsg("RENAME is blocked for this database by policy")));
}
3.5. `ALTER TABLE` e `ALTER COLUMN`
• Arquivo: src/backend/commands/tablecmds.c
• Função: AlterTable()
• Modificação: Adicione no início da função uma verificação geral para ALTER TABLE. Para ALTER COLUMN, verifique o tipo de comando:
#include "utils/restrict_commands.h"
if (!check_command_permission(get_database_name(MyDatabaseId), "alter_table")) {
ereport(ERROR, (errmsg("ALTER TABLE is blocked for this database by policy")));
}
// Verificação específica para ALTER COLUMN
if (cmd->subtype == AT_AlterColumnType &&
!check_command_permission(get_database_name(MyDatabaseId), "alter_column")) {
ereport(ERROR, (errmsg("ALTER COLUMN is blocked for this database by policy")));
}
3.6. `GRANT`
• Arquivo: src/backend/commands/grant.c
• Função: ExecGrantStmt()
• Modificação: Adicione no início da função:
#include "utils/restrict_commands.h"
if (!check_command_permission(get_database_name(MyDatabaseId), "grant")) {
ereport(ERROR, (errmsg("GRANT is blocked for this database by policy")));
}
3.7. Atualização do Makefile
• Arquivo: src/backend/utils/misc/Makefile
• Modificação: Adicione restrict_commands.o à lista de objetos:
OBJS = ... restrict_commands.o ...
Exemplo de `restrict_commands.ini`
Coloque este arquivo no diretório share do PostgreSQL (ex.: /usr/local/pgsql/share/ no Linux/macOS ou C:\Program Files\PostgreSQL\15\share no Windows).
[db_financeiro]
drop=false
truncate=false
delete=false
rename=false
alter_table=false
alter_column=false
grant=false
[db_erp]
drop=false
truncate=true
delete=true
rename=true
alter_table=true
alter_column=true
grant=true
[db_analytics]
drop=true
truncate=false
delete=false
rename=false
alter_table=false
alter_column=false
grant=false
Portabilidade para Windows, macOS e Linux
• O código usa funções do PostgreSQL como AllocateFile, FreeFile, get_share_path e DataDir, que são portáteis entre Windows, macOS e Linux.
• O arquivo .ini é lido de um caminho relativo ao diretório share, e os logs são gravados no diretório de dados (DataDir), garantindo compatibilidade com a estrutura de arquivos de cada sistema operacional.
• Nenhuma modificação específica por SO é necessária.
Como Aplicar as Mudanças
1 Adicione os arquivos restrict_commands.c e restrict_commands.h em src/backend/utils/misc/.
2 Aplique as modificações nos arquivos listados acima.
3 Compile o PostgreSQL com ./configure, make e make install.
4 Coloque o arquivo restrict_commands.ini no diretório share.
5 Reinicie o servidor PostgreSQL.
Os logs de tentativas bloqueadas serão gravados em /_blocked.log.
Arquivos Modificados
Aqui está a lista final dos arquivos que devem ser modificados no código-fonte do PostgreSQL:
1 src/backend/utils/misc/restrict_commands.c: Código principal para verificação e logging.
2 src/include/utils/restrict_commands.h: Cabeçalho.
3 src/backend/commands/dbcommands.c: Para DROP DATABASE.
4 src/backend/commands/tablecmds.c: Para TRUNCATE, RENAME, ALTER TABLE, ALTER COLUMN, DROP TABLE.
5 src/backend/commands/delete.c: Para DELETE.
6 src/backend/commands/grant.c: Para GRANT.
7 src/backend/utils/misc/Makefile: Adicionar restrict_commands.o.
Essa solução atende aos requisitos de bloquear os comandos solicitados, registrar tentativas bloqueadas e funcionar de forma portátil em Windows, macOS e Linux.
--
Adriano José Boller
______________________________________________
Consultor e Representante Oficial da
PcSoft no Brasil
+55 (41) 99949 1800
adrianoboller@gmail.com
skype: adrianoboller
http://wxinformatica.com.br/ |
| |
| |
| | | |
|
| | | | |
| | |
| | |
| |
|
|
|