Aprendendo a criar e gerenciar usuários no PostgreSQL(Primeiros Passos)

Criando seu 1° super usuário no PostgreSQL.

Depois de instalado seu SGBD postgres, como criar seu 1° usuário? Qual a senha de root? Qual a senha padrão?

Esta dica é para iniciantes.

Simples, na máquina com o banco instalado siga os seguintes passos, existem varias maneiras de fazer isso, e essa foi a maneira mais básica/simples que eu achei para explicar.

Logue-se como root, digite “su root” ou “sudo -i” ou “sudo su” (no terminal a mensagem padrão deve terminar com #).
Entre como usuário postgres com o comando:

?

1	# su postgres

Conecte ao Shell do postgres (por padrão você se conecta ao DB postgres) comando:

?

1

$ psql

Agora deve aparecer uma frase de boas vindas (Bem vindo ao psql…) e você está no terminal do posgres, note que o shell mudou antes era root@pc # agora ficou postgres=#
Isso quer dizer que agora você está no shell do PostgreSQL conectado no banco postgres.

Para criar nosso superuser, digite o comando:

?

1	CREATE USER nomedousuario SUPERUSER INHERIT CREATEDB CREATEROLE;

Depois temos que alterar a senha, então digite  o comando:

?

1	ALTER USER nomedousuario PASSWORD 'senha';

Olha que maravilha! Super Usuário criado.

Comandos básicos, para você que está acostumado com o golfinho, o Mamute é bem diferente.

# psql
\l => Lista todos os bancos de dados
\c nome-banco  => Conecta no banco nome-banco
\d  => Lista as tabelas do banco de dados conectado
\d  table-name => Descrimina as colunas da tabela
\dg => Lista usuários existentes, as permissões e grupos que o usuário pertence
\password [usuario] => Troca a senha do usuário com segurança, ou seja você não vê a seha que foi digitada e não é possível ver através do histórico de comandos.
select * from table-name  => Lista o conteúdo das tabelas (o.O)

Poderíamos usar essas opções da mesma forma, mas criando um usuário comum e colocando as permissões mais restritivas.

?

1	CREATE USER jesuejunior WITH PASSWORD 'supersecret';

Para conceder a permissão de executar somente Update ou Select ou Insert ou Delete ou Rule(dono)

?

1	GRANT UPDATE ON nomedatabela to jesuejunior

?

1	GRANT SELECT ON nomedatabela to jesuejunior

?

1	GRANT INSERT ON nomedatabela to jesuejunior

?

1	GRANT DELETE ON nomedatabela to jesuejunior

?

1	GRANT RULE ON nomedatabela to jesuejunior

E para dar permissão para fazer todas as operações.

?

1	GRANT ALL PRIVILEGES ON nomedatabela to public

Partindo do principio que você já está conectado com o usuário postgres.
Para apagar um usuário:

?

1	$ dropuser jesuejunior

Gere Automaticamente seus Comandos GRANT e REVOKE!

Os comandos GRANT e REVOKE concedem e retiram permissões de acesso dos usuários aos objetos do banco de dados relativas à inserção, exclusão e alteração de dados, entre outras possibilidades. Este tipo de procedimento não é muito comum porque ambos os comandos apresentam sintaxes simples que permitem a concessão de acessos sem a necessidade de automação.

Para construir scripts para automatizar a concessão e revogação destes acessos, o primeiro passo é saber quais são os usuários cadastrados no servidor.

1. Quais são os usuários cadastrados?

Para conceder ou revogar privilégios aos usuários, é interessante saber quantos e quais usuários estão cadastrados no seu SGBD, e uma consulta a PG_USER .

select * from pg_user;

usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig

----------+----------+-------------+----------+-----------+----------+----------+-----------

postgres |       10 | t           | t        | t         | ******** |          |

gisuser  |    17141 | f           | f        | f         | ******** |          |

A próxima etapa é identificar as tabelas para as quais será concedido acesso.

2. Quais são as tabelas criadas no banco?

Uma consulta aos metadados de PG_TABLES retorna o nome das tabelas utilizadas. Observe que na consulta, selecionamos apenas as  tabelas do schema public, ignorando as tabelas de sistema.

pf=# select tablename from pg_tables where schemaname = 'public';
 tablename
-----------
 pfdet2011
 pf2011
 ns2011
 nsdet2011
 cliente
(5 registros)

3. Concedendo Acessos em Massa

Com o comando GRANT, posso conceder permissões de inclusão, alteração e exclusão nas tabelas do banco para um determinado usuário. Basta executar este select e utilizar o resultado da consulta como entrada para o postgresql:

pf=# select 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || tablename || ' TO 

postgres'  from pg_tables where schemaname = 'public';
                           ?column?                           
---------------------------------------------------------------
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO postgres
(5 registros)

Uma pequena alteração no script faz o produto cartesiano entre tabelas e usuários, gerando todas as combinações:

pf=# select 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || tab.tablename || ' TO ' || usu.usename || ' ; ' as COMANDO  from pg_tables tab, pg_user usu where tab.schemaname = 'public' ;
                             comando                             
------------------------------------------------------------------
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO hacker ;
(20 registros)

4. Revogando permissões de acesso

Com o comando REVOKE, as permissões  para todos os usuários podem ser revogadas instantaneamente:

pf=# select 'REVOKE SELECT, INSERT, UPDATE, DELETE ON ' || tab.tablename || ' FROM ' || usu.usename || ' ; ' as COMANDO  from pg_tables tab, pg_user usu where tab.schemaname = 'public' ;
                               comando                              
---------------------------------------------------------------------
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM hacker ;
(20 registros) 

5. Considerações Práticas

Como já foi mencionado neste post, a concessão de acesssos com GRANT e REVOKE raramente demanda alguma automação. Sintaxes poderosas e simples resolvem o problema sem maiores problemas, geralmente sendo executadas diretamente pelo DBA:

GRANT ALL ON DATABASE postgres TO hacker;

REVOKE ALL ON DATABASE postgres FROM hacker;

Dicas de PostgreSQL

Criando banco

**************************************************************************

torne-se root

gutocarvalho@pgsql01:~$ sudo -i

torne-se postgresql

root@pgsql:~$# su postgres

acesse o CLI do postgres

postgres@pgsql01:~$ psql

agora estamos na CLI

postgres=#

Criando usuario com senha

postgres=# CREATE USER fernandao WITH PASSWORD 'senha';

Criando o banco

postgres=# CREATE DATABASE eleicoes_carga;

Dando permissoes ao usuario para acessar o banco

postgres=# GRANT ALL PRIVILEGES ON DATABASE projeto_carga to fernando;

alterando owner do banco

postgres=# ALTER DATABASE projeto_carga OWNER TO fernando;

liberando acesso de uma maquina ao banco

após isto basta liberar o ip o cara no pg_hba.conf, veja um exemplo

# IPv4 local connections:  

host    all         all         127.0.0.1/32          md5  
host    all         all         172.16.10.20/32       md5 # gandalf  

configurando postgres para listar em todas as interfaces

e tenha certeza que o postgresql está rodando em todas as interfaces, veja isso em postgresql.conf

listen_addresses = '0.0.0.0'            # what IP address(es) to listen on;

precisa reiniciar o banco.

dump

dump de um banco inteiro - formato sql puro

pg_dump -U usuario -h ip_servidor nomedobanco > nomedobanco.sql

dump de um banco inteiro - formato binario

pg_dump -U usuario -h ip_servidor -Fc -f banco.dump nome_do_banco

dump de uma tabela do banco

pg_dump -U usuario -h ip_servidor -Fc -t tabela -f tabela.pgdump nome_do_banco

dump de um table de um schema

pg_dump -U postgres -h 127.0.0.1 -Fc -n schema -t tabela -f tabela.pgdump banco

restore

para restaurar um banco com dump em formato SQL

psql -U usuario -h ip_do_servidor -f nomeodabanco.sql nomedobanco

para restaurar um banco com dump em formato binário

pg_restore -U usuario -h ip_servidor -d nome_do_banco arquivo.dump

para restaurar somente os dados:

pg_restore -U usuario -h ip_servidor -a -d nome_do_banco arquivo.dump

para restaurar somente um determinado esquema

pg_restore -U usuario -h ip_servidor -n meu_esquema -d nome_do_banco arquivo.dump

para restaurar apenas os dados de uma tabela especifica

pg_restore -U usuario -h ip_servidor -a -t tabela_especifica -d nome_do_banco arquivo.dump

exemplos

fazendo dump de tabela da radioagencia

pg_dump -Fc -U drupal -h 127.0.0.1 -t drupal_webform -f arquivo -d drupal drupal_webform

restaurando tabela da radioagencia

pg_restore -Fc -U drupal -h 127.0.0.1 -t drupal_webform -d drupal drupal_webform

Observe o formato do DUMP, ele precisa ser tipo C ou tipo F para que o pg_restore consiga recuperar, se for plain text vai dar erro.

dicas soltas

alterando senha do usuario

postgres=#alter user fernando with password 'senha';

tamanho de uma base

dados sem arrendodamento, tamanho do bancos apenas

postgres=# SELECT pg_database_size('geekdb');

arredondando, tamanho do banco apenas

postgres=# SELECT pg_size_pretty(pg_database_size('geekdb'));

tamanho real do banco no disco arredondando

postgres=# SELECT pg_size_pretty(pg_database_size('somedatabase')) As fulldbsize;

tamanho de uma tabela

postgres=# SELECT pg_size_pretty(pg_total_relation_size('drupal_cache'));

nde

tamanho de todas as tabelas

postgres=# SELECT
  relname as "Table",
  pg_size_pretty(pg_total_relation_size(relid)) As "Size",
  pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
  FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
  

tamanho de todos as tabelas e objetos maiores que 64k

postgres=# SELECT
  relname AS objectname,
  relkind AS objecttype,
  reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
  FROM pg_class
  WHERE relpages >= 8
  ORDER BY relpages DESC;
  
  

tamanho da maior tabela para menor

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

psql

usando o console

listando bancos

postgres=# \l

conectando a um banco

postgres=# \c nome_do_banco

outros

work mem

Quando algum processo pesado é executado, por exemplo a geracao de indices do drupal ou consultas que usam muito ORDER BY, um diretorio pgsqltemp será criado caso o uso da memoria setado na variavel WORKMEM seja execedido.

Nestes caso o aconselhável é aumentar - perante monitoramento - o parametro WORK_MEM na sessão em que está executando a query ou rodando os indices.

SET WORK_MEM TO "64MB"

Esse valor é por consulta, se colocar um valor muito alto a máquina vai sentar bonito, procure um valor entre 64 e 128, monitorando o servidor para verificar o impacto.

Alterar diretório de dados PostgreSQL

Código:

parar o serviço do postgresql: service postgresql-9.3 stop

Edite o script start-up

Código:

root @ host # vi /etc/init.d/postgresql

Altere a variável PGDATA e PGLOG para onde quiser (mudanças em vermelho ) ...

Código:

# Definir padrões para as variáveis ​​de configuração
PGENGINE=/usr/bin
PGPORT=5432
PGDATA=/path/to/pgdata
if [ -f "$PGDATA/PG_VERSION" ] && [ -d "$PGDATA/base/template1" ]
then
        echo "Usando estrutura de diretórios de estilo antigo"
else
        PGDATA=/d2/pgdata
fi
PGLOG=/path/to/pgdata/pgstartup.log

# Override defaults from /etc/sysconfig/pgsql if file is present 
[ -f /etc/sysconfig/pgsql/${NAME} ] && . /etc/sysconfig/pgsql/${NAME}

export PGDATA
export PGPORT

Crie o diretório ... e definir as permissões

Código:

root @ host # mkdir-p /path/ to /pgdata
root @ host # chown postgres: postgres /path/to/pgdata

Inicializar o banco de dados ...

Código:

root @ host # su - postgres-c "initdb-D /path/ to /pgdata"

Agora você pode começar postgres

Código:

root @ host # service postgresql-9.3 start

Agora você pode verificar isso com o ps e você verá (em vermelho ) que a dir pgdata é onde o banco de dados é

Código:

root @ host # ps aux | grep pós
 postgres 11140 0,0 0,1 122556 3372? S 10:41 0:00 / usr / bin / postmaster-p 5432-D / path / to / pgdata
postgres 11142 0,0 0,0 111736 672? S 10:41 0:00 postgres: processo logger                 
postgres 11144 0,0 0,0 122692 1168? S 10:41 0:00 postgres: processo escritor                 
postgres 11145 0,0 0,0 112736 672? S 10:41 0:00 postgres: Estatísticas processo de buffer           
postgres 11146 0,0 0,0 111864 848? S 10:41 0:00 postgres: Status de processo de coletor        
raiz 11186 0,0 0,0 61120 724 pts / 1 S + 10:58 0:00 grep pós

Instalação PostgreSQL 9.3

Existe diversa maneira de fazer a instalação pelo fonte compilando e instalando, baixando o pacote .rpm e usando gerenciador de pacote yum que vai fazer todo trabalho automático. Qual escolher vai depender do cenário para este vamos usar o yum  instalando a versão 9.3

Primeiro vamos atualizar o S.O

#  yum update 

Vamos inserir no repositório o caminho para o pacote postgresql 9.3

# rpm -i http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-redhat93-9.3-1.noarch.rpm

Agora conseguimos instalar através do yum install

# yum install postgresql93-server postgresql93-contrib  

Agora que já instalamos vamos iniciar o banco depois o serviço.

# service postgresql-9.3 initdb 
# service postgresql-9.3 start
# chkconfig postgresql-9.3 on

Precisamos ajustar o servidor para permitir conexões externas na porta "5432"

Primeiro vamos liberar no iptables, editar o arquivo "/etc/sysconfig/iptables"

# vim /etc/sysconfig/iptables

Na linha 5 coloque a regra abaixo:

-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

Apos salvar o arquivo reinicie o serviço do iptables

# service iptables restart

Agora precisa dizer para o Postgre aceitar conexões externa, vamos editar "/var/lib/pgsql/9.3/data/postgresql.conf"

# vim /var/lib/pgsql/9.3/data/postgresql.conf

precisamos tirar o comentário de duas linhas "listen_addresses" e "port" abaixo exemplo como deve ficar:

próximo passo e editar o arquivo "/var/lib/pgsql/9.3/data/pg_hba.conf" na coluna METHOD trocar ident por md5 e inserir faixa de rede ou ip que vai ter acesso ao servidor, abaixo exemplo como deve ficar:

reiniciar o serviço para as configurações aplicar

#  service postgresql-9.3 restart

Vamos definir senha do usuario postgres

# su postgres

bash-4.1$  psql postgres

postgres=# alter user postgres password 'sua_senha';

Caso queira acessar o Postgre de uma maquina Windows com interface grafica e só instalar o programa pgAdmin link para download http://www.postgresql.org/ftp/pgadmin3/release/v1.18.1/win32/ depois de instalar vai encontrar essa tela e só colocar os parâmetros de conexão.

Pronto Concluído.

ERRORLOG

Hoje estou postando apenas uma dica. Como vocês sabem o ERRORLOG do SQL Server vai crescendo a medida que as informações são inseridas e sendo assim o arquivo de log somente é reiniciado quando a instancia do SQL Server é reiniciada, então como é a ordem natural das coisas o serviço do SQL Server dificilmente é reiniciado fazendo com que o ERRORLOG cresça e cresça cada vez mais impossibilitando muitas vezes a leitura do mesmo.

O SQL Server então possui uma stored procedure que faz essa reclicagem do ERRORLOG sem necessitar reiniciar a instancia. Segue o comando abaixo.

use master
go
xp_readerrorlog
go

agora vamos executar o comando para reclicar o log.

use master
go
exec sp_cycle_errorlog
go
exec xp_readerrorlog

Conforme a imagem abaixo o ERRORLOG foi recriado.

Como podemos ver na imagem abaixo tinhamos o arquivo ERRORLOG de 14KB  que apos executarmos a stored procedure, foi criado um novo arquivo ERRORLOG e o antigo foi renomeado para ERRORLOG.1.