1. Ip do servidor: 10.101.0.100
2. Ip do cliente: 10.101.0.1

Vamos setar o endereço do nosso repositório

export PKG_PATH=ftp://openbsd.locaweb.com.br/pub/OpenBSD/5.1/packages/`machine -a`/

Agora vamos instalar o postgreSQL

pkg_add -v postgresql-server postgresql-client postgresql-docs

Agora vamos mandar setar uma senha para o postgres

passwd _postgresql
Changing local password for _postgresql.
New password: #S3Nh4
Retype new password: #S3Nh4

Agora vamos logar com o Postgres

su - _postgresql

Agora vamos criar o diretório que vai armazenar os dados do Postgres

mkdir /var/postgresql/data

Agora vamos inicializar o banco e setar uma senha para o usuário postgres interno do Banco

initdb -D /var/postgresql/data -U postgres -A md5 -W
The files belonging to this database system will be owned by user "_postgresql".
This user must also own the server process.

The database cluster will be initialized with locale C.
The default database encoding has accordingly been set to SQL_ASCII.
The default text search configuration will be set to "english".

fixing permissions on existing directory /var/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 40
selecting default shared_buffers ... 24MB
creating configuration files ... ok
creating template1 database in /var/postgresql/data/base/1 ... ok
initializing pg_authid ... ok
Enter new superuser password: #S3Nh4
Enter it again: #S3Nh4
setting password ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... not supported on this platform
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

Success. You can now start the database server using:

    postgres -D /var/postgresql/data
or
    pg_ctl -D /var/postgresql/data -l logfile start

Agora vamos sair do usuário postgres

exit

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 /var/postgresql/data/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 /var/postgresql/data/pg_hba.conf
[...]
#no final do arquivo adicione a seguinte linha
host    all         all         10.101.0.0/24             md5

Aqui no exemplo acima estamos liberando acesso para as máquinas da rede 10.101.0.0/24

Agora vamos reiniciar o nosso postgreSQL

/etc/rc.d/postgresql restart

Agora vamos verificar se ele esta escutando

netstat -na | egrep 5432
tcp          0      0  *.5432                 *.*                    LISTEN
tcp6         0      0  *.5432                 *.*                    LISTEN
0xfffffe801de05778 stream      0      0 0xfffffe801d70fbc0                0x0                0x0                0x0 /tmp/.s.PGSQL.5432

Agora vamos acessar o nosso postgres para ver se está tudo ok

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

postgres=# \q

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

pacman -Suy psqlodbc

Agora vamos testar a conexão

psql -h 10.101.0.100 -U postgres -W
Senha para usuário postgres: 
psql (9.2.1, servidor 9.1.2)
AVISO: psql versão 9.2, servidor versão 9.1.
         Algumas funcionalidades do psql podem não funcionar.
Digite "help" para ajuda.

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.2 e o servidor é 9.1 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 no postgresql

Agora vamos criar este usuário no postgresql com direitos administrativos

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

postgres=# CREATE USER douglas WITH PASSWORD 'S3Nh4' SUPERUSER;
CREATE ROLE
postgres=# \q

Agora vamos conectar pelo cliente novamente.

psql -h 10.101.0.100 -U douglas -W
Senha para usuário douglas: 
psql: FATAL:  database "douglas" does not exist

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

Agora vamos criar o banco de dados douglas

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

postgres=# CREATE DATABASE douglas WITH OWNER douglas;

Agora vamos logar no banco que criamos

psql douglas
Password: #S3Nh4
psql (9.1.2)
Type "help" for help.

douglas=# \q

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

psql -h 10.101.0.100 -U douglas -W
Senha para usuário douglas: 
psql (9.2.1, servidor 9.1.2)
AVISO: psql versão 9.2, servidor versão 9.1.
         Algumas funcionalidades do psql podem não funcionar.
Digite "help" para ajuda.

douglas=# \q

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

psql -h 10.101.0.100 -U douglas -W -d postgres
Senha para usuário douglas: 
psql (9.2.1, servidor 9.1.2)
AVISO: psql versão 9.2, servidor versão 9.1.
         Algumas funcionalidades do psql podem não funcionar.
Digite "help" para ajuda.

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

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

postgres=# DROP USER douglas;
ERROR:  role "douglas" cannot be dropped because some objects depend on it
DETAIL:  owner of database douglas
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.

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

postgres=# DROP DATABASE douglas;
DROP DATABASE
postgres=# DROP USER douglas;
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:

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:

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:

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:

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:

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:

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:

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:

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 dando

Vamos primeiro criar um usuário especifico para este novo banco de dados.

useradd -m -s /usr/local/bin/bash usuario

Agora precisarmos setar uma senha para ele

passwd usuario 
Changing local password for usuario.
New password: #S3Nh4
Retype new password: #S3Nh4

Agora vamos criar o usuário no postgresql

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

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

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

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

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

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.

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

postgres=# GRANT ALL PRIVILEGES ON DATABASE teste TO usuario;
GRANT
postgres=# \q

Agora vamos inserir o postgres na inicialização do sistema

vim /etc/rc.conf
[...]
pkg_scripts="postgresql"

Agora pra testar vamos reiniciar o servidor

shutdown -r now

Agora vamos logar novamente no servidor pelo cliente

psql -h 10.101.0.100 -U postgres -W
Senha para usuário postgres: 
psql (9.2.1, servidor 9.1.2)
AVISO: psql versão 9.2, servidor versão 9.1.
         Algumas funcionalidades do psql podem não funcionar.
Digite "help" para ajuda.

postgres=# \q

Como pode ser notado está tudo ok ;)

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

1. http://wiki.postgresql.org/wiki/Main_Page
2. http://wiki.postgresql.org/wiki/FAQ/pt
3. http://www.postgresql.org/docs/manuals/
4. http://www.postgresql.org/docs/books/
5. http://www.postgresql.org/files/documentation/pdf/8.4/postgresql-8.4-A4.pdf
6. http://www.postgresql.org/files/documentation/pdf/9.1/postgresql-9.1-A4.pdf
7. http://www.postgresql.org/docs/8.1/static/backup.html