PostgreSQL Hosting

After years of administering single-site PostgreSQL, I'm finally
getting started on a PostgreSQL hosting project. I just wanted to
check to see how our approach jives with what other folks in the
community have done.

Much of this is derived from the "hardening" techniques derived from
phpPgAdmin.patch:

http://www.linuxpages.org/phpPgAdmin.patch_en.php

Here is an excerpt from our current pg_hba.conf:

pg_hba.conf
# allow users to connect to database of same name, from network, with
password
host sameuser all 192.168.1.0/24 md5
# postgres connect from network with password
host all postgres 192.168.1.0/24 md5

As far as I can tell, this solves 80% of the problem. Now users are
restricted to databases corresponding to their usernames. The only
difficulty seems to be unrestricted access to cluster-wide system
catalogs.

So, in order to restrict access to the system catalogs, we do the
following (where all connections are performed as user postgres and
dbuser is the example name of a user/database):

# all connections as user postgres
template1=# create database dbuser;
template1=# revoke all privileges on database dbuser from public;

dbuser=# create or replace view pg_db as select oid, * from
pg_database where datname=(select current_user);
dbuser=# grant select on pg_db to public;
dbuser=# revoke select on pg_database from public;
dbuser=# create or replace view pg_grp as select * from pg_group
where groname=(select current_user);
dbuser=# grant select on pg_grp to public;
dbuser=# revoke select on pg_group from public;
dbuser=# create or replace view pg_user as select usename, usesysid,
usecreatedb, usesuper, usecatupd, '********'::text as passwd,
valuntil, useconfig FROM pg_shadow where usename=(select current_user);
dbuser=# grant select on pg_user to public;
dbuser=# grant select on public.pg_user to public;
dbuser=# revoke all privileges on schema public from public;

template1=# create user dbuser with 'changeme';
template1=# grant all privileges on database dbuser to dbuser;
template1=# alter database dbuser owner to dbuser;

dbuser=# grant all on schema public to dbuser;

If we ever needed to remove a user/database, it should be as easy as:

dropdb dbuser
dropuser dbuser

As far as I can tell, this pretty well locks down anyone accessing
the database through allowed interfaces into the databases they own
and prevents them from accessing or modifying any other databases.

Otherwise, we'll be letting users use phpPgAdmin to administer their
databases. In phpPgAdmin, setting $conf['owned_only'] = true allows
one to restrict the display of databases to those owned by the user
who is logged in, but this setting does nothing to prevent arbitrary
SQL being run to access and modify databases and tables not owned by
the current user. Even though the above settings serve to fulfill
this function, we still decided to set this.

Is this idiomatic? Is this in the realm of best practices? Am I
missing anything? Are there any less intrusive ways of doing things?
Am I hurting my prospects for upgrade paths? There were a few minor
patches made to phpPgAdmin, for instance, to get it to use the new
pb_db view.

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005