Grant *.* to usuário……

Ontem lá pelas tantas da madruga, dando um rolé pelos sites me deparei com o site do depesz Fazia um tempinho que eu não acessava o site do cara, meu irmão o cara é #”!$%% !!! Pra quem conhece o blog do cara sabe que ele manda muito bem !!!!

Bom…. já rasguei a seda…….. Um dos artigos ,o cara comentava da dificuldades dos novos dbas postgresql em dar um GRANT/REVOKE em todas as tabelas do banco de dados.

A Saga do Guerreio Paulo César (PC Boy) !!

Me lembrei do cabra aqui do trampo, grande amigo meu que trabalha aqui detentor da seguinte fala “Deixa eu chegar chegando pra não ficar ficando , aproveitar o embalo e passar batido” :).

Um dos bancos aqui tem umas 400 tabelas e ele ia uma por uma, em uma luta infernal.

Grant select,insert,update,delete on tabela1 to usuario;
Grant select,insert,update,delete on tabela2 to usuario;
……
Grant select,insert,update,delete on tabela400 to usuario;

hehehheheehehee engraçado! Mas eu já passei por isso e pode até parecer uma coisa simples ou idiota, mas….

AAAAAAA mais o pgadmin faz isso, automaticamente!!!!

É mesmo!!! Faz, mas e se você por necessidade só tem um “SIMPLES” shell hehehe?? KD o seu pgadmin? Tipo o servidor de Banco de dados não tem ambiente gráfico e tal…

No site do DESPEZ ele tinha bolado uma stored procedure que dava um grant all *.*, muito legal, vale a pena da uma olhada!!!
Eu sempre tinha resolvido esse tipo de problema fazendo um shell scriptzinho, mas como eu nunca salvo as coisas tinha que fazer toda vez que era necessário!!

PG_grantall


Eu não vou negar, shell script RuleZ!!! Gosto do bash, me sinto acolhido naquele lugar escuro e quentinho hehehhee. Tava meio enferrujadão de shell script e resolvi escrever um script para automatizar esses grants , na verdade não foi nem muito pra ajudar o jovem PC, foi pra relembrar algumas coisas mesmo hehehhe, coloquei o nome dele de pg_grantall.

# pg_grantall -h
pg_grantall [OPTIONS]

OPTIONS
-T GRANT on tables on database
-S GRANT on sequences on database
-U login user
-H Hostname
-R Role to grant

Por exemplo: Quero dar os todos os privilegios nas tabelas do banco de dados banco1, para o jovem paulo.

pg_grantall -T -U postgres -H localhost -R paulo banco1

Por exemplo: Quero dar os todos os privilegios nas sequences do banco de dados banco1, para o jovem paulo novamente.

pg_grantall -S -U postgres -H localhost -R paulo banco1

Por exemplo: Quero dar os todos os privilegios nas sequences e também nas tabelas do banco de dados banco1, para o jovem paulo

pg_grantall -ST -U postgres -H localhost -R paulo banco1

Pra quem quizer da uma olhada, sinta-se a vontade, inclusive para adaptá-lo.
Como sempre ando meio cansado, e reconheço que o código não está essa coca-cola toda, poderia estar melhor mas ta aí.

pg_grantall


#!/bin/bash   

MENSAGEM=" $(basename "$0") [OPTIONS]
	OPTIONS:

	-T 	Grant tables in  database
	-S      Grand sequences in database
	-U      login user
	-H      hostname 
	-R      role to grant. "

while getopts ":TSU:H:hR:" opcao 
do
	case $opcao  in 
		T) TABLES="r";;
		S) SEQUENCES="S";;
		U) USERDB=$(echo "$OPTARG");;
		H) HOST=$(echo "$OPTARG");;
		R) ROLE=$(echo "$OPTARG");;
		h) echo "$MENSAGEM";exit;; 
		\?) echo "Invalid Option!!";exit;;
		: ) echo "Argument missing!!;exit";;
		
			
	esac

done

shift $((OPTIND -1))

if [   -z "$TABLES" -a  -z "$SEQUENCES" ]  
then
	echo "Missing option T  or S"
	exit
fi

if [ -z "$USERDB" ] 
then
	USERDB="postgres"

fi

if [ -z "$HOST" ]
then

	HOST="localhost"
fi

if [ -z "$ROLE" ] 
then
	echo "what role should i give the privilleges?"
	exit;
fi 

if [ -z "$*" ]
then
       echo "What database should i connect ??"
       exit;	
       
fi

echo "Password for user $USERDB: "
read -s PASSWD 

PGPASSWORD=$(echo "$PASSWD")
export  PGPASSWORD


psql -U $USERDB -h $HOST -t  -c "select relname from pg_class where relkind
 in ('$TABLES','$SEQUENCES') and relname !~ '^(pg_|sql)';"   $*  | grep -v "^$" | 
 while read linha 
do 
	psql -U $USERDB -h $HOST -c "grant all on $linha to $ROLE;"   $*
done

Eu coloquei esse script no meu /usr/local/pgsql/bin e ficou legalzin!!

Começando com o PLPROXY….

Esse post é baseado no próprio tutorial do PL/PROXY e um HOW TO do Kristo Kaiv.

Depois de muito tempo, realmente sentei e prometi pra mim mesmo que iria conhecer o plproxy. Ontem resolvi dedicar algum tempo a ele, e acho que foi um tempo muito bem aproveitado. Bem vamos lá!
A primeira vista o tutorial do próprio projeto me pareceu bastante fraco, e demorei um pouco para absorver as particularidades do projeto.

Para que serve o particionamento?

Particionamento possibilita balancear a carga e os dados em múltiplos bancos de dados.

Entendi mas não to vizualizando…

Imagine o seguinte cenário:
Você tem uma tabela de login, com milhões de registros, e diariamente milhões de usuário se conectam no seu banco de dados. Então você tem uma carga muito alta em cima desse servidor e consequentemente muitos dados em uma única tabela.

Beleza mas o que eu poderia fazer então??


Poderíamos particionar essa tabela de login em vários servidores baseados em um determinado critério , no nosso exemplo poderia ser a primeira letra do login usuário. Exemplo: usuários de A-J servidor1, usuário de K-Z servidor2. Com isso diminuiríamos o LOAD em cima de um único servidor e promoveríamos o particionamento dos dados entre os servidores.

Mas como é feito esse particionamento Hein Hein Hein?


O caso mais comum de particionamento é realizado em cima de hash na chave primário, no nosso caso o login do usuário. Usando uma função hash, a carga vai sendo balanceada entre as partições.

Exemplo: Suponhamos que temos 2 partições, ou seja dois servidores e a nossa tabelinha de login está particionada entre os servidores. Usuários de A-J no servidor1 e usuários de K-Z no servidor2.
Então é aplicada uma função hashtext(’login’) que determina a partição que o login do usuário estará:

Sugiro que o leitor entre no banco de dados e faça os seguintes testes:
select @(hashtext(’joao’)%2 = 1
selet @(hashtext(’cosme’)%2 = 0
select @(hashtext(’lindao’)%2 = 1

O número 0 corresponde a primeira partição, e o 1 a segunda.
PS: eu sei que você digitou lindão heehe 🙂
Bem, o que ta acontecendo, através de um hash aplicado em um login eu determino em que partição se encontra o dado!!!! ok!!! mais se forem 4 partições??

select @(hashtext(’joao’)%4 = 1
select @(hashtext(’cosme’)%4 = 0
selet @(hashtext(’junior’)%4 = 3
select @(hashtext(’lindao’)%4 = 2

Isso tudo é só pra explicar como vai ser particionado e balanceado os dados e as cargas nos servidores, pois na configuração isso fica transparente.

O que é um PROXY?


Bem, eu costumo dizer que um proxy faz o serviço sujo pra vocÊ!!!!! Você se conecta em um servidor que vai servir como proxy de banco de dados e esse server se conecta nas partições.

Como funciona PLPROXY?

O plproxy é uma linguagem criada dentro de um banco de dados postgresql (assim como outras linguagens como plpgsql…. ) Possibilitando um acesso remoto em outros databases assim como o dblink. Segundo Kaiv, o plproxy é um dblink ANABOLIZADO! (hehehe não foi que quem disse isso) mas concordei.

A primeira coisa quando eu vi a definição acima, sobre plproxy, que é uma linguagem lálálá lalá lálaá que possibilita que eu me conecte em outros databases remotos ,não vi nenhuma vantagem em relação ao dblink, e questionei, mas depois eu vi claramente 🙂

Instalando o plproxy

Você pode baixar os fontes na página do projeto :http://pgfoundry.org/projects/plproxy/:
Como eu estou utilizando o debian 4.0, eu já tenho instalado os pacotes de compilação (make,gcc…..)
caso você não tenha utilize:
apt-get install build-essentials:

Antes de iniciar coloque no PATH o caminho para o pg_config

PATH:=$PATH:/usr/local/pgsql/bin

Baixado o fonte, tar -xzvf plproxy…. , entre no diretório make e depois make install.
Se por acaso ocorreram erros na compilação reclamando do flex e do bison,
apt-get install flex bison
(Essa vai pro marins hehheh)

Se tudo ocorrer certinho vamos ter o arquivo /usr/loca/pgsql/share/contrib/plproxy.sql

Criando o ambiente!!!


Vamos fazer o seguinte: iremos criar 4 databases localmente:

create database bdproxy;
create database bd1;
create database bd2;
create database bd3;

Criaremos uma tabela “usuarios” que conterá o login e o mail de cada usuário do nosso sistema. Essa tabela deve ser criada no bd1, bd2, bd3.

create table usuarios(login text,mail text);

O cliente se conecta no bdproxy, e ele particiona os dados e faz o balanceamento nos outros servidores.

Criado todos os bancos de dados e a tabela usuarios em cada um deles, vamos dar suporte somente no bdproxy a linguagem plproxy:
psql -U postgres -h servidor bdproxy < /usr/local/pgsql/share/contrib/plproxy.sql

CREATE FUNCTION
CREATE LANGUAGE

Outro pré-requisito é a criação do eschema plproxy no banco bdproxy;
conectado no banco bdproxy digite:

create schema plproxy;

Criando o primeiro CLUSTER

Existem 3 funções que devem ser criadas no banco de dados que servirá como bd_proxy, no caso o bdproxy:

  • plproxy.get_cluster_version(cluster_name)

Essa função é chamada em cada requisição, e deve retornar o número da versão da configuração corrente para um cluster em particular. Se a versão retornada por essa função for maior do que a versão cacheada pelo plproxy , então a configuração e as informações sobre as partições irão sofrer um reload na configuração.

  • plproxy.get_cluster_partitions(cluster_name)

Essa função é chamada quando uma nova configuração de partição sofre um reload. Essa função deve retornar as strings de conexão para as partições no cluster. As strings de conexão devem ser retornadas na ordem correta. O número total de strings de conexão que devem ser retornadas devem ser potência de 2.

Se a string string “user=” não aparecer na string de conexão, entao a string user = CURRENT_USER irá ser adicionada na string de conexão pelo plproxy. Então o usuário que se conectou no servidor de proxy irá ser o mesmo a ser usados em outros databases. Plproxy não reconhece nenhum tipo de password, os databases que serão particionadas devem aceitar a conexão do proxy como “TRUST”, se não for requisitado nenhum password. Se a string de conexão contém explicitamente um username, então um password pode ser setado na string de conexão.

A melhor maneira de explicitar os password é adicionando eles no arquivo.pgpass no diretório home do usuário Postgres.

  • plproxy.get_cluster_config(cluster)

Deve retornar parametros de configuração como keys – valores em pares. Todas os parametros são opcionais.

Não se esqueçam , essas 3 (plproxy.get_cluster_version, plproxy.get_cluster_partitions, plproxy.get_cluster_config) funções devem ficar no banco que servirá como proxy: bdproxy

então abra uma conexão no bdproxy e criaremos essas 3 funções nele:

CREATE OR REPLACE FUNCTION plproxy.get_cluster_version(cluster_name text)
RETURNS int4 AS $$ 

BEGIN 

      IF cluster_name = 'usercluster' THEN 

           RETURN 1;

       END IF; 

    RAISE EXCEPTION 'Cluster Desconhecido';

END;

$$ LANGUAGE plpgsql; 

CREATE OR REPLACE FUNCTION plproxy.get_cluster_partitions(cluster_name text)
RETURNS SETOF text AS 

BEGIN

 IF cluster_name = 'usercluster' THEN
       RETURN NEXT 'dbname=bd1 host=127.0.0.1'; 

       RETURN NEXT 'dbname=bd2 host=127.0.0.1'; 

       RETURN NEXT 'dbname=bd3 host=127.0.0.1';

       RETURN NEXT 'dbname=bd4 host=127.0.0.1'; 

       RETURN; 

END IF;

RAISE EXCEPTION 'Cluster Desconhecido'; 

END;

$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION plproxy.get_cluster_config (cluster_name text,
out key text, out val text)
RETURNS SETOF record AS $$
BEGIN
    RETURN;
END;
$$ LANGUAGE plpgsql;

Essas são as 3 funções básicas que devem estar no banco que servirá como proxy.

Eu quero é inserir dados!!!!!

Calma jovem!!! Eu sempre fico ansioso quando começo a seguir um how to ou tutorial ( principalmente quando o cara gasta umas 2 horas e a parada não funfa), então dá uma levantada da cadeira, toma um café e VOLTA, para não errarmos nada!!!

Queremos inserir dados nos banco de dados, e logicamente isso acontecerá mediante a utilização do bdproxy, ou seja invocaremos uma função de inserção no bdproxy e ele vai fazer o balanceamento nos databases particionados.

Então vamos criar a função de insert no bdproxy.

CREATE OR REPLACE FUNCTION insert_user(username text, mail text)
RETURNS integer AS $$
     CLUSTER 'usercluster';
     RUN ON hashtext(username);
$$ LANGUAGE plproxy;

É através dessa função que os dados serão inseridos nos bancos de dados. Explicando um detalhe:

CLUSTER – é o nome do cluster que nós setamos lá no plproxy.get_cluster_version

RUN ON hashtext (username) – Agora sim…… O particionamento acontece aqui, lembra da função hash lá em cima???? ou seja baseado no username ocorre obtemos um balanceamento nos servidores :), Esse parametro é muito importante.

Agora em cada um dos servidores, bd1. bd2, bd3 , bd4

crie a seguinte função:

CREATE OR REPLACE FUNCTION insert_user(username text, mail text)
RETURNS integer AS $$
     INSERT INTO usuarios VALUES ($1,$2);
     SELECT 1;
$$ LANGUAGE SQL;


Agora vamos inserir os dados efetivamente…..

Elaborei esse shell script para automatizar a inserção, você pode inserir diretamente no bdproxy usando seu cliente de preferência.

Digite isso no shell:
for i in $(seq 1 50000)
do
psql -U postgres -h localhost -c ” select insert_user(‘usuario$i’,’usuario$i@mail.com.br’);” bdproxy
done

Então inserimos 50000 registros. Após inserirmos os registros vamos contar o número de tuplas da tabela usuarios em cada um dos databases;

bd1 —> select count(*) from usuarios = 12455
bd2 —> select count(*) from usuarios = 12528
bd3 —> select count(*) from usuarios = 12400
bd4 —> select count(*) from usuarios = 12617

Somando um total de 50000 🙂

KD meus dados??? BABY come back to meeee

Beleza, inserimos os dados e podemos observar que eles estão particionados. E agora como recuperaríamos esses dados? Bem, da mesma forma que o bdproxy particiona os dados utilizando a função hash , baseando-se no mesmo hash ele sabe em quais partições estão os dados.

Então no bdproxy vamos escrever a seguinte função:

create or replace function get_user_mail (username text) returns text as $$

        CLUSTER 'usercluster';

        RUN ON hastext(username);

        select mail from usuarios where login = username;

$$ language plproxy;


E para finalizar, (porque eu já estou cansado pra caramba…) no banco bdproxy :

select get_user_mail(‘usuario40’);

Considerações finais

Ainda faltam alguns detalhes sobre a ferramenta nesse post, mas para o começo acho que está ótimo!!! Prometo que daqui a 1/2 dias eu posto o resto!!

Uma boa lida no tutorial do plproxy e do how to do kristo são indispensáveis, acho que com esse post já possibilita um entendimento muito bom !!!

A ferramente me paraceu muito interessante, principalmente quando integramos com o pgbouncer e o londiste ou slony, mas esse post fica pra próxima.

Como eu disse no início, eu estou iniciando os estudos em cima do plproxy, ainda estou imaginando uma arquitetura bem legal pra implementar com mais maturidade e realizar alguns testes.

Automatizando deleção de databases…

Hoje surgiu uma situação um pouco que anormal. O banco de desenvolvimento estava gigantesco com vários databases que sinceramente mais pareciam um depósitco de lixo em vez de repositório de dados. O mais interessante é que todos os databases que deveriam ser apagados tinham uma maneira de serem identificados pelo nome: começavam com bd e terminavam com old.

ex: bd_sistema1_old

Então vamos lá:

PS: Logicamente eu utilizo GNU/linux

psql -U postgres -h servidor -t -c “select datname from pg_database where datname ~ ‘^bd.*old$’ ” postgres | while read databases

do

psql -U postgres -h servidor -c “drop database $databases;” postgres

done