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 [2019/08/08 15:24] (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  *.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
Print/export
QR Code
QR Code instalando_e_configurando_o_postgresql_no_openbsd_pt_br (generated for current page)