Instalando e configurando o PostgreSQL no Debian Squeeze

  • Ip do servidor: 10.0.0.22
  • Ip do cliente: 10.0.0.20

Prepare o seu sistema com o seguinte script http://wiki.douglasqsantos.com.br/doku.php/confinicialsqueeze_en para que não falte nenhum pacote ou configuração.

Agora vamos instalar o postgreSQL

aptitude install postgresql postgresql-client postgresql-common postgresql-contrib postgresql-doc \
postgresql-server-dev-all -y

Vamos agora à algumas configurações básicas, vamos começar liberando acesso ao postgreSQL para as máquinas.

Aqui vamos configurar em qual endereço ip o servidor vai escutar vamos colocar * para ele escutar em todas as interfaces disponíveis.

vim /etc/postgresql/8.4/main/postgresql.conf
[...]
listen_address = '*'
[...]

Agora precisamos liberar o acesso no postgresql para que os clientes consigam se conectar nele, então vamos liberar a maquina do programador por exemplo que tem o ip 10.0.0.20

vim /etc/postgresql/8.4/main/pg_hba.conf
[...]
#no final do arquivo adicione a seguinte linha
host    all         all         10.0.0.20/32             md5

Aqui no exemplo acima estamos liberando acesso para a máquina 10.0.0.5 porém podemos liberar acesso para uma rede inteira utilizando CIDR ex: 10.0.0.0/24

Agora vamos reiniciar o nosso postgreSQL

/etc/init.d/postgresql restart

Agora vamos definir uma senha para o usuário postgres

su - postgres
psql
postgres=# ALTER USER postgres WITH PASSWORD 'senha';
ALTER ROLE
postgres=#

Agora vamos instalar o cliente em uma maquina Ubuntu para efetuar um teste de conexão

aptitude install postgresql-client -y

Agora vamos testar a conexão

psql -h 10.0.0.22 -U postgres -W
Password for user postgres: 
psql (9.1.2, server 8.4.10)
WARNING: psql version 9.1, server version 8.4.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
 
postgres=# \q

Como pode ser notado conseguimos conectar no servidor porém recebemos um aviso sobre as versões da aplicação, eu estou utilizando um cliente 9.1 e o servidor é 8.4 neste caso o cliente tem compatibilidade com o servidor caso fosse o contrário o cliente não se conectaria no servidor por incompatibilidade de versão.

Agora vamos cadastrar mais um usuário para teste, teste usuário tem que existir no postgresql

su - postgres
psql
CREATE USER usuario WITH PASSWORD 'senha' SUPERUSER;
CREATE ROLE
postgres=# \q

Agora vamos conectar pelo cliente novamente.

psql -h 10.0.0.22 -U usuario -W
Password for user usuario: 
psql: FATAL:  banco de dados "usuario" não existe

Recebemos um erro pois o padrão é que o usuário tenha uma base de dados com o seu nome vamos fazer um teste para validar isso

su - postgres
psql
psql (8.4.10)
Digite "help" para ajuda.
 
postgres=# CREATE DATABASE usuario;

Pronto criamos a nossa base de dados com o nome do usuário agora vamos conectar no banco novamente.

psql -h 10.0.0.22 -U usuario -W
Password for user usuario: 
psql (9.1.2, server 8.4.10)
WARNING: psql version 9.1, server version 8.4.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
 
usuario=# \q

Como pode ser notado ocorreu tudo certo, agora vamos ver como acessamos outro banco de dados pelo cliente.

psql -h 10.0.0.22 -U usuario -W -d postgres
Password for user usuario: 
psql (9.1.2, server 8.4.10)
WARNING: psql version 9.1, server version 8.4.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
 
postgres=# \q

Como pode ser notado conectamos pelo cliente no servidor porém já selecionado o banco postgres.

Agora para remover um usuário podemos fazer da seguinte forma

postgres@debian22:~$ psql
psql (8.4.10)
Digite "help" para ajuda.
 
postgres=# drop user usuario;
ERRO:  role "usuario" não pode ser removida porque alguns objetos dependem dela
DETALHE:  dono de banco de dados usuario
postgres=# \q

Como pode ser notado recebemos um aviso que o usuário não pode ser removido porque ele é o dono do banco de dados usuário então vamos ter que remover primeiro o banco de dados e depois o usuário.

su - postgres
postgres@debian22:~$ psql
psql (8.4.10)
Digite "help" para ajuda.
 
postgres=# drop database usuario;
DROP DATABASE
postgres=# drop  user usuario;
DROP ROLE
postgres=# \q

Liberando direitos de acesso (comando GRANT)

Por meio do comando GRANT é possível atribuir direitos de acessos a contas de usuários e/ou regras do sistema de forma eficiente. Como o comando é responsável por gerenciar acessos a diferentes tipos de objetos do servidor PostgreSQL, o mesmo possui algumas variações, tendo opções voltadas somente para tabelas, outras para funções etc.

Existem dois princípios básicos de utilização do comando GRANT. O primeiro é na atribuição de regras em usuário e/ou grupos, e a segunda é a atribuição de direitos de acesso em objetos do servidor. Ambas as funcionalidades serão abordadas a seguir.

OBS: Nunca forneça a um usuário mais acessos do que ele precisa. Esta recomendação faz parte dos requisitos de segurança de seu servidor.

Atribuir regras à usuários e grupos

Ao criar um usuário e/ou uma regra no PostgreSQL, o comando CREATE USER permite informar a qual grupo a conta em questão pertence, bem como informar quais outras contas fazem parte da regra que está sendo criada, se for necessário. Porém, muitos destes atributos variam com o tempo, sem alternar os nomes das contas. Por este motivo é recomendado gerenciar as atribuições de grupos e regras por meio do comando GRANT, e somente criar as contas com o comando CREATE USER.

A sintaxe do comando GRANT, visando atribuir regras para contas de usuários, outras regras ou grupos é a seguinte:

GRANT role[,role2, ...] TO usuário [,usuário2, ...] [WITH ADMIN OPTION];

A única opção extra possível de utilizar neste comando é a WITH ADMIN OPTION, a qual permite que os usuários que estão recebendo as atribuições possam repassá-las para outras contas, caso o desejarem.

Atribuindo acesso em objetos

Como existem vários tipos de objetos no PostgreSQL, existe uma sintaxe específica para atribuir acessos a cada um deles. Na verdade, o comando é o mesmo, com pequenas variações, conforme será visto a seguir.

Tabelas

GRANT opções_tabela
ON TABLE tabela [, tabela2, ...]
TO beneficiários;

opções_tabela: As opções de utilização de tabelas são as seguintes:

OpçãoDescrição
SELECTPermite selecionar registros da tabela em questão
INSERTPermite inserir registros na tabela em questão
UPDATEPermite atualizar registros na tabela em questão
DELETEPermite excluir registros na tabela em questão
REFERENCESPermite utilizar registros desta tabela como chave estrangeira de outras tabelas
TRIGGERPermite criar triggers na tabela em questão.
ALLHabilita todos os direitos de acessos previamente abordados.

Sequências

GRANT opções_sequência
ON SEQUENCE sequência[,sequência2,...]
TO beneficiários;

opções_sequência: As opções de utilização de sequências são as seguintes:

OpçãoDescrição
USAGEPermite utilizar a numeração da sequência.
SELECTPermite capturar o valor numérico da sequência
UPDATEPermite atualizar o valor numérico da sequência
ALLHabilita todos os direitos de acessos previamente abordados.

Banco de dados

GRANT opções_banco
ON DATABASE banco[,banco2,...]
TO beneficiários;

opções_banco: AS opções de utilização de bancos de dados são as seguintes:

OpçãoDescrição
CREATEPermite criar objetos no banco de dados.
CONNECT Permite conectar ao banco de dados
TEMPORARYPermite criar tabelas temporárias no banco de dados. Estas tabelas são removidas do sistema assim que a conexão com o usuário é encerrada.
TEMPO mesmo que a opção TEMPORARY
ALLHabilita todos os direitos de acessos previamente abordados.

Funções

GRANT opções_funções
ON FUNCTION função (argumentos) [,função2(argumentos),...]
TO beneficiários;

opções_funcões: AS opções de utilização de funções são as seguintes:

OpçãoDescrição
EXECUTEPermite invocar a executar a função
ALLHabilita todos os direitos de acessos previamente abordados. No caso, tem o mesmo efeito de utilizar somente a opção anterior, pois somente esta opção está disponível para utilização.

Linguagens

GRANT opções_linguagem
ON LANGUAGE linguagem [, linguagem2, ...]
TO benificiários;

opções_linguagem: As opções de utilização de linguagens são as seguintes:

OpçãoDescrição
USAGEPermite utilizar a linguagem em questão.
ALLHabilita todos os direitos de acessos previamente abordados. No caso, tem o mesmo efeito de utilizar somente a opção anterior, pois somente esta opção está disponível para utilização.

Esquemas

GRANT opções_schema
ON SCHEMA schema [,schema2,...]
TO benificiários;

opções_schema: As opções de utilização de esquemas são as seguintes:

OpçãoDescrição
CREATEPermite criar objetos no esquema em questão
USAGEPermite utilizar objetos criados dentro do esquema em questão.
ALLHabilita todos os direitos de acessos previamente abordados.

Tablespaces

GRANT opções_tablespaces
ON TABLESPACE tablespace [, tablespaces2, ...]
TO beneficiários;

opções_tablespaces: AS opções de utilização de tablespaces são as seguintes:

OpçãoDescrição
CREATEPermite criar tablespaces no banco de dados
ALLHabilita todos os direitos de acessos previamente abordados. No caso, tem o mesmo efeito de utilizar somente a opção anterior, pois somente esta opção está disponível para utilização

Como é possível notar, para cada utilização do comando GRANT existe um conjunto distinto de opções (para tabelas, bancos etc.). As opções de cada tipo de utilização estão listadas a seguir:

beneficiários: As opções de utilização de beneficiários são as seguintes:

OpçãoDescrição
nome_usuarioÉ possível informar diretamente o nomd da conta de usuário e/ou regra que será o beneficiário do comando GRANT
GROUP groupnameCaso seja um grupo o beneficiário do comando, é possível informar o nome utilizando a opção GROUP
PUBLICSe o acesso estiver sendo liberado sem restrições, utilize o termo PUBLIC.

WITH GRANT OPTION: No final da sintaxe é possível adicionar a opção WITH GRANT OPTION, a qual permite com que os beneficiários do comando possam repassar os direitos de acesso em questão para outras contas e/ou regras do sistema.

Revogando direitos de acesso (comando REVOKE)

O comando REVOKE é utilizado para remover direitos de acesso de usuários, grupos e/ou regras do sistema. Sua utilização é muito semelhante à do comando GRANT, apenas alterando o nome do comando para REVOKE e alterando seu parâmetro TO por FROM.

Criando um banco e dados

Agora vamos criar o usuário no postgresql

postgres=# CREATE user usuario WITH password 'senha' superuser;
CREATE ROLE

Agora vamos criar um banco de dados com o usuário postgres

postgres=# CREATE DATABASE teste WITH OWNER usuario;

Permissões para um usuário em um determinado banco de dados

Aqui abaixo vamos dar todos os direitos no banco de dados teste para o usuário: usuário.

su - postgres
psql
postgres=# GRANT ALL PRIVILEGES ON DATABASE teste TO usuario;
GRANT

Para fazermos um backup de uma determinada base de dados podemos fazer da seguinte forma

Vamos exportar a senha do postgresql para ele não ficar pedindo.

export PGPASSWORD="senha"
pg_dumpall -i -h localhost -U postgres -c -o -f arquivo.backup

Aqui estou fazendo backup da base de dados teste e mandando ela para o arquivo teste.dump

Vamos matar ela do postgres para efetuarmos um teste de restore

psql -h localhost -U postgres -W
Password for user postgres: 
psql (9.1.2)
Type "help" for help.

postgres=# DROP DATABASE teste;
DROP DATABASE
postgres=# \q

Agora vamos tentar logar no banco de dados teste que excluímos

psql -h localhost -U postgres -W -d teste
Password for user postgres: 
psql: FATAL:  database "teste" does not exist

Como pode ser notado a exclusão aconteceu com sucesso agora vamos mandar restaurar o nosso banco teste

Vamos exportar a senha do postgresql para ele não ficar pedindo.

export PGPASSWORD="senha"

Agora vamos importar o dump

psql -h localhost -U postgres -f arquivo.backup
You are now connected to database "postgres" as user "postgres".
SET
SET
ERROR:  role "postgres" already exists
ALTER ROLE
ERROR:  role "usuario" already exists
ALTER ROLE
REVOKE
REVOKE
GRANT
GRANT
CREATE DATABASE
Password for user postgres: 
You are now connected to database "postgres" as user "postgres".
SET
SET
SET
SET
SET
COMMENT
CREATE EXTENSION
COMMENT
REVOKE
REVOKE
GRANT
GRANT
Password for user postgres: 
You are now connected to database "template1" as user "postgres".
SET
SET
SET
SET
SET
COMMENT
CREATE EXTENSION
COMMENT
REVOKE
REVOKE
GRANT
GRANT
Password for user postgres: 
You are now connected to database "teste" as user "postgres".
SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
REVOKE
REVOKE
GRANT
GRANT

Agora vamos mandar logar no banco teste novamente

psql -h localhost -U postgres -W -d teste
Password for user postgres: 
psql (9.1.2)
Type "help" for help.

teste=# \q

Está restaurado ;)

Referências