Tutorial PostgreSQL ( instalar em ambiente Linux – Debian )

Tutorial PostgreSQL ( instalar em ambiente Linux – Debian ) | Blog do Albuquerque

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 identonde 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.

FONTE: https://concani3.wordpress.com/2011/05/16/instalar-e-configurar-postgresql-postgis-e-phppgadmin-no-debian/

Deixe um comentário Cancelar resposta