Instalando e configurando o PostgreSQL no CentOS
- Ip do servidor: 192.168.1.40
- Ip do cliente: 192.168.1.41
Prepare o seu sistema com o seguinte script http://wiki.douglasqsantos.com.br/doku.php/confinicialcentos6_en para que não falte nenhum pacote ou configuração.
Agora vamos instalar o postgreSQL
yum check-update && yum install postgresql postgresql-contrib postgresql-devel postgresql-docs postgresql-libs postgresql-server -y
Agora vamos inserir o postgresql na inicialização do sistema
chkconfig --add postgresql chkconfig postgresql on
Agora vamos mandar inicializar o banco de dados
/etc/init.d/postgresql initdb
Agora vamos inicializar o postgresql
/etc/init.d/postgresql start
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/lib/pgsql/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/lib/pgsql/data/pg_hba.conf [...] #vamos deixar o final do arquivo da seguinte forma # "local" is for Unix domain socket connections only local all all md5 # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5 host all all 192.168.1.41/32 md5Aqui no exemplo acima estamos liberando acesso para a máquina 192.168.1.41 porém podemos liberar acesso para uma rede inteira utilizando CIDR ex: 192.168.1.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 CentOS para efetuar um teste de conexão
yum check-update && yum install postgresql -y
Agora vamos testar a conexão
psql -h 192.168.1.40 -U postgres -W Senha para usuário postgres: psql (8.4.13) Digite "help" para ajuda. postgres=# \q
Agora vamos cadastrar mais um usuário para teste, teste usuário tem que existir no postgresql e no sistema.
Agora vamos criar este usuário no postgresql com direitos administrativos
su - postgres psql CREATE USER douglas WITH PASSWORD 'senha' SUPERUSER; CREATE ROLE postgres=# \q
Agora vamos conectar pelo cliente novamente.
psql -h 192.168.1.40 -U douglas -W Senha para usuário douglas: psql: FATAL: banco de dados "douglas" não existe
Recebemos um erro pois o padrão é que o usuário tenha uma base de dados com o seu nome vamos logar então com o postgres e criar o banco para o usuário
No cliente
psql -h 192.168.1.40 -U postgres -W Senha para usuário postgres: psql (8.4.13) Digite "help" para ajuda. postgres=# CREATE DATABASE douglas WITH OWNER douglas; CREATE DATABASE postgres=# \q
Pronto criamos a nossa base de dados com o nome do usuário agora vamos conectar no banco novamente.
psql -h 192.168.1.40 -U douglas -W Senha para usuário douglas: psql (8.4.13) 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 192.168.1.40 -U douglas -W -d postgres Senha para usuário douglas: psql (8.4.13) 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
su - postgres psql psql (8.4.13) Digite "help" para ajuda. postgres=# DROP USER douglas; ERRO: role "douglas" não pode ser removida porque alguns objetos dependem dela DETALHE: dono de banco de dados douglas postgres=# \qComo 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 psql psql (8.4.13) Digite "help" para ajuda. 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 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 ;)