Ir para o conteúdo
ou

Software livre Brasil

Tela cheia
 Feed RSS

SAVEPOINT

27 de Maio de 2009, 0:00 , por Software Livre Brasil - | Ninguém está seguindo este artigo ainda.

Para minha irmã caçula

23 de Maio de 2018, 6:21, por Savepoint - 0sem comentários ainda

Há cerca de 25 anos atrás ocorreu um fato dramático em minha vida que poucas pessoas conhecem. Aos 14 anos de idade minha irmã sofreu um acidente de automóvel fatal da BR 116 em direção à Curitiba. Ela dormia no banco de trás e soltou o cinto de segurança quando o carro capotou e ela morreu na hora. Foi algo chocante que abalou minha família de forma irreversível.  No dia em que ela morreu eu não chorei. Eu não me desesperei. Eu comecei a arrumar a casa pois sabia que iriam chegar muitas visitas na casa onde eu morava na época. É claro que eu amava a minha irmã. O fato é que naquele momento eu não desmontei. Eu permaneci firme e ajudei quem eu podia ajudar. Talvez eu esteja tendo um retrato equivocado de mim mesmo, já faz muito tempo e pode ser que outras pessoas tenham uma percepção diferente da minha reação naqueles dias.

No enterro, parentes e amigos da família encheram o cemitério de gente, todos muito tristes e chorando. Me abraçavam energicamente chorando muito, dizendo que eu deveria ser forte e tal. E eu achava tudo muito…  desconfortável. Eu já estava sendo forte. Eu não estava aos prantos nem chamando a atenção. Estava lá discretamente. Segurei a alça do caixão e ajudei a levar o corpo da minha irmã para o seu local de repouso. Depois houve a missa de sétimo dia. A missa foi dentro da escola onde ela estudava, havia uma capela lá que estava abarrotada de alunos em pleno início de janeiro. Foi um momento mais brando, com palavras mais serenas. Não havia um caixão fechado com um rosto desfigurado dentro. As palavras do padre foram gentis e delicadas. Meus pais já não estavam com o rosto tão inchado e com escoriações do acidente. Foi um bom momento, com pessoas que fizeram parte da vida da minha irmã lá. Pessoas com quem ela foi muito feliz nos últimos anos. Ela adorava aquela escola.

No ano novo eu fui passar a virada do ano com amigos na praia. Foi a primeira vez na vida que eu bebi bebida alcoólica na vida. Foi a primeira vez que eu fumei um cigarro. Não passei mal nem fiz nada de extraordinário. Estava com quase 18 anos e estava me abrindo para novas experiências. Para mim a vida estava indo muito bem, apesar do ocorrido. Dias depois, ao ir visitar um grande amigo, dei a notícia de que a minha irmã havia falecido. Naquele momento todos na casa pararam o que estavam fazendo para falar comigo. Ficaram chocados com a naturalidade que eu narrei os fatos. Como se eu tivesse a obrigação de estar chorando e estar terrivelmente abalado.

No final de janeiro eu me encontrei com os colegas do Grêmio Estudantil da escola e fomos preparar a recepção dos calouros. Eu havia começado a fumar e beber. As pessoas achavam que o fato de ter perdido a minha irmã havia me abalado profundamente e eu estava me entregando à vícios e coisa e tal. É verdade que eu só parei de fumar 10 anos depois… mas depois de um porre de whisky no meu aniversário de 18 anos, eu nunca mais passei da conta com bebida. Nunca. O que aconteceu é que meses antes da minha irmã falecer, eu estava na melhor época da minha vida até então. Havia encontrado um grupo de amigos incrível. E até hoje, são esses os meus melhores amigos. Estava feliz, acabara de fundar o grêmio da escola, era reconhecido pela minha atuação, tive minha primeira namorada, haviam debates acalorados, uma sensação de que estávamos mudando o mundo. E na verdade, estávamos mudando a nós mesmos, marcas que ficaram para o resto das nossas vidas. Tempo bom, que deixou muitas saudades. Já escrevi um pouco sobre isso aqui e aqui.

Mas o fato é que minha irmã se foi. Seguimos nossa vidas, estudamos, trabalhamos, nos apaixonamos, nos perdemos e nos encontramos. Não sei dizer onde minha irmã está metafisicamente. Se está no céu, se reencarnou, se teve algum destino após a vida. Ouvi muitas pessoas dizerem que quando mortes trágicas assim acontecem, há uma explicação espiritual para tudo isso. Eu sei que havia uma curva perigosa na BR 116, e nessa curva um caminhão derrubou areia na pista, e nessa areia um carro derrapou. O guard rail que estava ali estava solto, uma vez que haviam muitos acidentes naquele ponto e os responsáveis pela manutenção não se deram ao trabalho de prende-lo novamente. Sei que depois do guard rail havia um barranco e o carro capotou nele. E sei que depois da minha irmã virar estatística eu brigo sempre para as pessoas usarem o cinto de segurança mesmo no banco de trás.

Sei exatamente onde a minha irmã está nesse exato momento. Está na minha memória, na lembrança de momentos inesquecíveis da minha infância. De uma irmã que me entendia, me fazia companhia e que eu amava muito. Está na lembrança de toda a família, dos amigos e das pessoas que a conheceram. Está nas histórias de viagens em família, festas de aniversário, nas frases de efeito incríveis que ela fazia, no sorriso e no gosto por ouvir Elis Regina. Não preciso de explicações para saber que ela está comigo para sempre. Eu já me peguei pensando em como seria a vida dela se não houvesse areia na pista, se o guard rail estivesse preso, etc. Se ela teria se casado, se teria filhos, se teria uma carreira profissional brilhante, se moraria aqui por perto… Não sei dizer. Acho que teria uma vida incrível. Mas de fato, ela teve uma vida incrível. E por algum motivo, eu me pego lembrando dela com mais frequência ultimamente. Tenho que confessar, eu realmente sindo falta da minha irmã caçula. Hoje o caçula sou eu, mas tenho que dizer: minha irmã querida, o seu lugar estará sempre aqui comigo.


Médicos Sem Fronteiras

8 de Maio de 2018, 15:55, por Savepoint - 0sem comentários ainda

Eu sempre acho que a gente deve agradecer ao universo quando boas coisas acontecem com a gente. Independente de religião ou credo, acho que a gratidão e fazer o bem ao próximo deveriam nos unir enquanto seres humanos. Então de tempos em tempos eu faço uma doação para alguma causa que eu admire, com pessoas que trabalham e lutam por um mundo melhor. Em geral eu costumo fazer doações pare entidades ligadas informática: Wikipedia, EFF, Gnome, PGXN e por aí vai. Este ano a Timbira está indo muito bem com novos clientes e antigos clientes voltando. Estou muito feliz com o nosso progresso e com o nosso trabalho.

Eu sempre tive muita desconfiança de projetos assistenciais. Já trabalhei junto de algumas e conheço escândalos de corrupção absurdos em várias. Mas eu lembro uma vez que meu pai me contou sobre quando decidiu ser médico, ele queria trabalhar para o Médico Sem Fronteiras. Que aqueles médicos eram os grandes heróis da profissão. Pessoas que honram o Juramento de Hipócrates e que fazer da sua profissão um ato de fé na humanidade. Mas… ele virou pai e teve que abandonar essa ideia para se dedicar à família. Claro que ele encontrou outras formas como médico de ajudar as pessoas, como o fez no combate à desnutrição infantil, um trabalho de toda uma vida.

Hoje estou fazendo uma doação para o Médico Sem Fronteiras, que são um exemplo para mim de pessoas cujo trabalho deve ser admirado e apoiado. Espero um dia como profissional poder levar a mesma paixão e compromisso para o meu trabalho. Se mais pessoas pensarem assim, certamente teremos um mundo melhor.

Hold On!


Desabilitando todos os gatilhos do PostgreSQL

20 de Abril de 2018, 9:38, por Savepoint - 0sem comentários ainda

Uma dica rápida vindo de uma pergunta no Telegram hoje de manhã: Como desativar todos os gatilhos de todas as tabelas de uma vez só?

A princípio pensei em consultar o INFORMATION SCHEMA para isso com algo como:

SELECT 'ALTER TABLE ' || event_object_schema || '.' || event_object_table || ' DISABLE TRIGGER ' || trigger_name || ';' 
FROM information_schema.triggers
GROUP BY trigger_name, event_object_schema, event_object_table;

Funciona, mas você não consegue filtrar apenas os gatilhos que estão habilitados. Então achei melhor partir para o catálogo do postgres logo de uma vez:

 

SELECT 'ALTER TABLE ' || n.nspname || '.' || c.relname || ' DISABLE TRIGGER ' || t.tgname || ';' 
FROM
    pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    JOIN pg_trigger t ON c.oid = t.tgrelid
WHERE 
    tgenabled = 'O' AND
    NOT tgisinternal;

Note o cuidado em filtrar aqui o tipo de gatilho. Depois de desabilitar tudo, você provavelmente vai querer habilitar tudo novamente:

SELECT 'ALTER TABLE ' || n.nspname || '.' ||c.relname || ' ENABLE TRIGGER ' || t.tgname || ';' 
FROM
    pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    JOIN pg_trigger t ON c.oid = t.tgrelid
WHERE 
    tgenabled = 'D' AND
    NOT tgisinternal;

Espero ter ajudado!


Proteja o seu banco de dados PostgreSQL

19 de Abril de 2018, 23:39, por Savepoint - 0sem comentários ainda

Segurança é um tema que só quem tem experiência se preocupa. Neste caso, experiência significa que você já foi invadido um dia. E a pergunta nunca é se você será invadido, mas quando. Um cliente me pediu um resumo rápido das medidas de segurança para proteger o seu PostgreSQL. Comecei a enumerar e achei melhor escrever aqui, uma vez que é um assunto que está tirando o sono de muita gente.

RTFM

Saiba o que está fazendo, leia a documentação oficial e não apenas em blogs e vídeos no YouTube! Veja se a documentação que você está lendo se refere a versão correta do software que você está utilizando.

Proteja o seu servidor

Instalação do servidor: menos é mais

Instale somente o necessário. Crie um servidor dedicado apenas com o essencial. Tudo que não for absolutamente necessário deve ser removido. Isso inclui serviços de compartilhamento de arquivos, impressão e até a interface gráfica… mesmo que o seu servidor seja em Windows. Além de economizar um pouco de espaço em disco, economizar CPU e memória você abre um número menor de portas no seu servidor e tem menos softwares com possíveis falhas de segurança instalados.

Mantenha as atualizações de segurança em dia

Prefira sempre instalar o PostgreSQL empacotado ao invés de compilado. Se estiver utilizando uma distribuição Linux, use o repositório do PGDG. Se for uma distribuição que usa pacotes RPM, utilize o repositório YUM do PGDG, se for uma distribuição que utiliza pacotes DEB utilize o repositório APT do PGDG. Feito isso, faça atualizações de segurança do seu sistema operacional com frequência. Muitas invasões exploram falhas de segurança conhecidas e já corrigidas. Mas você precisa atualizar o seu Sistema Operacional (incluindo o PostgreSQL), senão o trabalho dos desenvolvedores para corrigir as falhas de segurança não adiantam nada.

Limite o acesso físico

Cuide do acesso físico ao servidor. Onde seu servidor fica? Lembre-se que qualquer pessoa que tenha acesso físico ao servidor pode para começar tirar ele da tomada… e pode até ser sem querer. Com um pouco de conhecimento a pessoa pode dar um boot num pen drive com um Linux qualquer e ter acesso TOTAL ao seu servidor e fazer o que ele quiser. Portanto, guarde seu servidor em um local seguro. E por mais que você tenha gastado muito dinheiro para compra-lo, não coloque ele numa vitrine para todos contemplarem ele. Servidores devem ficar numa sala escondida onde apenas quem precisa sabe onde fica e pode entrar lá. Trabalhei por anos na Itautec ao lado do CTO do Itaú. Ia jantar lá toda semana e nunca soube em que andar ficam os servidores.

Limite o acesso privilegiado remoto

Algumas pessoas provavelmente terão acesso remoto ao servidor. A primeira coisa a fazer é limitar o número de pessoas que vão ter acesso. Quem realmente precisa ter acesso à ele? A segunda é criar usuários únicos para cada uma delas no SO e dar uma senha diferente para cada uma delas. Rastreie a conexão das pessoas no servidor. Se cada um tiver um usuário diferente, você saberá quem se conectou quando. Não confie apenas em senhas para o acesso. Exija que os usuários tenham certificados para poder acessar o servidor.

Utilize conexões encriptadas com SSL

Se toda a informação da sua conexão trafegar pela rede pública sem encriptação…. toda a sua preocupação com segurança vai por água a baixo. Então se os seus dados não trafegam numa rede segura e isolada, configure no servidor e clientes para minimizar a chance ter seus dados roubados inadvertidamente, inclusive senhas. Veja na documentação como fazer isso e teste com calma antes de ativar conexões hostssl no pg_hba.conf

Proteja o IP do servidor

Seu servidor jamais deve ter um IP válido na Internet. Isso é um erro imperdoável. Para chegar ao servidor você deve primeiro acessar um servidor intermediário, através de um Firewall e ou uma VPN. Jamais deixe seu servidor exposto numa rede pública. O resultado é fatal.

Limite o acesso das aplicações

O PostgreSQL possui um mecanismo sofisticado e simples para filtrar as conexões antes de chegarem ao seu banco de dados. Ele se chama pg_hba.conf e possui um capítulo específico na documentação só sobre ele. LEIA. Se você utiliza alguns poucos servidores de aplicação, então você deve limitar o acesso a estes IPs específicos individualmente. Se você tem uma aplicação client/server, limite o acesso a uma faixa de IPs. Jamais libere tudo. Limite também qual usuário ou grupo pode utilizar qual base. Você pode utilizar também as opções sameuser se tiver uma base para cada cliente por exemplo. Se você tem uma aplicação client/server, pode optar também por utilizar uma autenticação via LDAP.

Cuide das suas senhas

Cuidar de senhas é uma baita dor de cabeça. O PostgreSQL oferece algumas ferramentas para te ajudar:

  • Senhas fáceis são um problema contra ataques de força bruta. O PostgreSQL possui o módulo do contrib chamado auth_delay para te ajudar a criar um atraso na autenticação, dificultando bastante ataques de força bruta. Não é algo muito elegante mas pode te ajudar se perceber que está sofrendo ataques desse tipo.
  • Outro módulo mais elegante é o uso do módulo passwordcheck. Este faz uma checagem automática toda vez que você criar um usuário com senha ou tentar alterar a senha. Se a senha for considerada fraca o comando dá erro e rejeita a senha. Ele tem um algorítimo próprio para definir as regras sobre o que é uma senha ruim. Você pode alterar o código fonte para mudar as regras se quiser.
  • Utilize outro método de autenticação que não MD5 ou SHA (o SCRAM-SHA-256 for introduzido no PostgreSQL a partir da versão 10). O PostgreSQL fornece varias alternativas como
    • GSSAPI
    • SSPI
    • Ident / Peer
    • LDAP
    • RADIU
    • Certificado SS

Privilégios: menos é mais

Você deve criar usuários separados para as suas aplicações e limitar o seu acesso:

  • Jamais, em hipótese alguma utilize um superusuário para a aplicação se conectar no banco de dados. Se alguém conseguir acesso ao banco de dados com um superusuário ele vai poder fazer qualquer coisa. E qualquer coisa não é algo nada divertido aqui.
  • Um usuário deve ser o dono dos seus objetos. Você vai utilizar este usuário apenas para o deploy. A aplicação jamais deve utilizar este usuário. O dono (owner) dos objetos pode criar e destruir qualquer objeto no banco de dados, logo, por uma questão de segurança, deve ficar protegido e longe da aplicação.
  • Se o usuário deve utilizar uma senha para entrar na aplicação e ele tiver uma senha no banco de dados, lembre-se que a senha do banco de dados não pode ser a mesma da aplicação. A coisa mais fácil que existe é o seu usuário instalar um client (pode até ser numa planilha via ODBC) e acessar diretamente o seu banco de dados com o usuário e senha dele. Adivinha o que pode acontecer se o seu usuário sair fazendo UPDATE e DELETE diretamente nas tabelas do sistema? Adicione um sufixo e um prefixo para o nome do usuário no banco de dados, faça a mesma coisa com a senha e depois embaralhe ela com um MD5 ou um SHA, depois crie o usuário no banco de dados. Quando for autenticar o usuário refaça a mesma operação todas as vezes. Não é um método muito seguro, mas ajuda a tirar os usuários comuns mal intencionados (como um funcionário comum que quer pedir demissão) de fazer um estrago.
  • Se todos os usuários da aplicação utilizam o mesmo usuário do banco de dados ou um pequeno grupo de usuários no banco de dados, você vai ter que guardar a senha deste usuário em algum lugar na máquina onde está a aplicação. Jamais guarde isso em um arquivo texto puro. Qualquer um que tiver acesso à máquina vai poder abrir este arquivo e fazer a festa no seu banco de dados. Você pode encriptar um arquivo com uma chave guardada dentro da aplicação. Também pode utilizar outros métodos de autenticação sem senha, utilizando certificados.
  • Como os usuários da aplicação no banco de dados não são donos dos objetos, você vai ter que conceder privilégios em cada tabela/função/visão/sequencia, etc individualmente. Antes de mais nada, dê uma olhada na documentação para aprender como isso funciona direito. Ajustar isso é um trabalho chato e tedioso. Ninguém gosta de fazer isso, mas precisa ser feito. E precisa ser feito no ambiente de desenvolvimento e homologação também. É terrível quando você faz um deploy na produção e a aplicação para de funcionar por falta de um simples GRANT. Isso acontece quando os testes foram feitos com um usuário com mais privilégios que o da produção. Tente dar apenas os privilégios extremamente necessários para cada usuário ou grupo de usuários (role). Se você nunca apaga registros de uma tabela, não dê um GRANT de DELETE nela. Fazer este ajuste fino em cada tabela pode levar muito tempo. As pessoas tendem a dar GRANT em tudo e apertar com bastante força o “botão do FODA-SE”! Resista a tentação. Se você tem centenas ou milhares de objetos para dar GRANT e não tem condições de revisar toda a sua aplicação para isso, revise pelo menos o acesso aos objetos mais sensíveis. A regra nesse caso é: “o cofre não pode ser mais caro que o ouro”. Então avalie o custo de acertar todos os privilégios um por um e avalie o custo de alguém apagar todos os dados em uma tabela com dados críticos.
  • Se você tem uma tabela com dados realmente importantes, você não deveria deixar a sua aplicação fazer SELECT, UPDATE, DELETE diretamente. Ao invés disso, crie visões (views) e funções que filtrem o acesso aos dados para você a sua aplicação. Assim você impede um UPDATE sem WHERE por exemplo ou o acesso à todas as linhas de uma tabela que tem restrições de privacidade.
  • Se você armazena dados realmente sensíveis como números de cartão de crédito (tenho medo de quem armazena esse tipo de coisa…) você deve encriptar as informações nestas colunas. O PostgreSQL tem um módulo do contrib chamado pgcrypto que faz isso. Não é algo trivial de se utilizar. Precisa gastar um tempo para entender como fazer isso. Mas é praticamente a única opção se você realmente precisa armazenar dados assim. Nem o seu DBA deve poder ver as informações nessas colunas!
  • Se você tem restrições severas a quais linhas de uma tabela o seu usuário pode acessar você pode utilizar o políticas de acesso em nível de linha. Não é algo tão complexo mas exige um pouco de planejamento para utilizar. A documentação tem alguns exemplos simples para lhe dar uma ideia de como funciona na prática.

Proteja a sua aplicação

O lado da aplicação é um lado frágil da corrente em termos de segurança. Você não pode evitar por exemplo que um servidor web fique exposto à uma rede pública como já fez com o banco de dados. Como está mais vulnerável você deve tomar uma serie de medidas para limitar o estrago o servidor onde a aplicação está seja comprometido. Então além de praticamente adotar os mesmos cuidados que utilizou no seu servidor de banco de dados, o lado da aplicação deve tomar cuidados adicionais.

Trilhas de auditoria

Uma forma importante de auditar quem mexeu no meu queijo e ter trilhas de auditoria em tabelas específicas. Saber quem alterou o que e quando é fácil e existem várias extensões prontas para fazer isso. Ou você também pode criar um gatilho para alimentar uma tabela de auditoria. Claro que você tem que proteger a tabela com os dados de auditoria e não dar GRANT para nenhum usuário dar DELETE ou UPDATE nela. Se o usuário da aplicação for o dono da tabela de auditoria então… melhor nem fazer.

SQL Injection

Este é um mal que afeta aplicações descuidadas, principalmente aquelas expostas na interntet. Existem zilhões de receitas prontas para invadir bancos de dados utilizando SQL Injection. E por incrível que pareça, as pessoas continuam caindo nisso! O assunto é um pouco extenso, mas tenho um artigo escrito especificamente sobre isso aqui. Veja que neste tipo de ataque, você não precisa explorar nenhuma vulnerabilidade do servidor, nem ter qualquer tipo de senha. O único elo frágil aqui é a sua aplicação. Então ou você aprende a escrever aplicações blindadas contra SQL Injection, ou você será invadido. Certeza.

Gere logs

Logs podem lhe ajudar a entender o que está acontecendo quando as coisas dão errado. Você pode sofrer uma invasão, perder dados e ser invadido novamente depois por não ter se protegido adequadamente. A única forma de entender o que aconteceu é gerar log no SO, na aplicação e no banco de dados. Gere logs sempre e aprenda a analisa-los de forma eficiente. Hoje temos uma infinidade de ferramentas para ajudar nisso. JUST DO IT!

Se tudo o mais falhar: backup

Parece besteira, mas muita gente não tem a menor ideia de como fazer um backup direito num banco de dados e tem aplicações enormes rodando em produção sem jamais terem lido a documentação sobre isso. Só um detalhe importante: ter um standby não lhe protege contra perda de dados em caso de invasão. Se o invasor apagar os dados de uma tabela em produção, esta alteração vai ser propagada para o standby. Portanto, você NÃO PODE FICAR SEM BACKUP. E como eu já escrevi e repeti por aqui… Dump não é backup!!!


Sintaxe no PostgreSQL: endereço de rede

3 de Abril de 2018, 18:44, por Savepoint - 0sem comentários ainda

Bom, chega um ponto em que estou falando mais sobre tipos de dados do que sobre sintaxe propriamente dito. O PostgreSQL é um SGDB que nasceu com uma proposta ousada, não apenas substituir o Ingres, mas ter uma arquitetura orientada a objetos e ser muito extensível. Um dos impactos disso é a capacidade de se criar vários tipos de dados diferentes. É realmente simples de fazer isso. Quando vimos o ENUM, vimos que você tem o CREATE TYPE, mas é só a ponta do iceberg: você tem o CREATE OPERATOR, CREATE AGGREGATE, CREATE CAST, CREATE ACCESS METHOD, CREATE COLLATION e por aí vai. Você nem precisa conhecer a linguagem C para fazer muita coisa interessante.

Uma das coisas interessantes que o PostgreSQL faz é criar tipos de dados novos. É quase um vício. Quase todo ano aparece um tipo de dados novo. O capítulo “Data Types” da documentação está dividido em 20 sessões na versão 10 do PostgreSQL. Na versão 7.1 eram apenas 8. Cada sessão apresenta um conjunto de tipos de dados específico. É claro que isso começa a parecer jabuticaba (uma daquelas coisas que só tem no Brasil…). Na verdade é mesmo. São extensões ao padrão SQL. Ninguém mais tem. Mas se considerarmos que a linguagem SQL é fortemente tipada, acho que os demais SGDBs deveriam ser um pouco mais desta forma também. Todos os SGDBs correram para criar uma implementação de XML e JSON nos seus bancos de dados. Mas existem muitas outras situações em que criar um tipo de dados específico para uma situação ajuda muito.

Vamos ver o caso específico de redes aqui. Este é um caso emblemático, pois é bem antigo no PostgreSQL e exemplifica bem como um tipo que poderia ser apenas um VARCHAR, pode ser muito mais que isso. O Postgres possui atualmente 4 tipos que armazenam endereços de rede:

  • inet: Armazena endereços IPv4 ou IPv6 e opcionalmente a subrede ou máscara (ok, é mais comum ouvir em inglês netmask).
  • cidr: Armazena a rede ou netmask da rede;
  • macaddr: Armazena  endereços MAC ou MAC addresses;
  • macaddr8: Semelhandte ao macaddr, mas utilizando o formato EUI-64 com 6 ou 8  bytes.

inet

A sintaxe para fazer a coerção para o tipo correto é a mesma que demonstramos no primeiro episódio da nossa saga. Vejamos o uso do inet:

teste=# SELECT inet '192.168.0.1/24' AS ip;
       ip
----------------
 192.168.0.1/24
(1 row)

teste=# SELECT  '192.168.0.1/24'::inet AS ip;
       ip
----------------
 192.168.0.1/24
(1 row)

teste=# SELECT  CAST('192.168.0.1/24' AS inet) AS ip;
       ip
----------------
 192.168.0.1/24
(1 row)

Cada um dos tipos possui uma sintaxe específica que é aceita e convertida internamente antes de armazenar. A primeira vantagem óbvia é a validação. Se você tentar inserir um valor inválido, o Postgres vai acusar um erro imediatamente, o que não aconteceria se você utilizar um simples VARCHAR:

teste=# SELECT inet '192.168.0.0.0/24' AS ip;
ERROR:  invalid input syntax for type inet: "192.168.0.0.0/24"
LINE 1: SELECT inet '192.168.0.0.0/24' AS ip;
                    ^
teste=# SELECT inet '192.168.0.257/24' AS ip;
ERROR:  invalid input syntax for type inet: "192.168.0.257/24"
LINE 1: SELECT inet '192.168.0.257/24' AS ip;

Claro que você pode criar uma função que faz esse tipo de validação na sua aplicação. Mas aqui você já tem isso pronto. Veja que o formato inet admite vários formatos diferentes de entrada, considerando sempre a máscara também. Vejamos alguns exemplos:

teste=# SELECT inet '192.168.0.1/32' AS ip;
     ip
-------------
 192.168.0.1
(1 row)

teste=# SELECT inet '192.168.0.1/24' AS ip;
       ip
----------------
 192.168.0.1/24
(1 row)

teste=# SELECT inet '192.168.0/32' AS ip;
ERROR:  invalid input syntax for type inet: "192.168.0/32"
LINE 1: SELECT inet '192.168.0/32' AS ip;
                    ^
teste=# SELECT inet '192.168.0/24' AS ip;
       ip
----------------
 192.168.0.0/24
(1 row)

Veja que um com máscara 32 o IP é exibido sem uma máscara uma vez que /32 é a mesma coisa não utilizar uma subrede. Já quando omitimos um dos últimos dígitos e utilizamos uma Classe C por exemplo, o PostgreSQL subentende que o último dígito é zero.

Podemos trabalhar com IPv6 também:

teste=# SELECT inet '2001:0DB8:0000:0000:130F:0000:0000:140B' AS "IPv6";
          IPv6
-------------------------
 2001:db8::130f:0:0:140b
(1 row)

teste=# SELECT inet '2001:DB8:0:0:130F::140B' AS "IPv6";
          IPv6
-------------------------
 2001:db8::130f:0:0:140b
(1 row)

teste=# SELECT inet '2001:DB8::130F:0:0:140B' AS "IPv6";
          IPv6
-------------------------
 2001:db8::130f:0:0:140b
(1 row)

cidr

Aqui temos algo semelhante, mas pensando apenas na máscara:

teste=# SELECT cidr '192/16' AS mask;
 mask
--------------
 192.0.0.0/16
(1 row)
teste=# SELECT cidr '192.168/16' AS mask;
      mask
----------------
 192.168.0.0/16
(1 row)

teste=# SELECT cidr '192.168.0/16' AS mask;
      mask
----------------
 192.168.0.0/16
(1 row)

teste=# SELECT cidr '192.168.0.0/16' AS mask;
      mask
----------------
 192.168.0.0/16
(1 row)

teste=# SELECT cidr '192.168.0.1/16' AS mask;
ERROR:  invalid cidr value: "192.168.0.1/16"
LINE 1: SELECT cidr '192.168.0.1/16' AS mask;
                    ^
DETAIL:  Value has bits set to right of mask.

Se você omitir o tamanho da máscara, o PostgreSQL vai deduzir o tamanho pela faixa de IPs que você fornecer:

teste=# SELECT cidr '10' AS mask;
    mask
------------
 10.0.0.0/8
(1 row)

teste=# SELECT cidr '128' AS mask;
     mask
--------------
 128.0.0.0/16
(1 row)

teste=# SELECT cidr '192' AS mask;
     mask
--------------
 192.0.0.0/24
(1 row)

É claro que a quantidade de dígitos que você fornecer também afeta a interpretação:

teste=# SELECT cidr '10' AS mask;
    mask
------------
 10.0.0.0/8
(1 row)

teste=# SELECT cidr '10.1' AS mask;
    mask
-------------
 10.1.0.0/16
(1 row)

teste=# SELECT cidr '10.1.2' AS mask;
    mask
-------------
 10.1.2.0/24
(1 row)

teste=# SELECT cidr '10.1.2.3' AS mask;
    mask
-------------
 10.1.2.3/32
(1 row)

macaddr

A sintaxe para o macaddr admite o uso dos separadores ‘.’, ‘:’, ‘-‘ ou nenhum separador, dependendo da forma como você agrupar ou não os dígitos:

teste=# SELECT macaddr '01:02:03:04:05:06' AS mac;
        mac
-------------------
 01:02:03:04:05:06
(1 row)

teste=# SELECT macaddr '01-02-03-04-05-06' AS mac;
        mac
-------------------
 01:02:03:04:05:06
(1 row)

teste=# SELECT macaddr '01.02.03.04.05.06' AS mac;
ERROR:  invalid input syntax for type macaddr: "01.02.03.04.05.06"
LINE 1: SELECT macaddr '01.02.03.04.05.06' AS mac;
                       ^
teste=# SELECT macaddr '0102.0304.0506' AS mac;
        mac
-------------------
 01:02:03:04:05:06
(1 row)

teste=# SELECT macaddr '0102-0304-0506' AS mac;
        mac
-------------------
 01:02:03:04:05:06
(1 row)

teste=# SELECT macaddr '0102:0304:0506' AS mac;
ERROR:  invalid input syntax for type macaddr: "0102:0304:0506"
LINE 1: SELECT macaddr '0102:0304:0506' AS mac;
                       ^
teste=# SELECT macaddr '010203:040506' AS mac;
        mac
-------------------
 01:02:03:04:05:06
(1 row)

teste=# SELECT macaddr '010203-040506' AS mac;
        mac
-------------------
 01:02:03:04:05:06
(1 row)

teste=# SELECT macaddr '010203.040506' AS mac;
ERROR:  invalid input syntax for type macaddr: "010203.040506"
LINE 1: SELECT macaddr '010203.040506' AS mac;
                       ^
teste=# SELECT macaddr '010203040506' AS mac;
        mac
-------------------
 01:02:03:04:05:06
(1 row)

Apesar de aceitar vários formatos, o padrão IEE especifica o uso do segundo exemplo como correto.

macaddr8

Da mesma forma, o MAC com 8 bytes ao invés de 6 pode ser utilizado em diferentes formatos:

teste=# SELECT macaddr8 '01:02:03:04:05:06' AS mac;
           mac
-------------------------
 01:02:03:ff:fe:04:05:06
(1 row)

teste=# SELECT macaddr8 '01:02:03:04:05:06:07:08' AS mac;
           mac
-------------------------
 01:02:03:04:05:06:07:08
(1 row)

teste=# SELECT macaddr8 '01-02-03-04-05-06-07-08' AS mac;
           mac
-------------------------
 01:02:03:04:05:06:07:08
(1 row)

teste=# SELECT macaddr8 '0102.0304.0506.0708' AS mac;
           mac
-------------------------
 01:02:03:04:05:06:07:08
(1 row)

teste=# SELECT macaddr8 '0102030405060708' AS mac;
           mac
-------------------------
 01:02:03:04:05:06:07:08
(1 row)

Note que se você inserir um MAC de 6 bytes num macaddr8 ele vai preencher 2 bytes com ff:fe no lugar dos bytes faltantes.

Armazenamento

É claro que os tipos de rede são mais eficientes em termos de armazenamento que utilizar simplesmente um VARCHAR. Cada dígito aqui é um dígito hexadecimal de 0 a F e não um byte completo de 00 a FF. Portanto, há uma economia de 50% do espaço ocupado. Em termos de espaço temos:

  • inet: 7 bytes com IPv4 e 19 bytes com IPv6
  • cdir: 7 bytes com IPv4 e 19 bytes com IPv6
  • macaddr: 6 bytes
  • macaddr8: 8 bytes

Operadores

A brincadeira começa a ficar interessante aqui. Se você olhar o capítulo “Funções e Operadores” na documentação, verá uma sessão específica só para tipos de rede. Antes de mais nada, vou criar uma tabela com alguns registros aleatórios para poder brincar aqui:

CREATE TABLE net (ip inet);

INSERT INTO net SELECT inet (
    '10.' || 
    round(random()*255) || '.' ||
    round(random()*255) || '.' ||
    round(random()*255) || '/' || 
    '8')
FROM generate_series(1,100);

INSERT INTO net SELECT inet (
    '128.191.' || 
    round(random()*255) || '.' ||
    round(random()*255) || '/' || 
    '16')
FROM generate_series(1,100);

INSERT INTO net SELECT inet (
    '192.168.1.' || 
    round(random()*255) || '/' || 
    '24')
FROM generate_series(1,100);

Alguns operadores não são novidade como <, <=, =, >=, > e <>. Como esperado temos:

teste=# INSERT INTO net SELECT inet (round(random()*255) || '.' || round(random()*255) || '.' ||round(random()*255) || '.' ||round(random()*255) || '/' || round(random()*4)*8) AS ip FROM generate_series(1,10);
INSERT 0 10
teste=# SELECT ip FROM net WHERE ip > inet '192.168.0.0';
        ip
-------------------
 221.208.133.56/16
 248.244.222.86/16
 254.187.237.159/8
(3 rows)

teste=# SELECT ip FROM net WHERE ip < inet '192.168.0.0';
        ip
------------------
 110.46.113.5/24
 173.21.188.12
 13.117.195.196/8
 143.51.137.1/8
 47.222.9.180/0
 164.98.44.97/16
 38.91.99.5/24
(7 rows)

teste=# SELECT ip FROM net WHERE ip =  inet '164.98.44.97';
 ip
----
(0 rows)

teste=# SELECT ip FROM net WHERE ip =  inet '164.98.44.97/16';
       ip
-----------------
 164.98.44.97/16
(1 row)

Mas o PostgreSQL traz outros operadores mais interessantes:

  • << está contido
  • <<= está contido ou igual
  • >> contém
  • >> contém ou igual
  • && contém ou está contido
  • NOT bit a bit
  • AND bit a bit
  • OR bit a bit
  • +
  • –

Vejamos como ficam alguns exemplos:

teste=# SELECT ip FROM net WHERE ip <<  inet '192.168.1.0/16';
        ip
------------------
 192.168.1.32/24
 192.168.1.115/24
 192.168.1.168/24

teste=# SELECT ip FROM net WHERE ip >>  inet '192.168.1.0/32';
        ip
------------------
 192.168.1.32/24
 192.168.1.115/24
 192.168.1.168/24
 192.168.1.233/24
 192.168.1.213/24

teste=# SELECT
teste-#     ip,
teste-#     ~ip AS "NOT",
teste-#     ip & inet'10.0.0.1' AS "AND",
teste-#     ip | inet'10.0.0.1' AS "OR"
teste-# FROM net LIMIT 5;
       ip        |        NOT        |   AND    |      OR
-----------------+-------------------+----------+---------------
 10.38.170.142/8 | 245.217.85.113/8  | 10.0.0.0 | 10.38.170.143
 10.134.7.73/8   | 245.121.248.182/8 | 10.0.0.1 | 10.134.7.73
 10.70.82.112/8  | 245.185.173.143/8 | 10.0.0.0 | 10.70.82.113
 10.224.98.126/8 | 245.31.157.129/8  | 10.0.0.0 | 10.224.98.127
 10.75.233.180/8 | 245.180.22.75/8   | 10.0.0.0 | 10.75.233.181
(5 rows)

teste=# SELECT
    ip,
    ip + 64 AS soma,
    ip - 64 AS subtracao,
    ip - inet'10.0.0.1' subtracao_ip
FROM net LIMIT 5;
        ip        |       soma       |    subtracao     | subtracao_ip
------------------+------------------+------------------+--------------
 10.38.170.142/8  | 10.38.170.206/8  | 10.38.170.78/8   |      2534029
 10.134.7.73/8    | 10.134.7.137/8   | 10.134.7.9/8     |      8783688
 10.199.2.34/8    | 10.199.2.98/8    | 10.199.1.226/8   |     13042209
 10.78.195.52/8   | 10.78.195.116/8  | 10.78.194.244/8  |      5161779
 10.153.148.224/8 | 10.153.149.32/8  | 10.153.148.160/8 |     10065119
(5 rows)

Agora a cereja do bolo. Primeiro vamos aumentar o volume de dados na tabela:

INSERT INTO net SELECT inet (
    round(random()*255) || '.' ||
    round(random()*255) || '.' ||
    round(random()*255) || '.' ||
    round(random()*255) || '/' || 
    '8')
FROM generate_series(1,100000);

Agora vejamos o plano de execução de uma de nossas consultas:

teste=# EXPLAIN ANALYZE SELECT ip FROM net WHERE ip <<  inet '192.168.1.0/32';
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on net  (cost=0.00..1697.75 rows=1 width=7) (actual time=18.627..18.627 rows=0 loops=1)
   Filter: (ip << '192.168.1.0'::inet)
   Rows Removed by Filter: 100300
 Planning time: 0.082 ms
 Execution time: 18.660 ms

Você não precisa entender muito sobre planos de execução. Apenas observe que fizemos um “Seq Scan” na tabela “net”, ou seja, varremos todas as linhas da tabela em busca de quais atendem aos requisitos da nossa cláusula WHERE. E claro: levou 18,66ms para rodar o comando.

Agora vamos criar um índice e rodar o mesmo plano de execução:

teste=# CREATE INDEX ON net (ip);
CREATE INDEX
teste=# EXPLAIN ANALYZE SELECT ip FROM net WHERE ip <<  inet '192.168.1.0/32';
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Index Only Scan using net_ip_idx on net  (cost=0.29..2.52 rows=1 width=7) (actual time=0.027..0.027 rows=0 loops=1)
   Index Cond: ((ip > '192.168.1.0'::inet) AND (ip <= '192.168.1.0'::inet))
   Filter: (ip << '192.168.1.0'::inet)
   Heap Fetches: 0
 Planning time: 0.336 ms
 Execution time: 0.063 ms
(6 rows)

Agora o plano de execução passou a utilizar o índice “net_ip_idx” que criamos. E o resultado? Um tempo de execução de 0,062ms. Este é o grande pulo do gato com os operadores. Suas operações já são indexáveis por natureza. E isso você nunca vai conseguir imitar utilizando um VARCHAR.

Funções

Além dos operadores, temos também algumas funções nativas para trabalhar com tipos de dados para redes. Estas não são indexáveis por natureza, mas você pode criar índices incluindo estas funções se for realmente importante. Você pode e deve ver a lista das funções existentes na documentação oficial. Sempre. Não pretendemos aqui substituir a documentação, apenas dar uma visão mais clara sobre alguns pontos específicos. Então vá lá e faça uma visita.

Conclusões

Tipos de dados específicos como os tipos de rede são muito poderosos, eles nos ajudam de diversas formas:

  • Armazenamento otimizado, ocupando muito menos espaço;
  • Validação nativa bem feita;
  • Existe um arsenal operadores e funções prontas para utilizarem estes tipos de dados;
  • Operadores nativos utilizam índices automaticamente sem que você precise fazer nada além de criar um índice comum no campo.