APGDIFF: Ferramenta Mediana que pode ser Útil!

Basta um momento de descuido para termos várias versões de um mesmo banco de dados em funcionamento. Identificar os pontos de alteração em esquemas de bancos de dados manualmente é mais do que cansativo: é arriscado.

Para solucionar estes problemas, existem várias ferramentas para comparação entre esquemas de banco de dados, dentre elas a "Another PostgreSQL Diff Tool", também chamada (apgdiff). É uma ferramenta livre que apresenta versão gratuita na web e atualmente está em sua versão 2.4. 

Neste post é mostrado o funcionamento básico da versão web da ferramenta, que é bem simples, e são colocadas as primeiras impressões na sua utilização.

* Funcionamento

A operação da ferramenta é bem simples:
- Inicialmente, acesse o sítio da ferramenta;
- Faça o backup dos bancos de dados que se deseja comparar. Com o utilitário pg_dump a sintaxe poderia ser: pg_dump -U postgres -v -f teste_atual.txt postgres (extraindo o banco de dados postgres para o arquivo txt teste_atual.txt)
- Uma vez que tenha extraído o backup dos dois bancos de dados a comparar, acione a opção "Create Diff online" e faça o upload dos arquivos de backup obtidos
- Acione a opção de comparação de esquemas. Abaixo, colocamos um exemplo passo a passo.

Tela inicial

Inclusão de arquivos para comparação

Diferença entre os esquemas

* Primeiras impressões

O site foi bastante rápido em suas análises, mas não detectou todas as poucas alterações realizadas.

No teste foi acrescentado um novo usuário, o que não foi detectado pela ferramenta. A chave primária da tabela incluída também não foi encontrada pela apgdiff. A ferramenta também apresentou como diferente uma chave primária que na verdade estava igual em ambos os esquemas.

A primeira impressão é que a apgdiff é interessante, mas está longe de ser perfeita. A análise dos backups mostrou que podem ser deixados de lado detalhes importantes, o que não tira sua importância como potencial ferramenta auxiliar.

O trabalho do DBA, ajudado por scripts próprios e outras ferramentas, seguramente pode se beneficiar dos recursos da apgdiff. Mas como qualquer ferramenta, esta apresenta limitações, algumas das quais identificadas neste post.

Range Types: Novo recurso do Postgresql 9.2!

Início e fim, começo e encerramento. Armazenar intervalos de valores é uma tarefa importante que já estava disponível no Postgresql, porém de modo mais dispendioso em termos de programação. Era possível por exemplo criar dois campos indicando os extremos de um intervalo sem problemas, implementar uma função ou ainda criar um tipo intervalar com o comando CREATE TYPE.

Representação Matemática de Intersecção de Intervalos de Valores

A versão 9.2 apresenta o conceito de "Range Types", que engloba um tipo de dados específico para intervalos, além de recursos para cálculos e manipulações relacionadas a estes tipos peculiares de dados. Considero este um grande avanço, que pode reduzir o esforço de implementação e aumentar o desempenho em várias situações. Pode-se por exemplo, indexar um campo intervalar.

Neste post, embora não se busque esgotar o tema, vamos ilustrar as principais possibilidades desta nova feature com exemplos.

* Tipos de Intervalos

O postgresql apresenta seis tipos de intervalos padrão, sendo alguns discretos e outros contínuos, mas você pode criar outros utilizando CREATE TYPE:

- int4range — Intervalo de inteiros de 4 bytes.
- int8range — Intervalo de "bigint" (inteiros de 8 bytes)
- numrange — Intervalo de números reais
- tsrange — Intervalo de timestamps sem time zone
- tstzrange — Intervalo de timestamps com time zone
- daterange — Intervalo de datas

* Operações Básicas para Definir Dados Intervalares

Um intervalo consiste em um conjunto de valores delimitados por um valor inicial e um final. O postgres oferece opção de se trabalhar com intervalos total e parcialmente limitados e não limitados. Os delimitadores compreendem colchetes "[]" para os intervalos fechados e parênteses "()" para os intervalos abertos.

 Intervalos abertos e fechados

Abaixo segue uma sequência de exemplos ilustrando as principais operações feitas com intervalos e os resultados obtidos.

Exemplo 1: Intervalo fechado contendo os números 1 a 5.
postgres=# SELECT '[1,5]'::numrange;
 numrange
----------
 [1,5]
(1 registro)

Exemplo 2: Intervalo fechado em campo inteiro, contendo os números de 1 a 5.
postgres=# SELECT '[1,5]'::int4range;
 int4range
-----------
 [1,6)
(1 registro)

Exemplo 3: Intervalo de 1 a 5, aberto no 5, isto é, não contendo o valor 5.
postgres=# SELECT '[1,5)'::numrange;
 numrange
----------
 [1,5)
(1 registro)

Exemplo 4: Intervalo sem limite máximo.
postgres=# SELECT '[1,]'::numrange;
 numrange
----------
 [1,)
(1 registro)

Exemplo 5: Intervalo sem limite mínimo.
postgres=# SELECT '(,1]'::numrange;
 numrange
----------
 (,1]
(1 registro)

Exemplo 6: Duas formas de expressar um intervalo sem quaisquer limites.
postgres=# SELECT '(,)'::numrange, numrange(null, null);
 numrange | numrange
----------+----------
 (,)      | (,)
(1 registro)

Exemplo 7: Intervalos de 1 a 5, abertos e fechados, criados a partir do construtor do tipo numrange.postgres=# SELECT numrange(1,5,'[)'), numrange(1,5,'(]'), numrange(1,5,'[]'), numrange(1,5,'()');
 numrange | numrange | numrange | numrange
----------+----------+----------+----------
 [1,5)    | (1,5]    | [1,5]    | (1,5)
(1 registro)

Exemplo 8: Duas formas de expressar um intervalo sem elementos.postgres=# SELECT numrange(1,1,'[)'), '[1,1)'::numrange;
 numrange | numrange
----------+----------
 empty    | empty
(1 registro)

Exemplo 9: Intervalo em campo data.
postgres=# SELECT daterange('12/01/2012',current_date,'[)');
        daterange       
-------------------------
 [2012-01-12,2012-10-17)
(1 registro)

Exemplo 10: Intervalo em campo data, exemplo 2.
postgres=# SELECT daterange(current_date -10,current_date,'[)');
        daterange       
-------------------------
 [2012-10-07,2012-10-17)
(1 registro)

Exemplo 11: Teste de pertencimento de elemento a um intervalo.
postgres=# SELECT int4range(10, 20, '[]') @> 9, int4range(10, 20,'[]') @> 15, int4range(10, 20,'[]') @> 21;
 ?column? | ?column? | ?column?
----------+----------+----------
 f        | t        | f

Exemplo 12: Recuperando Limites Superior e Inferior de um Intervalo
postgres=# SELECT lower(numrange(10, 100, '[]')), upper(numrange(10, 100, '[]'));
 lower | upper
-------+-------
    10 |   100
(1 registro)

Exemplo 13: Verificação de sobreposição de intervalos
É feita com o operador &&.

postgres=# SELECT numrange(10, 20) && numrange(25, 30), daterange('01/01/2010', '31/12/2011') && daterange('01/01/2011', '31/12/2012');
 ?column? | ?column?
----------+----------
 f        | t
(1 registro)

Exemplo 14: Intersecção de Intervalos
O teste é feito com o operador *, retornando 'empty' caso a intersecção não apresente elementos.

postgres=# SELECT int4range(10, 20,'[]') * int4range(15, 25,'[]'),daterange('2010-01-01','2012-06-30','[]') * daterange('2011-01-01','2012-12-30','[]');
 ?column? |        ?column?        
----------+-------------------------
 [15,21)  | [2011-01-01,2012-07-01)
(1 registro)

postgres=# SELECT int4range(10, 20) * int4range(25, 35);
 ?column?
----------
 empty
(1 registro)

Exemplo 15: Teste se intervalo é vazio (empty)

postgres=# SELECT isempty(numrange(10, 20)), isempty(numrange(10, 10));
 isempty | isempty
---------+---------
 f       | t
(1 registro)

Exemplo 16: Criação de Tabelas e Visões e Índices com Intervalos

postgres=# CREATE TABLE teste_range (rang_data daterange, rang_int4 int4range, rang_int8 int8range, rang_num numrange, rang_timestamp tsrange);
CREATE TABLE
postgres=# CREATE TABLE teste_range_2 (rang_data daterange PRIMARY KEY, rang_int4 int4range UNIQUE, rang_int8 int8range, rang_num numrange, rang_timestamp tsrange);
NOTA:  CREATE TABLE / PRIMARY KEY criará índice implícito "teste_range_2_pkey" na tabela "teste_range_2"
NOTA:  CREATE TABLE / UNIQUE criará índice implícito "teste_range_2_rang_int4_key" na tabela "teste_range_2"
CREATE TABLE
postgres=# CREATE OR REPLACE VIEW view_teste_range AS SELECT * FROM teste_range ORDER BY rang_data;
CREATE VIEW
postgres=# CREATE INDEX ind_teste_range ON teste_range (rang_data, rang_int4);
CREATE INDEX
postgres=# INSERT INTO teste_range (rang_data, rang_int4) VALUES (daterange('2010-01-01','2012-06-30','[]'), int4range(10, 20,'[]'));
INSERT 0 1
postgres=# select * from TESTE_RANGE;
        rang_data        | rang_int4 | rang_int8 | rang_num | rang_timestamp
-------------------------+-----------+-----------+----------+----------------
 [2010-01-01,2012-07-01) | [10,21)   |           |          |
(1 registro)

* Pontos Fortes

- Programas, funções e consultas que utilizem intervalos ficam menores.
- Opções para manipulação de intervalos são confiáveis e não demandam pluigins ou instalação de novos componentes.

* Pontos Fracos

- Perda de compatibilidade com outros SGBDs em todas as funcionalidades que utilizem intervalos.

Blog Indiano de PostgreSQL

Achei bastante interessante este blog. É de um indiano chamado Ragavedra e está em inglês.

Os pontos fortes são:
- Informações sobre a arquitetura de aplicações PostgreSQL
- As imagens de alta qualidade que ele utiliza, que acredito serem de "produção própria". São explicativas e relativamente detalhadas.

Acesse o site aqui.

Categorias de Volatilidade de Funções no Postgres

O processamento de funções em bancos de dados é uma opção bastante utilizado em certos ambientes. A quantidade e complexidade das lógicas de negócio que são alocadas nas funções podem ser expressivas. O lado negativo de se empregar funções no Postgres ou em qualquer SGBD é a necessidade de um tempo significativo de processamento.

Caso se utilize muitas funções o sistema, ao mesmo tempo em que gerencia os dados, passa a ser também um processador de regras da camada de negócios do sistema. Um recurso válido para facilitar o processamento de funções, reduzindo o esforço computacional, é empregar as categorias de volatilidade de funções no Postgres ao escrever os seus códigos. Esta feature promete maior desempenho no processamento das funções sem a necessidade de alterações no corpo das funções implementadas.

As categorias de funções são:

- VOLATILE - Uma função volatile pode alterar os dados de um banco. Também pode retornar valores diferentes em chamadas sucessivas com os mesmos parâmetros. Uma função com essas características terá seu plano de execução recalculado a cada chamada da função, portanto não é passível de otimização;

- STABLE - Quando uma função é STABLE, assume-se que a mesma não modifica a base de dados e retornará os mesmos valores caso sejam fornecidos os mesmos parâmetros na mesma chamada. Desta forma, o processador de consultas do postgres pode otimizar múltiplas chamadas da função para uma única chamada;

- IMMUTABLE - Se a sua função não altera jamais a base de dados e sempre retorna os mesmos valores para os mesmos parâmetros, não importando o contexto, deve ser classificada como IMMUTABLE, facilitando a otimização das consultas. Caso uma função seja assinalada como IMMUTABLE, mas não seguir este comportamento, apresentando respostas distintas para um mesmo conjunto de parâmetros, por exemplo, este será um erro difícil de rastrear.

* Como definir a classificação das funções?

Se a função apresentar comandos INSERT, DELETE ou UPDATE, pode portanto alterar o banco de dados, e deve ser assinalada como VOLATILE. Caso utilize ou produza valores aleatórios (random()), ou empregue funções que variam seu resultado como currval(), timeofday(), também se classifica como VOLATILE.

Caso ao sua função não possua comandos de alteração de dados, mas apresente o comando SELECT internamente, o resultado da consulta pode variar a cada chamada da função, pois o banco de dados está sujeito a atualizações entre as execuções da função, então a mesma possivelmente pode ser classificada como STABLE. Utilizei o termo "possivelmente" porque a consulta pode ser feita, por exemplo, em tabelas que não sofrem atualização, e neste caso, a função até poderia ser classificada como IMMUTABLE(!). SE sua função emprega funções da família de current_timestamp, possivelmente se alinhará com a classificação STABLE, pois o valor destas funções não muda no decorrer da transação.

Funções que realizem cálculos matemáticos sem utilização de valores aleatórios e funções com resultado variável são candidatas a IMMUTABLE, assim como funções que realizem validações simples sobre os parâmetros fornecidos, sem uso do comando SELECT.

O valor padrão é VOLATILE, assumido pelo postgres quando não fornecido pelo programador.

Uma função VOLATILE enxerga alterações no banco de dados ocorridas durante sua execução, enquanto que funções STABLE e IMMUTABLE não apresentam esta visibilidade.

* IMMUTABLE

O exemplo 1 gera erro em tempo de execução por alterar o banco de dados com o comando INSERT em uma função IMMUTABLE.

Exemplo 1:

CREATE TABLE teste (codigo integer, descricao varchar(20));

CREATE OR REPLACE FUNCTION teste_ins() RETURNS varchar(20) AS $$
BEGIN
    INSERT INTO teste VALUES (1,'Teste Class.');
    RETURN 'OK';
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;

postgres=# SELECT teste_ins();
ERRO:  INSERT não é permitido em uma função não-volátil
CONTEXTO:  comando SQL "INSERT INTO teste VALUES (1,'Teste Clas.')"
PL/pgSQL function "teste_ins" line 2 at comando SQL
O exemplo 2 executa normalmente. ele utiliza o SELECT mas não consulta tabelas ou visões, então pode ser IMMUTABLE.

Exemplo 2:

CREATE OR REPLACE FUNCTION teste_soma_p1_p2(p1 integer, p2 integer) RETURNS integer AS $$
BEGIN
    RETURN (select $1 + $2);
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;

postgres=# SELECT teste_soma_p1_p2 (1,1); 
teste_soma_p1_p2
------------------
                2
(1 registro)

postgres=# SELECT teste_soma_p1_p2 (5,4);
 teste_soma_p1_p2
------------------
                9
(1 registro)


* STABLE

O exemplo abaixo funciona como função STABLE, apresentando o comando SELECT.

Exemplo 3:

CREATE OR REPLACE FUNCTION teste_select() RETURNS varchar(10) AS $$
BEGIN
    RETURN (SELECT CAST(count(*) AS VARCHAR) FROM teste);
END;
$$ LANGUAGE PLPGSQL STABLE;

postgres=# SELECT teste_select();
 teste_select
--------------
 1
(1 registro)

O exemplo 4 apresenta função STABLE om utilização da função current_timestamp().

Exemplo 4:

CREATE OR REPLACE FUNCTION teste_timest() RETURNS varchar(30) AS $$
BEGIN
    RETURN (SELECT CAST(current_timestamp AS VARCHAR) );
END;
$$ LANGUAGE PLPGSQL STABLE;

postgres=# SELECT teste_timest();
         teste_timest         
-------------------------------
 2012-08-24 10:57:59.429209-03
(1 registro)

* VOLATILE

O exemplo abaixo só funciona se a função for VOLATILE.

Exemplo 5:

postgres=# CREATE OR REPLACE FUNCTION teste_ins() RETURNS varchar(20) AS $$
BEGIN
INSERT INTO teste VALUES (1,'Teste Class.');
RETURN 'OK';
END;
$$ LANGUAGE PLPGSQL VOLATILE;

postgres=# SELECT teste_ins();
 teste_ins
-----------
 OK
(1 registro)

Os exemplos apresentados são relativamente simples, mas quanto mais complexa a função, maior o ganho de utilização da classificação de funções. Empregue no seu dia a dia este recurso do postgres, mas não se esqueça de fazer testes criteriosos para cada função, pois os erros de execução causados pela classificação incorreta de uma função podem de difícil detecção.

Tratamento de Parâmetros de Funções com Pl/PgSQL

Existem várias formas de se processar erros em parâmetros fornecidos a funções. Existem casos em que valores diferentes do esperado e nulos são fornecidos, o que faz com que as entradas de parâmetros devam receber um tratamento meticuloso.

Neste post vamos apresentar alguns recursos simples que podem ser utilizados para tratar parâmetros em funções no Postgresql.

* Raise Notice

Utilize Raise Notice para disparar avisos ao usuário da função. Estes avisos podem funcionar como advertências, apresentar informações relevantes sobre os parâmetros fornecidos e sobre a execução da função em si.

Estes avisos não interrompem a execução da função nem são considerados erros pelos aplicativos.

Exemplo 1:

CREATE OR REPLACE FUNCTION teste_par_1(par_1 varchar(10)) RETURNS varchar(10) AS
$$
BEGIN
IF char_length(par_1) < 2 THEN
    RAISE NOTICE 'Valor não fornecido ou muito pequeno: %',$1;
    RETURN 'AVISO';
END IF;
RETURN 'OK';
END;
$$ LANGUAGE PLPGSQL;


banco=# Select teste_par_1 ('T');
NOTA:  Valor não fornecido ou muito pequeno: T
 teste_par_1
-------------
 AVISO
(1 registro)


* Raise Exception

Utilize Raise Exception para disparar um erro ao usuário da função acompanhado de uma mensagem explicativa. A emissão de erro interrompe a execução da função.

Exemplo 2:

CREATE OR REPLACE FUNCTION teste_par_2(par_2 varchar(10)) RETURNS varchar(10) AS
$$
BEGIN
IF char_length(par_2) < 2 THEN
    RAISE EXCEPTION 'Formato inválido: %',$1;
    RETURN 'ERRO';
END IF;
RETURN 'OK';
END;
$$ LANGUAGE PLPGSQL;

banco=# Select teste_par_2 ('T');

ERRO:  Formato inválido: T

* RETURNS NULL ON NULL INPUT ou STRICT

O uso da cláusula STRICT ou "RETURNS NULL ON NULL INPUT" faz com que seja retornado valor nulo caso um dos parâmetros fornecidos seja nulo. É um recurso interessante e que pode poupar tempo de processamento em funções mais elaboradas. Para que a função aceite valores nulos, existe a cláusula "CALLED ON NULL INPUT", mas a mesma é pouco utilizada por ser o comportamento default para as funções no Postgresql.


Observe no exemplo abaixo que o valor nulo (null) é diferente da string sem elementos.

Exemplo 3:

CREATE OR REPLACE FUNCTION teste_par_3(par_3 varchar(10)) RETURNS varchar(10) AS
$$
BEGIN
RETURN 'OK';
END;
$$ LANGUAGE PLPGSQL RETURNS NULL ON NULL INPUT;

 
banco=# Select teste_par_3 (null);
 teste_par_3
-------------
 
(1 registro)

banco=# Select teste_par_3 ('');
 teste_par_3
-------------
 OK
(1 registro)
banco=# Select teste_par_3 ('T');
 teste_par_3
-------------
 OK
(1 registro)
A utilização de várias validações conjuntamente é a melhor forma de assegurar que a função receba valores processáveis. O exemplo abaixo é uma ilustração desta necessidade.

Exemplo 4:

CREATE OR REPLACE FUNCTION teste_par (par_todos varchar(10)) RETURNS varchar(10) AS
$$
BEGIN
IF char_length(par_todos) <=3  THEN
    RAISE EXCEPTION 'Valor muito pequeno não nulo: %',$1;
    RETURN 'ERRO';
ELSE
    IF char_length(par_todos) <=5  THEN
        RAISE NOTICE 'Valor muito pequeno: %',$1;
        RETURN 'AVISO';
    END IF;
END IF;
RETURN 'OK';
END;
$$ LANGUAGE PLPGSQL RETURNS NULL ON NULL INPUT;

pf=# Select teste_par (null);
 teste_par
-----------

(1 registro)

pf=# Select teste_par ('T');
ERRO:  Valor muito pequeno não nulo: T
 
pf=# Select teste_par ('Test');
NOTA:  Valor muito pequeno: Test
 teste_par
-----------
 AVISO
(1 registro)

Atualmente existem além de NOTICE e EXCEPTION vários outros qualificadores das mensagens: DEBUG, LOG, INFO, NOTICE, WARNING, e EXCEPTION, sendo que este último é o valor padrão. 

Utilize-os nas suas validações, tentando sempre manter o código o mais simples possível!

Expresso Livre: Mais de 500000 contas de correio eletrônico. Todas PostgreSQL!

Email, Agenda, Catálogo de Endereços, Workflow e Mensagens Instantâneas em um único ambiente. Essa é a promessa do Expresso Livre, software livre mantido por um consórcio de entidades que engloba:

• CAIXA ECONÔMICA FEDERAL
• CELEPAR - Empresa de TI do Governo do Paraná
• PROCERGS - Empresa de TI do Governo do Rio Grande do Sul
• PROGNUS - Empresa de Consultoria
• SERPRO - Empresa de TI do Governo Federal

A ferramenta está em desenvolvimento desde 2007 e é utilizada atualmente por mais de 500.000 usuários em 167 empresas ou instituições, e utiliza como banco de dados o PostgreSQL. Vale a pena conhecer!

Gere Automaticamente seus Comandos GRANT e REVOKE!

Os comandos GRANT e REVOKE concedem e retiram permissões de acesso dos usuários aos objetos do banco de dados relativas à inserção, exclusão e alteração de dados, entre outras possibilidades. Neste post, vamos gerar automaticamente comandos GRANT e REVOKE utilizando SQL. Este tipo de procedimento não é muito comum porque ambos os comandos apresentam sintaxes simples que permitem a concessão de acessos sem a necessidade de automação.

Para construir scripts para automatizar a concessão e revogação destes acessos, o primeiro passo é saber quais são os usuários cadastrados no servidor.

1. Quais são os usuários cadastrados?

Para conceder ou revogar privilégios aos usuários, é interessante saber quantos e quais usuários estão cadastrados no seu SGBD, e uma consulta a PG_USER .

select * from pg_user;

usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig

----------+----------+-------------+----------+-----------+----------+----------+-----------

postgres |       10 | t           | t        | t         | ******** |          |

gisuser  |    17141 | f           | f        | f         | ******** |          |

A próxima etapa é identificar as tabelas para as quais será concedido acesso.


2. Quais são as tabelas criadas no banco?



Uma consulta aos metadados de PG_TABLES retorna o nome das tabelas utilizadas. Observe que na consulta, selecionamos apenas as  tabelas do schema public, ignorando as tabelas de sistema.

pf=# select tablename from pg_tables where schemaname = 'public';
 tablename
-----------
 pfdet2011
 pf2011
 ns2011
 nsdet2011
 cliente
(5 registros)
3. Concedendo Acessos em Massa

Com o comando GRANT, posso conceder permissões de inclusão, alteração e exclusão nas tabelas do banco para um determinado usuário. Basta executar este select e utilizar o resultado da consulta como entrada para o postgresql:

pf=# select 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || tablename || ' TO 

postgres'  from pg_tables where schemaname = 'public';
                           ?column?                           
---------------------------------------------------------------
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO postgres
(5 registros)

Uma pequena alteração no script faz o produto cartesiano entre tabelas e usuários, gerando todas as combinações:

pf=# select 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || tab.tablename || ' TO ' || usu.usename || ' ; ' as COMANDO  from pg_tables tab, pg_user usu where tab.schemaname = 'public' ;
                             comando                             
------------------------------------------------------------------
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO hacker ;
(20 registros)
4. Revogando permissões de acesso


Com o comando REVOKE, as permissões  para todos os usuários podem ser revogadas instantaneamente:



pf=# select 'REVOKE SELECT, INSERT, UPDATE, DELETE ON ' || tab.tablename || ' FROM ' || usu.usename || ' ; ' as COMANDO  from pg_tables tab, pg_user usu where tab.schemaname = 'public' ;
                               comando                              
---------------------------------------------------------------------
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM hacker ;
(20 registros)

5. Considerações Práticas


Como já foi mencionado neste post, a concessão de acesssos com GRANT e REVOKE raramente demanda alguma automação. Sintaxes poderosas e simples resolvem o problema sem maiores problemas, geralmente sendo executadas diretamente pelo DBA:

GRANT ALL ON DATABASE postgres TO hacker;

REVOKE ALL ON DATABASE postgres FROM hacker;


Este post é mais um exercício do que um exemplo prático, mas pode ser útil em situações em que se deseje maior controle.

Consulte as especificações dos comandos GRANT e REVOKE para ver a grande diversidade de opções disponíveis!

Bom Material sobre Otimização de Desempenho de Bancos de Dados PostgreSQL

Ajustes de performance são uma parte importante do trabalho dos DBAs. Este trabalho de conclusão de curso de Qiang Wang mostra diversas opções que podem ser empregadas para melhorar o desempenho do Postgresql.

O texto está em um inglês de fácil compreensão e as soluções sugeridas são bastante simples, o que torna o material bastante prático.

Pesquisas sobre PostgreSQL: Ambientes Escaláveis para SGBD em Software Livre

O Serpro está investindo em convênios para pesquisas sobre ambientes escaláveis implementados com o Postgresql.

O artigo de Flávio Gomes Lisboa, publicado na edição de maio de 2012 da revista Tema (p. 12 e 13), é uma boa referência de como o Governo e Universidades podem estabelecer parcerias para pesquisas avançadas envolvendo teoria, prática e  tecnologias livres.

Desenvolva suas Aplicações de Bancos Postgres com Wavemaker

 

Tela 1: Servidor do Wavemaker Online

 

 

Cansado de ter de programar as interfaces em Java e PHP? Ferramentas de desenvolvimento são importantes para adquirir maior produtividade e para se explorar os vários recursos dos bancos de dados. O Wavemaker é uma ferramenta de desenvolvimento que oferece bons recursos para criar e gerir aplicações web, minimizando o esforço de programação, e que apresenta plena compatibilidade com bancos de dados PostgreSQL!

É uma ferramenta livre com código aberto através de licença Apache. Neste post a preocupação não é mostrar em profundidade os recursos da ferramenta, nem criar um tutorial, mas sim apresentar as funcionalidades básicas.

Tela 2: Interface do Wavemaker

A instalação é relativamente simples, e o programa pode ser baixado em http://www.wavemaker.com. O wavemaker é compatível com windows, linux e macintosh.

Tela 3: Criação de Projeto no Wavemaker

O Wavemaker apresenta uma interface bastante simplificada e ao mesmo tempo prática, e as operações são todas feitas dentro do navegador web. Basta se selecionar um objeto para suas propriedades estarem disponibilizadas para edição à direita da tela. A interface de programação é WYSIWYG. É uma ferramenta cliente-servidor, o que exige os devidos cuidados com a segurança em rede.

Abaixo, alguns recursos associados ao PostgreSQL:

* Importar Database
Por meio do menu "Services/ Import database" é possível recuperar todas as informações em um banco já existente. A interface é intuitiva para quem tem alguma experiência de desenvolvimento.

Tela 4: Importar Database

Entre com os dados do banco de dados, teste a conexão utilizando a opção "Test connection" e acione a importação do banco de dados com o botão "Import".

As tabelas importadas aparecem à esquerda da tela, na pasta "Database Widgets". É possível utilizar estas tabelas para criar formulários CRUD, consultas e relatórios, entre outras possibilidades.

* Projetar Database
Acione a opção "Services/ Design Database" para criar suas bases de dados, tabelas e para estabelecer os relacionamentos entre as mesmas.

Tela 5: Projetar Database

Ao disparar esta opção, você define o nome do banco a ser criado e confirma. O banco aparecerá no menu à esquerda da tela.

Selecione o banco e na parte central da tela aparecerão as opções de criação das tabelas do seu banco. A interface realmente é bem agradável. Clique no ícone do disquete para salvar as tabelas que for desenvolvendo.

Tela 6: Criação de Tabela

* Consultar
O menu "Services/ Query" permite que se realize e salve consultas às tabelas.

Tela 7: Construção de Consultas

A ferramenta apresenta ainda grids, treeviews, charts para apreentação dos dados, entre outras funcionalidades. É possível definir o dataset de uma grid e indicar as colunas a serem mostradas, o que facilita muito o desenvolvimento.

Tela 8: Dados de Uma Tabela

* Pontos fortes:
- Boa interface
- Visual WYSIWYG
- Facilidade de instalação (segui o tutorial e não houve qualquer incidente)
- Código aberto com licença Apache
- Tutoriais no sítio da ferramenta
- A desenvolvedora foi adquirida recentemente pela VMWare, o que pode garantir mais recursos para a evolução desta ferramenta

* Pontos fracos
- Compatibilidade boa com Postgresql, mas não excepcional. Recursos específicos como herança de tabelas e indexação avançada não são abordados na ferramenta e tem de ser codificados manualmente no banco.
- A desenvolvedora foi adquirida recentemente pela VMWare, e o impacto desta mudança no desenvolvimento da ferramenta não pode ser previsto de antemão

* Avaliação Pessoal
A primeira impressão que me causou foi bastante positiva, mas não recomendo a utilização em ambientes de produção sem vários testes com prototipação e simulações de carga.

Questões de concurso sobre o PostgreSQL!

Este link mostra um site com questões de vários concursos no Brasil sobre o postgres coletadas desde 2007.

É interessante ver que esta tecnologia já está sendo utilizada em diversos órgãos órgãos como MPU, MEC, TJ, INFRAERO, EMBASA, entre outros, a ponto de fazer parte dos processos seletivos.

Acesse: Revista Internacional de PostgreSQL

A revista PostgreSQL Magazine tem sua primeira edição lançada. Minha primeira impressão foi bastante positiva!

Abaixo, uma visão geral dos conteúdos:

  - PostgreSQL 9.1 : 10 incríveis novos recursos
  - NoSQL : Implementação com Postgres
  - Entrevista : Stefan Kaltenbrunner
  - Opinião: Financiamento de Funcionalidades do PostgreSQL
  - A espera pela versão 9.2 : Cascading Streaming Replication
  - Dicas e Truques: PostgreSQL no Mac OS X Lion

O acesso está disponível em três formas:
  * Leitura online gratuita: http://pgmag.org/01/read
  * Pela aquisição da edição impressa: http://pgmag.org/01/buy
  * Versão em PDF: http://pgmag.org/01/download



A revista apresenta abertura para escritores, tradutores e outros interessados.

Espero que haja fôlego para a produção de mais revistas com este nível. Confira!