tscho's questions

Estou usando autenticação de certificado para clientes em um servidor de banco de dados PostgreSQL 9.1. Vários nomes comuns de certificados são mapeados para um único usuário do banco de dados em pg_ident.conf .

Não desejo criar um usuário de banco de dados para cada certificado porque é mais fácil gerenciar certificados aceitos com pg_ident.conf. Mas agora preciso de alguma consulta para mapear um processo de back-end para o nome comum usado para a conexão. Existe uma maneira de obter o nome comum (campo CN) do certificado com o qual um usuário se conectou ao banco de dados?

EDITAR

Obrigado araqnid por me indicar a extensão sslinfo ( source ). A função ssl_client_dn_field('CN') fornece exatamente as informações de que preciso. Mas as funções sslinfo funcionam apenas para a conexão/backend da sessão atual, prefiro precisar dessas informações para outras conexões/backends (consulte pelo ID do processo de backend).

A extensão sslinfo usa a variável global MyProcPort (definida em globals.c ) do tipo struct Port* para obter as informações SSL necessárias. Talvez alguém possa me dar uma dica de como a MyProcPortvariável de outros backends pode ser acessada em uma extensão (também conhecido como módulo contrib).

Estou executando o servidor de banco de dados PostgreSQL 8.4 e o front-end do terminal psql no Ubuntu 10.04 Lucid Lynx e gostaria de abranger uma única transação em várias sessões psql sequenciais.

Quando eu me conecto ao meu banco de dados com o psql, uma nova conexão é estabelecida e um processo de back-end do servidor para essa conexão é criado. Quando desconecto, a conexão é liberada e o processo de back-end é encerrado. Uma transação (não-XA*) está vinculada ao escopo de uma conexão, portanto, obviamente, não há uma maneira direta de abranger uma única transação em várias sessões do psql.

O que eu gostaria de conseguir é que a seguinte sequência de comandos possa ser executada em uma única transação e, portanto, retornar o mesmo registro de data e hora da transação em cada chamada de now():

tscho@test:~$ sudo -u postgres psql -p 5433 --no-align --tuples-only -c "select now()"
2012-02-17 21:25:07.027056+01
tscho@test:~$ sudo -u postgres psql -p 5433 --no-align --tuples-only -c "select now()"
2012-02-17 21:25:09.487601+01

registro do banco de dados:

2012-02-17 21:25:07 CET 0- LOG:  connection received: host=[local]
2012-02-17 21:25:07 CET 0- LOG:  connection authorized: user=postgres database=postgres
2012-02-17 21:25:07 CET 0-2/0 LOG:  duration: 0.366 ms  statement: select now()
2012-02-17 21:25:07 CET 0- LOG:  disconnection: session time: 0:00:00.002 user=postgres database=postgres host=[local]
2012-02-17 21:25:09 CET 0- LOG:  connection received: host=[local]
2012-02-17 21:25:09 CET 0- LOG:  connection authorized: user=postgres database=postgres
2012-02-17 21:25:09 CET 0-2/0 LOG:  duration: 0.347 ms  statement: select now()
2012-02-17 21:25:09 CET 0- LOG:  disconnection: session time: 0:00:00.002 user=postgres database=postgres host=[local]

Claramente, isso não é o que eu realmente quero fazer. Quero poder executar vários scripts bash que se conectam ao banco de dados e executar instruções e scripts SQL com psql em uma única transação.

* Embora o protocolo XA permita BEGIN TRANSACTION e PREPARE TRANSACTION em conexões diferentes, mas o PostgreSQL não oferece suporte a isso.

Minha primeira tentativa de resolver esse problema foi configurar o pool de conexões do PgBouncer 1.5 e configurá-lo como um proxy simples com exatamente uma conexão com o banco de dados de destino (modo de pooling de sessão). Meu raciocínio era que o PgBouncer estabeleceria essa conexão na inicialização e que eu poderia conectar/desconectar de/para o proxy com psql enquanto a conexão com o banco de dados permanece aberta.

tscho@test:~$ sudo -u postgres psql pgproxy -U pgbouncer -p 6432 --no-align --tuples-only -c "select now()"
2012-02-17 21:25:23.517019+01
tscho@test:~$ sudo -u postgres psql pgproxy -U pgbouncer -p 6432 --no-align --tuples-only -c "select now()"
2012-02-17 21:25:26.943172+01

Na verdade, isso funciona muito bem, como mostra o log do banco de dados:

2012-02-17 21:25:17 CET 0- LOG:  connection received: host=[local]
2012-02-17 21:25:17 CET 0- LOG:  connection authorized: user=postgres database=postgres
2012-02-17 21:25:23 CET 0-1/0 LOG:  duration: 0.110 ms  statement: select 1
2012-02-17 21:25:23 CET 0-1/0 LOG:  duration: 0.053 ms  statement: select now()
2012-02-17 21:25:23 CET 0-1/0 LOG:  duration: 0.046 ms  statement: DISCARD ALL
2012-02-17 21:25:26 CET 0-1/0 LOG:  duration: 0.126 ms  statement: select now()
2012-02-17 21:25:26 CET 0-1/0 LOG:  duration: 0.043 ms  statement: DISCARD ALL

Mas há um pequeno problema com essa abordagem, assim que eu começo uma transação na conexão do proxy e desconecto...

tscho@test:~$ sudo -u postgres psql pgproxy -U pgbouncer -p 6432 --no-align --tuples-only -c "start transaction"
START TRANSACTION

...a conexão é liberada pelo PgBouncer.

2012-02-17 21:32:47 CET 0-1/2178 LOG:  duration: 0.025 ms  statement: start transaction
2012-02-17 21:32:47 CET 0- LOG:  disconnection: session time: 0:07:20.737 user=postgres database=postgres host=[local]

Claro que isso faz todo o sentido para um pool de conexão. Sua função é fornecer (uma) conexão(ões) compartilhada(s) para vários clientes, mas isolar as transações desses clientes. Mas para o meu caso de uso, uma transação compartilhada é exatamente o que eu precisaria...

Então, minha pergunta agora é: existe uma maneira de configurar o PgBouncer (ou outro pool de conexão) para não liberar a conexão após a desconexão após BEGIN/START TRANSACTION ou existe outra maneira de conseguir o que eu gostaria de fazer?

Todas as perguntas adicionais a este post, comentários e, claro, respostas apreciadas!