Instalando e configurando o PostgreSQL no OpenBSD
- Ip do servidor: 10.101.0.100
- 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 md5Aqui 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:
Opção | Descrição |
---|---|
SELECT | Permite selecionar registros da tabela em questão |
INSERT | Permite inserir registros na tabela em questão |
UPDATE | Permite atualizar registros na tabela em questão |
DELETE | Permite excluir registros na tabela em questão |
REFERENCES | Permite utilizar registros desta tabela como chave estrangeira de outras tabelas |
TRIGGER | Permite criar triggers na tabela em questão. |
ALL | Habilita 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ção | Descrição |
---|---|
USAGE | Permite utilizar a numeração da sequência. |
SELECT | Permite capturar o valor numérico da sequência |
UPDATE | Permite atualizar o valor numérico da sequência |
ALL | Habilita 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ção | Descrição |
---|---|
CREATE | Permite criar objetos no banco de dados. |
CONNECT | Permite conectar ao banco de dados |
TEMPORARY | Permite 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. |
TEMP | O mesmo que a opção TEMPORARY |
ALL | Habilita 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ção | Descrição |
---|---|
EXECUTE | Permite invocar a executar a função |
ALL | Habilita 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ção | Descrição |
---|---|
USAGE | Permite utilizar a linguagem em questão. |
ALL | Habilita 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ção | Descrição |
---|---|
CREATE | Permite criar objetos no esquema em questão |
USAGE | Permite utilizar objetos criados dentro do esquema em questão. |
ALL | Habilita 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ção | Descrição |
---|---|
CREATE | Permite criar tablespaces no banco de dados |
ALL | Habilita 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ção | Descriçã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 groupname | Caso seja um grupo o beneficiário do comando, é possível informar o nome utilizando a opção GROUP |
PUBLIC | Se 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 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 ;)