Connect to PostgreSQL using integrated security or fall back to public login

Question

I am using Npgsql and I was wondering whether there was a way to connect to a PostgreSQL server using integrated security that automatically falls back to a public login? Or maybe there is another way to do what I am trying to achieve.

I have an ASP.NET web site (IIS7.5) that uses Windows Authentication, and I have configured PostgreSQL to allow access via SSPI, this works fine as long as there is a role with the right name in the database. The web site is available for everyone on the intranet to view but some users have a few extra abilities (or can see extra data) that everyone else should not be able to do.

Currently, I am using something like the following:

NpgsqlConnectionStringBuilder csb = new NpgsqlConnectionStringBuilder();

csb.Pooling = false; // if true, causes intermittent DB connection issues
csb.Database = "dbname";
csb.Host = "192.168.1.100";
csb.IntegratedSecurity = true;

dbconn.ConnectionString = csb.ConnectionString;

try
{
    dbconn.Connect();
}
catch (NpgsqlException ex)
{
    if (/* check for authentication failed */)
    {
        csb.IntegratedSecurity = false;
        csb.UserName = "www-read";
        dbconn.ConnectionString = csb.ConnectionString;
        dbconn.Open();
    }
}

This works fine, except for each time it falls back to the "www-read" role, it adds a critical log entry saying that there was an authentication error. The only way I can see around this is always connecting via www-read and querying whether a suitable integrated-security role exists and reconnecting, but that seems even more cumbersome than the above.

Answer 1

I don't see any passwords in your code. Are you using any security at all?

If you don't want to embed password into your source code (and rightly so), you should create ~/.pgpass file in your home directory (%appdata%/postgresql/pgpass.conf on Windows) which has 1 or more lines like this:

hostname:port:database:username:password

This will allow any program that is using libpq (including npgsql) to look up for passwords from .pgpass upon any connection attempt.

If this will run under www-data user, .pgpass should be created in its home directory (but be sure to configure your web server not to serve this file ever!)

I think doing this should also fix problem with connection pooling that you seem to have.

Answer 2

Integrated Security for Postgres on Windows can be done with some caveats by editing 2 files in your postgresql data directory.

1. pg_ident.conf maps A/D logins to postgres usernames:

# MAPNAME   A/D LOGIN                 PG-USERNAME
#
MapForSSPI  Login@DOMAINNAME          www-read
MapForSSPI  OtherLogin@DOMAINNAME     or-whatever-pg-rolename

I'm not sure there is a better option here than individually listing all users (or service accounts?) who you will permit to use integrated login. Every A/D login has to be mapped to a Postgres username. So this is probably only viable for a short and closely-controlled list.

2. pg_hba.conf then permits the PG-USERNAMEs you listed above to use integrated security as a login method. Put these user-specific lines above any user all lines. (Often in pg_hba.conf you rely on user all lines to permit anyone to login with their username & password).

#== put these lines above any catchall lines ==
# TYPE  DATABASE  USER      ADDRESS       METHOD
host    all       www-read  <yourIP4net>  sspi  map=MapForSSPI
host    all       www-read  <yourIP6net>  sspi  map=MapForSSPI

The caveat is that this only works on a machine in a Windows Domain (presumably your normal situation at work) or else only on localhost.

More details: https://www.cafe-encounter.net/p2034/postgres-using-integrated-security-on-windows-on-localhost.