Tutorial PostgreSQL ( instalar em ambiente Linux – Debian )

Publicado: 16/05/2011 em Linux, Serviços IP
Tags:, ,

Neste post disponibilizo algumas informações que avaliei serem essenciais para uma instalação rápida, fácil e segura do banco de dados PostgreSQL no ambiente Linux/Debian, além de alguns procedimentos para sua boa administração. Espero que o esforço deste post seja útil para você!

Um bom guia para o que está aqui exposto pode ser visualizado no manual do PostgreSQL em sua sessão Server Administration .

1. Instalar o PostgreSQL
a) Verificar qual a versão do pacote do servidor de Banco de Dados (BD) PostgreSQL disponível no repositório:
Antes de tudo, pode-se verificar se o PostgreSQL já está instalado na máquina alvo, qual a sua versão, e qual a versão do PostgreSQL que está disponível no repositório Debian para instalação. Verifique através do comando mostrado abaixo:
$ apt-cache policy postgresql

postgresql:
Instalado: (nenhum)
Candidato: 8.4.10-0squeeze1

Pelo resultado do comando, observa-se que o servidor de BD PostgreSQL ainda não está instalado na máquina alvo e que o candidato à instalação é a sua versão 8.4

b) Verificar qual a versão do pacote cliente PostgreSQL (interface de linha de comando) disponível no repositório
O servidor de BD PostgreSQL possui um cliente em modo terminal, o psql. Este cliente permite digitar os comandos interativamente, enviá-los para o PostgreSQL e ver os resultados. Alternativamente, a entrada pode vir de um arquivo. O psql possui um certo número de meta-comandos e diversas funcionalidades semelhantes às da shell facilitando a criação de scripts e automatizando uma grande variedade de tarefas.
$ apt-cache policy postgresql-client

postgresql-client:
Instalado: 8.4.10-0squeeze1
Candidato: 8.4.10-0squeeze1

c) Para fazer a instalação do servidor de BD PostgreSQL, de seu pacote client e de sua interface web de administração
# apt-get install postgresql postgresql-doc phppgadmin
Observações:

  • com este comando serão instalados simultaneamente o servidor de Banco de Dados PostgreSQL, o seu cliente (que oferece uma interface através de linha de comando para o usuário) e o cliente de administração phpPgAdmin via interface gráfica;
  • no ambiente do S.O. são criados o usuário “postgres” e o grupo “postgres”;
  • no ambiente do gerenciador de BD PostgreSQL são criados o usuário “postgres”, o banco de dados “postgres” e dois banco de dados modelo (template): “template0” e “template1”. É importante frisar que em toda criação de um novo banco de dados no PostgreSQL este necessita de um banco modelo, e que quando isto não é especificado no comando SQL de criação do novo banco o PostgreSQL utiliza por default o banco “template1” (veja mais sobre template databases no Manual do PostgreSQL);
  • o usuário “postgres” é o único usuário de BD na nova instalação, tem o atributo “Super-usuário” e NÃO tem senha de acesso. Neste momento, só é possível se conectar ao banco PostgreSQL utilizando este usuário, e, ao mesmo tempo, estando logado no S.O. como usuário “postgres”. Isto atende a seguinte regra presente no arquivo de controle de autenticação de clientes pg_hba.conf (que será abordado mais adiante neste post):

    local all postgres ident

    onde se vê o auth-method como “ident”: método de autenticação que solicita do S.O. o username do cliente e confronta com o username da base de dados. Para mais detalhes sobre autenticação de clientes veja a sessão Client Authentication no manual do PostgreSQL .

  • o arquivo de logs do PostgreSQL estará na pasta /var/log/postgresql/;
  • arquivos de configuração: a pasta com os arquivos de configuração do servidor PostgreSQL pode ser verificada através do seguinte comando:
    
    postgres=# SELECT name,setting FROM pg_settings where category = 'File Locations';
           name        |                 setting                  
    -------------------+------------------------------------------
     config_file       | /etc/postgresql/8.4/main/postgresql.conf
     data_directory    | /var/lib/postgresql/8.4/main
     external_pid_file | /var/run/postgresql/8.4-main.pid
     hba_file          | /etc/postgresql/8.4/main/pg_hba.conf
     ident_file        | /etc/postgresql/8.4/main/pg_ident.conf
    (5 registros)
    

    Assim, para esta instalação do PostgreSQL (no caso para a sua versão 8.4) a pasta com os arquivos de configuração está em /etc/postgresql/8.4/main/ . Esta pasta é chamada de “default PostgreSQL data folder”. Dois arquivos em especial merecem atenção nesta pasta:

    • postgresql.conf: contém a configuração geral do PostgreSQL, tais como quanto de memória a alocar, localização default das novas bases de dados, qusi os IPs que o PostgreSQL ouve, onde o “log” é guardado e outras;
    • pg_hba.conf: controle da segurança, onde está a definição do controle de autenticação de clientes. Controla a autenticação baseada em host a qual define quais os usuários podem se conectar a quais banco de dados e o como eles podem se conectar. O arquivo é uma lista de declarações, que são verificadas em ordem até que uma das linhas combina com a situação. Estas linhas listam o método de acesso (local, host e hostnossl/hostssl), o banco de dados que eles podem se conectar, o(s) usuário(s) autorizado(s) a se conectar e o método de autenticação a ser utilizado (trust, reject, md5 …).
  • para visualizar os usuários do banco de dados, duas tabelas em especial podem ser verificadas: pg_shadow e pg_user. Para acessar a primeira tabela é necessário ser um super usuário. Já para visualizar a tabela pg_user, a qual é identica a primeira só que mostra o campo relativo ao password como estrelas, basta ser um usuário normal. Caso o campo relativo ao password do usuário da tabela pg_shadow esteja em branco, significa que o usuário não tem senha cadastrada. A seguir exemplificamos os comandos para visualizar o conteúdo destas tabelas do sistema:
    psql=# select * from pg_shadow;
    psql=# select * from pg_user;

d) Instalar unicamente pacote PostgreSQL client (interface de linha de comando)
Se desejar apenas fazer a instalação da interface de linha de comando psql, basta fazer:
# apt-get install postgresql-client
Este procedimento é típico na máquina do usuário administrador do BD para se conectar à máquina remota onde está instalado o servidor de BD. O cliente pode se conectar a distintos servidores de BD, o que facilita a vida do administrador. É importante que a versão do cliente tenha correspondência com a versão do servidor de BD para que todos os comandos funcionem plenamente.

1.1 Verificação da instalação do pacote “postgresql”
Verificação 1: cache de pacotes do APT

$ apt-cache policy postgresql
postgresql:
Instalado: 8.4.10-0squeeze1
Candidato: 8.4.10-0squeeze1

Vê-se pela resposta do comando acima que o servidor de BD está instalado, sendo sua versão a 8.4

Verificação 2: processos ativos
Ao instalar o servidor PostgreSQL no Debian, o gerenciador de banco de dados é automaticamente iniciado pelo S.O. Transcrevemos abaixo apenas parte da resposta ao comando explicitado:

$ ps -ef
postgres     1  /usr/lib/postgresql/8.4/bin/postgres -D /var/lib/postgresql/8.4/main -c config_file=/etc/postgresql/8.4/main/postgresql.conf
postgres 22158  postgres: writer process                                                                                                    
postgres 22158  postgres: wal writer process                                                                                                
postgres 22158  postgres: autovacuum launcher process                                                                                       
postgres 22158  postgres: stats collector process

Verificação 3: usuário e grupo criado
Como dito, são criados no S.O. um usuário “postgres” e um grupo “postgres”, como se verica abaixo:


$ cat /etc/group | grep postgres
  postgres:x:108:

$ cat /etc/passwd | grep postgres
  postgres:x:104:108:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

# cat /etc/shadow | grep postgres
  postgres:*:16111:0:99999:7:::

$ passwd -S postgres
  postgres L 02/10/2014 0 99999 7 -1

Temos então a confirmação do usuário e grupo “postgres” criados. Mas pelos dois últimos comandos, vemos que o usuário do S.O. “postgres” ainda não possui senha de acesso e apresenta seu login bloqueado (“L”: locked). Para criar a senha deste usuário e desbloqueá-lo:

# passwd postgres

Agora vê-se a diferença com relação ao controle de acesso deste usuário “postgres” do S.O.:


# cat /etc/shadow | grep postgres
  postgres:$6$J4wQZ0P8$axIiRpc3ALQxlMM4M3wqxAFxgLbV/8LwGRvL8eEpqm/5M8Wm5muEhZHEELq3nbItBvtq0TPtdizhuOM4MWK0E0:16111:0:99999:7:::

$ passwd -S postgres
  postgres P 02/10/2014 0 99999 7 -1

Agora o usuário do S.O. “postgres” tem um “usable password (P)”, e poderá fazer login no sistema. Para verificar que o login do usuário “postgres” realmente está permitido, execute o comando abaixo digitando o password correspondente ao usuário:

meu_usuario$ su - postgres
postgres$

Agora o client do gerenciador de banco de dados PostgreSQL já pode ser utilizado:

postgres$ psql
psql (8.4.17)
Digite "help" para ajuda.
psql=#

2. Criar um usuário do BD, com privilégios de criação de Banco de Dados, para auxiliar nas demais demonstrações deste post
Por segurança, não se deve utilizar o usuário do banco de dados “postgres” pois este tem privilégios de “Super-usuário”. Vamos criar um novo usuário do banco. No terminal da máquina em que está o servidor de BD, fazer (em negrito os comandos do terminal):

meu_usuario$ su - postgres
postgres$ psql
psql (8.4.17)
Digite "help" para ajuda.
psql=# CREATE ROLE jose WITH LOGIN PASSWORD 'secret';
CREATE ROLE
psql=# ALTER ROLE jose WITH CREATEDB;
ALTER ROLE
psql=# \du

              Lista de roles
 Nome da role |   Atributos   | Membro de 
--------------+---------------+-----------
 jose         | Cria BD       | {}
 postgres     | Super-usuário | {}
              : Cria role       
              : Cria BD


 
Vemos acima a criação do usuário “jose”, com a posterior confirmação da sua criação. Recomendamos consultar no manual do PostgreSQL o comando SQL CREATE ROLE e sua designação de atributos.

No entanto, ao tentar utilizar o psql obtem-se uma negação:
$ psql -U jose -d postgres
psql: FATAL: autenticacao do tipo Ident falhou para usuario "jose"

Para elimitar esta regra de segurança que impede o uso do psql para o usuário “jose”, editar o arquivo /etc/postgresql/8.4/main/pg_hba.conf (para a versão 8.4 do PostgreSQL) e alterar uma linha de sua configuração que contém a declaração de autenticação ident:
DE:
# "local" is for Unix domain socket connections only
local all all ident


PARA:
local all all md5

Neste arquivo pg_hba.conf pode-se configurar várias regras de acesso. Deve-se ter cuidado para não fragilizar a segurança! No caso acima, a primeira regra só permite a conexão ao PostgreSQL através do auth-method “ident” (confrontação do usuário do BD com o usuário atual do S.O.). Ou seja, a tentativa de se conectar ao BD com usuário “jose” só será possível se partir do usuário do S.O. “jose”. Já a segunda regra permitirá se conectar ao servidor PostgreSQL partindo de qualquer usuário do S.O. para qualquer usuário do BD, desde que a senha correspondente ao usuário do BD seja fornecida corretamente.

a) Um exemplo prático de uma boa configuração para o arquivo pg_hba.conf

# TYPE  DATABASE    USER        CIDR-ADDRESS                   METHOD
  local   all      postgres		                       ident
  local   all         all			                md5
  host    all         all      191.176.38.51  255.255.255.255	md5


Na ordem acima, teríamos:

  • restringe o acesso ao banco utilizando o username do BD “postgres” apenas quando partir da própria máquina local, e estiver logado no S.O. como usuário também chamado “postgres”. É o que se chama “database administrative login by UNIX sockets”;
  • habilita acesso ao BD para os demais usuários cadastrados no BD desde que a requisição parta da máquina local (inclusive de serviços instalados localmente, como o Geoserver) e o password fornecido coincida com o que está cadastrado na base do BD. São as chamadas “Unix domain socket connections only”;
  • habilita o acesso ao BD de requisições que partam da máquina 191.176.38.51, desde que a combinação usuário/senha cadastrado no BD coincida.

3. Acessar o servidor PostgreSQL remotamente
Até o momento instalamos o servidor de banco de dados PostgreSQL e fizemos seu acesso com o usuário default “postgres” a partir da própria máquina onde está instalado (localhost). Agora vamos tentar acessar o servidor de banco de dados via TCP/IP a partir de uma máquina remota da rede. Em termos de ferramental de administração do PostgreSQL, podemos fazer isto com o cliente de linha de comando psql ou com aplicativos de interfaces gráficas como o phpPgAdmin ou PgAdminIII. Neste ponto vamos demonstrar com o psql, mas os problemas que encontraremos e as soluções dadas seriam as mesmas caso usássemos um aplicativo com interface gráfica.

Para conseguir fazer o acesso TCP/IP ao servidor PostgreSQL veremos a seguir que será necessário alterar configurações nos arquivos de configuração postgresql.conf e pg_hba.conf.

3.1 Instalar o cliente de linha de comando do PostgreSQL na máquina local do usuário
Se você deseja apenas se conectar a um servidor PostgreSQL, não há necessidade de se instalar o pacote completo PostgreSQL, devendo-se instalar apenas o pacote PostgreSQL-client. Para isto, na máquina local do usuário usar o seguinte comando:

# apt-get install postgresql-client
$ psql --version

OBS:
– é importante que a versão do psql local corresponda a versão do BD PostgreSQL instalado no servidor. Caso contrário, alguns comandos poderão não funcionar.
– o segundo comando acima verifica se o psql realmente está disponível, mostrando a sua versão.

3.2 Controle de acesso ao servidor BD PostgreSQL
O controle de acesso ao PostgreSQL passa por diversas etapas de validação: domínio de origem da solicitação de conexão do usuário, sua senha, autorização de acesso ao banco de dados solicitado, autorização de acesso à tabela solicitada e o direito de execução da operação sobre a tabela. Se a solicitação do usuário for negada em alguma destas validações, uma mensagem de erro será enviada ao terminal do usuário (e registrada no arquivo de “log”) e a operação abortada. É preciso atenção do usuário para entender o problema que está acontecendo para proceder a sua superação. É o que veremos nos próximos tópicos a seguir.

a) Tentativa de acesso frustrada devido conexões via TCP/IP desabilitadas
Por padrão, ao instalar o PostgreSQL as conexões via TCP/IP estão desabilitadas. É o que exploraremos neste item.
Vamos tentar fazer acesso ao servidor PostgreSQL remoto a partir da máquina local do usuário. Para se conectar a um banco de dados é necessário informar:
– o nome do banco de dados (parâmetro -d);
– nome da máquina (parâmetro -h);
– número da porta do servidor (parâmetro -p);
– e o nome de usuário a ser usado para a conexão (parâmetro -U).
A porta default do PostgreSQL é 5432.

Na máquina local, fazer:
$ psql -h IP-servidor -U jose -d postgres

Deverá se obter uma mensagem de erro semelhante a:

psql: não pôde conectar ao servidor: Conexão recusada
	O servidor está executando na máquina "IP-servidor" e aceitando
	conexões TCP/IP na porta 5432?

Este erro significa que conexões remotas ao servidor PostgreSQL estão bloqueadas. Para possiblitar este tipo de conexão deve ser alterada uma configuração do servidor (máquina remota) em /etc/postgresql/8.4/main/postgresql.conf:

DE:
#listen_addresses = 'localhost'
PARA:
listen_addresses = '*'

Ou de uma forma mais segura, por exemplo, se o usuário está em uma máquina no domínio 191.178.30.178, deve-se configurar:

listen_addresses = 'localhost, 191.178.30.178'

e não esquecer de reiniciar o servidor remoto:
# /etc/init.d/postgres restart

b) Tentativa de acesso frustrada devido domínio de origem não autorizado
Após alterar a configuração do servidor PostgreSQL remoto, fazer nova tentativa de conexão a partir da máquina local. Um novo erro deverá ocorrer como ilustrado abaixo:

$ psql -h IP-servidor -U jose -d postgres
psql: FATAL:  nenhuma entrada no pg_hba.conf para máquina "IP-maquina_local", usuário "jose", banco de dados "postgres", SSL habilitado
FATAL:  nenhuma entrada no pg_hba.conf para máquina "IP-maquina_local", usuário "jose", banco de dados "postgres", SSL desabilitado

Isto se deve ao fato que no arquivo de configuração pg_hba.conf não está previsto o acesso remoto ao PostgreSQL a partir da máquina remota em uso. Há necessidade de habilitar o acesso a partir da máquina em uso. Se, por exemplo, a máquina local tem IP 189.176.32.186, altere o arquivo em /etc/postgresql/8.4/main/pg_hba.conf inserindo ao final deste a seguinte linha:

# IPv4 remote connections:
host all all 189.176.32.1/24 md5

No caso acima permite acesso ao PostgreSQL das máquinas partindo do domínio 189.176.32.xxx, a qualquer base de dados e de qualquer usuário (claro a senha do usuário ainda será validada).

Se desejar liberar apenas o acesso de um determinado domínio, para todos os banco de dados e a qualquer usuário, pode-se fazer:
# IPv4 remote connections:
host all all 189.176.32.43 255.255.255.255 md5

Reiniciar o servidor PostgreSQL e retentar a conexão remota, o que deverá obter êxito:
Do servidor:
# /etc/init.d/postgres restart
Da máquina local do usuário:
$ psql -h IP-servidor -U jose -d postgres

OBS:
– a configuração para acesso ao PostgreSQL a ser realizada no pg_hba.conf obedece ao seguinte formato:
TYPE | DATABASE | USER | CIDR-ADDRESS | METHOD

4. Entendendo um pouco mais o psql
Um conjunto de informações essenciais sobre o psql podem ser encontradas na internet, como este material em psql — terminal interativo do PostgreSQL, ou no próprio manual oficial do PostgreSQL na sua sessão relativa ao psql. O psql possui alguns parâmetros que podem ser alterados. Para conhecer os valores destes parâmetros que estão em vigor, fazer (após estar com o prompt do psql):
psql=#\set

AUTOCOMMIT = ‘on’
PROMPT1 = ‘%n@%m %/%R%# ‘
PROMPT2 = ‘%/%R%# ‘
PROMPT3 = ‘>> ‘
VERBOSITY = ‘default’
VERSION = ‘PostgreSQL 8.4.17 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit’
DBNAME = ‘postgres’
USER = ‘postgres’
HOST = ‘/var/run/postgresql’
PORT = ‘5432’
ENCODING = ‘UTF8’

4.1 Personalizar o prompt do psql
O prompt do psql pode ser alterado, como ilustrado através dos dois exemplos abaixo:
psql=# \set PROMPT1 '%n@%m %/%R%# '
postgres@[local] postgres=#

ou
psql=# \set PROMPT1 '%n@%M:%>%x %/# '
postgres@[local]:5432 postgres#

Para automatizar, o melhor é colocar esta configuração na sessão de configuração do psql: o arquivo .psqlrc . Este arquivo deve existir na pasta “home” do usuário que está lançando o psql. Toda vez que o psql for lançado, e após o usuário conseguir se autenticar com o servidor PostgreSQL, automaticamente todos os comandos dentro deste arquivo serão executados. Isso evita ter que redigitar comandos toda vez que se lança o psql. Quaisquer comandos aceitos pelo psql poderão ser colocados dentro deste arquivo, e não apenas os exemplos anteriores relativos a personalização do prompt. Por exemplo, a listagem do arquivo .psqlrc abaixo é um arquivo válido:

\set PROMPT1 '%n@%M:%>%x %/# '
\l
\du
\c bancoRH
SET search_path TO esquema_rh,public

5. Entendendo um pouco mais o arquivo pg_hba.conf
O arquivo de controle da segurança /etc/postgresql/8.4/main/pg_hba.conf é uma lista de declarações, que são verificadas na ordem de cima para baixo até que uma das linhas combine com a situação. Estas linhas listam o método de acesso (local, host e hostnossl/hostssl), o banco de dados que eles podem se conectar, o(s) usuário(s) autorizado(s) a se conectar e o método de autenticação a ser utilizado (ident, reject, md5 …).

Vejamos alguns exemplos:
a) Exemplo-1:


# TYPE  DATABASE    USER    CIDR-ADDRESS            METHOD
  local   all      postgres                         ident

Interpretação:
O role postgres pode se conectar ao servidor PostgreSQL:
– estando logado no S.O. local (localhost);
– estando logado no S.O. como usuário postgres, já que o auth-method é “ident” (perceba que não haverá solicitação de senha devido ao “ident”).

Se em vez de “ident” houvesse “md5”, teria-se a seguinte regra para o role postgres:
– a conexão ao BD só poderá ocorrer a partir do localhost;
– a conexão poderá se estabelecer independentemente de qual usuário do S.O. esteja solicitando, mas sempre tendo que fornecer a senha correta do role postgres.

b) Exemplo-2:


# TYPE  DATABASE    USER    CIDR-ADDRESS            METHOD
  local   all      postgres 		             ident
  local   al         all                             ident

Interpretação:
O role postgres pode se conectar ao servidor PostgreSQL na forma descrita no exemplo-1.

Já para os demais roles do BD, partido do localhost:
– só haverá conexão se coincidir o nome do role com o nome do usuário do S.O. de onde parta a solicitação de conexão ao banco. Já que o auth-method é “ident”, não haverá solicitação de senha caso exista a coincidencia role-usuário.

Se em vez de “ident” houvesse “md5” na segunda linha, teria-se a seguinte regra:
– para o role postgres manteria-se a regra anterior;
– para os demais roles, a conexão partindo do localhost poderá se estabelecer independentemente de qual usuário do S.O., mas sempre tendo que se fornecer a senha correta do role (auth-method “md5”).

c) Exemplo-3:


# TYPE  DATABASE    USER            CIDR-ADDRESS                METHOD
  local   all      postgres 		                        ident
  local   all        all		                        md5
  host    all        all    191.178.30.178	255.255.255.255	md5

Interpretação:
– para o role postgres mantem-se a regra anterior;
– para os demais roles partindo do localhost, a conexão ao PostgreSQL poderá se estabelecer independentemente de qual usuário do S.O. está solicitando, mas desde que forneça a senha correta do role (auth-method “md5”).
– para todos os roles (inclusive para o role postgres) cuja solicitação parta do host com IP 191.178.30.178, a conexão poderá se estabelecer desde que se forneça a senha correta do role (auth-method “md5”).

6. Entendendo os parâmetros de run-time do PostgreSQL
O PostgreSQL possui um conjunto de parâmetros de configuração de “run-time”. Os parâmetros podem ser definidos de 3 (três) maneiras distintas:
– dentro do arquivo postgresql.conf;
– através de uma linha de comando;
– através dos comandos do psql SET e RESET.

Existem três comandos especiais que acessam estes parâmetros: SHOW, SET e RESET. Alguns parâmetros não podem ser modificados via SET: por exemplo, se eles controlam o comportamento do PostgreSQL que não podem ser alterados sem que se tenha de reiniciá-lo. Também alguns parâmetros só podem ser modificados via SET ou ALTER por superusuários.

Exemplos de utilização destes comandos:

psql=# SET datestyle TO postgres, dmy;
psql=# SET search_path TO my_schema, public;
psql=# SET TIME ZONE 'Europe/Rome';
psql=# SET search_path TO DEFAULT;
psql=# RESET search_path;
psql=# RESET timezone;
psql=# SHOW ALL;
psql=# SHOW search_path;
psql=# SHOW max_connections;

OBS:
– o comando RESET restaura o valor do parâmetro de “run-time” para o valor “default”. Este valor “default” é definido como o valor que o parâmetro teria, se não houvesse ocorrido o comando SET na sessão atual;
– as mudanças através do comando SET permanecem com efeito enquanto durar a conexão à base de dados. Ao fazer uma conexão a outra base de dados do PostgreSQL, os efeitos do comando SET cessarão.

7. Comandos básicos
7.1 Aguns comandos essenciais do psql (cliente de linha de comando do PostgreSQL)

Comando Comentário
meu_usuario$ su - postgres acessar usuário postgres do S.O.
posgres$ psql chama o programa cliente psql. Conecta-se automaticamente ao BD postgres (situação default)
postgres$ psql -d banco1 chama o programa cliente psql. Conecta-se automaticamente ao BD banco1
psql=# \? para help dos comandos do psql
psql=# \h para help dos comandos SQL
psql=# \l
psql=# \l+
listar as base de dados
psql=# \du listar roles (usuários)
psql=# \dg listar roles (grupos)
psql=# \c dbexemplo comando para conectar ao BD dbexemplo
psql=# \dn+ listar os esquemas
psql=# \dt pg_catalog.* listar as tabelas pertencentes ao esquema pg_catalog
psql=# \db listar os tablespaces
psql=# \q sair do psql

 
7.2 Alguns comandos essenciais SQL

Comando Comentário
psql=# CREATE DATABASE dbexemplo TEMPLATE template1; criar uma base de dados

psql=# CREATE ROLE nome_usuario WITH LOGIN PASSWORD 'senha_usuario';
psql=# CREATE ROLE nome_usuario WITH CREATEDB CREATEROLE LOGIN PASSWORD 'senha_usuario';
criar ROLE permitindo:
a) login no banco
b) login, criar usuário e BD
psql=# ALTER ROLE nome_usuario WITH CREATEDB CREATEROLE;
psql=# ALTER ROLE nome_usuario WITH PASSWORD 'secret';
altera atributos do usuário
psql=# DROP ROLE nome_usuario; remover usuário e/ou regra

 
8. phpPgAdmin
A pasta com os arquivos de configuração do phpPgAdmin está em /etc/phppgadmin/ .
8.1 Liberar acesso para o phpPgAdmin
– no arquivo /etc/phppgadmin/config.inc.php confirmar as seguintes linhas:
$conf['servers']['0']['host'] = 'localhost';
$conf['servers']['0']['defaultdb'] = 'postgres';
$conf['extra_login_security'] = true;

8.2 Configurar o phpPgAdmin (Interface Web de manipulação do banco de dados)

a) Autenticar-se como usuário postgres
$ su postgres

b) Criar um novo usuário do PostgreSQL para fazer acesso via phpPgAdmin
postgres$ createuser nome_novo_usuario
A nova role poderá criar um super-usuário? (s/n) n
A nova role poderá criar bancos de dados? (s/n) s
A nova role poderá criar novas roles? (s/n) s

OBS:
i- O comando “createuser” define um novo usuário do PostgreSQL (ou mais precisamente, um papel). Somente os superusuários e usuários com privilégio de “CREATEROLE” pode criar novos usuários. Portanto, este comando de criar usuário deve ser executado por alguém que é superusuário ou um usuário com privilégios de CREATEROLE.

ii- A criação deste novo usuário PostgreSQL, de nome diferente de “postgres”, é necessário por razões de segurança já que o phpPgAdmin não aceita este nome.

c) Criar  senha para o novo usuário que fora criado
postgres$ psql postgres
postgres=# ALTER ROLE nome_novo_usuario WITH password 'senha_usuario';

d) Se desejar criar uma base de dados
postgres$ createdb -E UNICODE db_nova

OBS: o comando “createdb” cria uma nova base de dados PostgreSQL. Normalmente o usuário que executou este comando se torna o proprietário da nova base de dados.

e) Sair:
postgres=# \q

f) Configurar o phpPgAdmin

Caso o phpPgAdmin necessite ser acessado de uma máquina remota, como por exemplo pela URL  http://<IP_servidor>/phppgadmin/ , uma mensagem de erro deverá mostrada: “client denied by server configuration: /usr/share/phppgadmin/”.

A mensagem de erro é fruto de uma regra de segurança: a configuração default é só permitir acesso ao phpPgAdmin em localhost. Para solucionar esta restrição, edite o arquivo /etc/apache2/conf.d/phppgadmin da seguinte forma:

COMENTAR A LINHA: “allow from 127.0.0.0/255.0.0.0″
DESCOMENTAR A LINHA:  “allow from all”

OBS: ou de uma forma mais segura, restrinja mais o acesso utilizando a notação CIDR:
allow from 192.168.0.100/24 (neste exemplo, permitindo apenas as máquinas em uma rede classe C 192.168.0.xxx fazerem acesso ao PhpPgAdmin.

g) Após essas configurações, reinicie o Postgresql e Apache:

# /etc/init.d/postgresql restart
# /etc/init.d/apache2 restart

8.3 Acessando o phpPgAdmin
Com o navegador, acesse: http://localhost/phppgadmin/ e faça login utilizando o nome do novo usuário criado “nome_novo_usuario” e sua senha.

Links:
1- Manual do PostgreSQL: PostgreSQL tutorial
2- Manual do PostgreSQL: PostgreSQL Client Authentication
3- Manual do PostgreSQL: SQL commands supported by PostgreSQL
4- psql
5- PostgreSQL Tutorial
6- PostgreSQL User Administration
7- Administração de um cluster de bases de dados PostgreSQL

Anúncios
comentários
  1. justdibr disse:

    PUTA MERDA, QUE AULA! Muito obrigado! To até emocionado!

  2. Michel disse:

    Muito bom mesmo, resolveu o problema que estava tendo
    Obrigado

  3. Misael Primaz disse:

    Muito bom, me ajudou bastante a entender.

  4. Leandro disse:

    Muito bom, Parabéns!

    Me ajudou a instalar no Ubuntu.

Deixe um comentário, pois isto é muito motivante para continuarmos este trabalho

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s