Safe design for SET SESSION AUTHORIZATION multi-user mod-perl2 connection caching

Question

I have a mod_perl2.0.4 / Apache2.2 web app running on CentOS 6.4 with PostgreSQL 9.0.

Until recently, I had this setup: Apache::DBI and DBI->connect_cached for all connections, which was starting to give FATAL: sorry, too many clients already even in my development area where I'm the only user.

In an effort to debug this, I have removed all references to Apache::DBI, upgraded to the latest DBI, and replaced all occurrences of connect_cached with plain DBI->connect. It seems to me now that somewhat less connections are made and then left <IDLE>. However, I realize that I haven't been calling disconnect() on all of my statement handles, because it had sounded like under Apache::DBI it wouldn't have made a difference.

My connections currently connect all as the same user, then lower their privileges based on which user it is via SET SESSION AUTHORIZATION. I do it this way because some other apps that use the database allow for a passworded login, which can pass the credentials directly to the database, but this particular web app uses an honour system login screen whereby you just click your name to log in. So it's future-security-ready but convenience-enabled at the moment. Also, database triggers for history and such rely on the session user being set correctly to track who did what.

Because I was concerned about a database handle being reused with the wrong session user, I pass { private_user_login => $login_role_name, PrintError => 0, RaiseError => 1, AutoCommit => 1} to connect_cached to differentiate each connection by user. But since I always set the session authorization immediately after connecting, I suppose that all the private_user_login hash does is make it so that for a given Apache process, there might be at least as many DB connections created and left idle as there are users, if eventually every user manages to randomly use a given Apache process. Meanwhile, because I don't disconnect any handles, they eventually get used up.

My question is, is it safe to take out the private_user_login to make all the connection handles look the same, to cut down on the number of connections left open, or is it possible that a connection handle could be re-used in the middle of a script (after setting the session user) by a different user, thus creating a race condition? Also, although Apache::DBI's docs say I needn't remove disconnect() calls, should I still have such a call at the end of every one of my scripts so that Apache::DBI can decide whether to disconnect?

In other words, without my private connection variable, do SET SESSION AUTHORIZATION's effects persist when the next Apache::DBI->connect() reuses the existing connection? If so, is it ever possible that a connection is re-used by another request while one request is currently executing but not currently using the database handle?

Answer 1

It seems safe. To "verify" you can make an artificial race condition like this:

use Apache2::RequestUtil;
use Apache2::RequestRec;
my $r = Apache2::RequestUtil->request;
$r->headers_out->add('Cache-control' => "must-revalidate, no-cache, no-store");
require Apache2::Request;
my $req = Apache2::Request->new($r);
$r->content_type("text/html");
my $login_role_name = $req->param('u');
$r->print($u);
$r->print('<br>' . $$);
use DBI;

my $dbh = DBI->connect_cached("dbi:Pg:dbname=......,{ RaiseError => 1, AutoCommit => 1});
$dbh->do("set session authorization ?; ", undef, $login_role_name);
{
    use warnings NONFATAL => 'all';
    my $rows = $dbh->selectall_arrayref('select pg_backend_pid(), current_user::text');
    warn "pg ${$$rows[0]}[0] mp $$ auth: ${$$rows[0]}[1] original auth: $login_role_name";
    sleep 10;
    $rows = $dbh->selectall_arrayref('select pg_backend_pid(), current_user::text');
    warn "pg ${$$rows[0]}[0] mp $$ auth: ${$$rows[0]}[1] original auth: $login_role_name";
}

...and then hit it with two different '?u=...' URLs. The auth will always match the original auth, because the dbh is not available to be given away while it's still in a script that's executing.

Answer 2

I recommend a somewhat different tack, if you can.

Keep it simple in Apache. Use private sessions per user, if that's what's easiest to make safe and reliable.

Then put a PgBouncer between the PostgreSQL server and your Apache instance. Set it to transaction pooling mode. It'll happily multiplex your connections, and it'll take care of calling DISCARD ALL whenever a connection switches between users.

I think you can still use SET SESSION AUTHORIZATION on connections made via PgBouncer.