Differences

This shows you the differences between two versions of the page.

Link to this comparison view

instalando_e_configurando_o_postgresql_no_openbsd_pt_br [2017/09/05 12:18] (current)
Line 1: Line 1:
 +====== ​ 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
 +<sxh bash>
 +export PKG_PATH=ftp://​openbsd.locaweb.com.br/​pub/​OpenBSD/​5.1/​packages/​`machine -a`/
 +</​sxh>​
 +
 +Agora vamos instalar o postgreSQL
 +
 +<sxh bash>
 +pkg_add -v postgresql-server postgresql-client postgresql-docs
 +</​sxh>​
 +
 +Agora vamos mandar setar uma senha para o postgres
 +<sxh bash>
 +passwd _postgresql
 +Changing local password for _postgresql.
 +New password: #S3Nh4
 +Retype new password: #S3Nh4
 +</​sxh>​
 +
 +Agora vamos logar com o Postgres
 +<sxh bash>
 +su - _postgresql
 +</​sxh>​
 +
 +Agora vamos criar o diretório que vai armazenar os dados do Postgres
 +<sxh bash>
 +mkdir /​var/​postgresql/​data
 +</​sxh>​
 +
 +Agora vamos inicializar o banco e setar uma senha para o usuário postgres interno do Banco
 +<sxh bash>
 +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
 +
 +</​sxh>​
 +
 +Agora vamos sair do usuário postgres
 +<sxh bash>
 +exit
 +</​sxh>​
 +
 +
 +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.
 +
 +<sxh bash>
 +vim /​var/​postgresql/​data/​postgresql.conf
 +[...]
 +listen_address = '​*'​
 +[...]
 +</​sxh>​
 +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
 +
 +<sxh bash>
 +vim /​var/​postgresql/​data/​pg_hba.conf
 +[...]
 +#no final do arquivo adicione a seguinte linha
 +host    all         ​all ​        ​10.101.0.0/​24 ​            md5
 +</​sxh>​
 +Aqui no exemplo acima estamos liberando acesso para as máquinas da rede 10.101.0.0/​24
 +
 +Agora vamos reiniciar o nosso postgreSQL
 +<sxh bash>
 +/​etc/​rc.d/​postgresql restart
 +</​sxh>​
 +
 +Agora vamos verificar se ele esta escutando ​
 +<sxh bash>
 +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
 +</​sxh>​
 +
 +
 +Agora vamos acessar o nosso postgres para ver se está tudo ok
 +<sxh bash>
 +psql -h localhost -U postgres -W
 +Password for user postgres: ​
 +psql (9.1.2)
 +Type "​help"​ for help.
 +
 +postgres=# \q
 +</​sxh>​
 +
 +Agora vamos instalar o cliente em uma maquina Arch Linux para efetuar um teste de conexão
 +
 +<sxh bash>
 +pacman -Suy psqlodbc
 +</​sxh>​
 +
 +Agora vamos testar a conexão
 +
 +<sxh bash>
 +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
 +</​sxh>​
 +
 +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
 +
 +<sxh bash>
 +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
 +</​sxh>​
 +
 +Agora vamos conectar pelo cliente novamente.
 +
 +<sxh bash>
 +psql -h 10.101.0.100 -U douglas -W
 +Senha para usuário douglas: ​
 +psql: FATAL: ​ database "​douglas"​ does not exist
 +</​sxh>​
 +
 +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
 +<sxh bash>
 +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;
 +</​sxh>​
 +
 +Agora vamos logar no banco que criamos
 +<sxh bash>
 +psql douglas
 +Password: #S3Nh4
 +psql (9.1.2)
 +Type "​help"​ for help.
 +
 +douglas=# \q
 +</​sxh>​
 +
 +Pronto criamos a nossa base de dados com o nome do usuário agora vamos conectar no banco novamente.
 +
 +<sxh bash>
 +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
 +</​sxh>​
 +
 +Como pode ser notado ocorreu tudo certo, agora vamos ver como acessamos outro banco de dados pelo cliente.
 +
 +<sxh bash>
 +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
 +</​sxh>​
 +
 +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
 +
 +<sxh bash>
 +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
 +</​sxh>​
 +
 +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.
 +
 +<sxh bash>
 +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
 +</​sxh>​
 +
 +** 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:
 +
 +
 +<sxh bash>
 +GRANT role[,​role2,​ ...] TO usuário [,​usuário2,​ ...] [WITH ADMIN OPTION];
 +</​sxh>​
 +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**
 +<sxh bash>
 +GRANT opções_tabela
 +ON TABLE tabela [, tabela2, ...]
 +TO beneficiários;​
 +</​sxh>​
 +**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**
 +<sxh bash>
 +GRANT opções_sequência
 +ON SEQUENCE sequência[,​sequência2,​...]
 +TO beneficiários;​
 +</​sxh>​
 +**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**
 +<sxh bash>
 +GRANT opções_banco
 +ON DATABASE banco[,​banco2,​...]
 +TO beneficiários;​
 +</​sxh>​
 +**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**
 +<sxh bash>
 +GRANT opções_funções
 +ON FUNCTION função (argumentos) [,​função2(argumentos),​...]
 +TO beneficiários;​
 +</​sxh>​
 +**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**
 +<sxh bash>
 +GRANT opções_linguagem
 +ON LANGUAGE linguagem [, linguagem2, ...]
 +TO benificiários;​
 +</​sxh>​
 +**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**
 +<sxh bash>
 +GRANT opções_schema
 +ON SCHEMA schema [,​schema2,​...]
 +TO benificiários;​
 +</​sxh>​
 +**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**
 +<sxh bash>
 +GRANT opções_tablespaces
 +ON TABLESPACE tablespace [, tablespaces2,​ ...]
 +TO beneficiários;​
 +</​sxh>​
 +
 +**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.
 +<sxh bash>
 +useradd -m -s /​usr/​local/​bin/​bash usuario
 +</​sxh>​
 +
 +Agora precisarmos setar uma senha para ele
 +<sxh bash>
 +passwd usuario ​
 +Changing local password for usuario.
 +New password: #S3Nh4
 +Retype new password: #S3Nh4
 +</​sxh>​
 +
 +Agora vamos criar o usuário no postgresql
 +<sxh bash>
 +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
 +</​sxh>​
 +
 +Agora vamos criar um banco de dados com o usuário postgres
 +<sxh bash>
 +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
 +</​sxh>​
 +
 +**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.
 +
 +<sxh bash>
 +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
 +</​sxh>​
 +
 +Agora vamos inserir o postgres na inicialização do sistema
 +<sxh bash>
 +vim /​etc/​rc.conf
 +[...]
 +pkg_scripts="​postgresql"​
 +</​sxh>​
 +
 +Agora pra testar vamos reiniciar o servidor
 +<sxh bash>
 +shutdown -r now
 +</​sxh>​
 +
 +Agora vamos logar novamente no servidor pelo cliente
 +<sxh bash>
 +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
 +</​sxh>​
 +
 +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.
 +<sxh bash>
 +export PGPASSWORD="​senha"​
 +</​sxh>​
 +
 +
 +<sxh bash>
 +pg_dumpall -i -h localhost -U postgres -c -o -f arquivo.backup
 +</​sxh>​
 +
 +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
 +<sxh bash>
 +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
 +</​sxh>​
 +
 +Agora vamos tentar logar no banco de dados teste que excluímos
 +<sxh bash>
 +psql -h localhost -U postgres -W -d teste
 +Password for user postgres: ​
 +psql: FATAL: ​ database "​teste"​ does not exist
 +</​sxh>​
 +
 +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.
 +<sxh bash>
 +export PGPASSWORD="​senha"​
 +</​sxh>​
 +
 +Agora vamos importar o dump
 +<sxh bash>
 +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
 +</​sxh>​
 +
 +Agora vamos mandar logar no banco teste novamente
 +<sxh bash>
 +psql -h localhost -U postgres -W -d teste
 +Password for user postgres: ​
 +psql (9.1.2)
 +Type "​help"​ for help.
 +
 +teste=# \q
 +</​sxh>​
 +
 +Está restaurado ;)
 +
 +
 +====== Referências ======
 +
 +
 +  - http://​wiki.postgresql.org/​wiki/​Main_Page
 +  - http://​wiki.postgresql.org/​wiki/​FAQ/​pt
 +  - http://​www.postgresql.org/​docs/​manuals/​
 +  - http://​www.postgresql.org/​docs/​books/​
 +  - http://​www.postgresql.org/​files/​documentation/​pdf/​8.4/​postgresql-8.4-A4.pdf
 +  - http://​www.postgresql.org/​files/​documentation/​pdf/​9.1/​postgresql-9.1-A4.pdf
 +  - http://​www.postgresql.org/​docs/​8.1/​static/​backup.html