Re: PostgreSQL Hosting

Okay, after setting up a hosting environment based on my original
post, we immediately discovered a few caveats. One is that, as
written, pg_user creates issues with pg_dump because a given user
needs access to various system catalogs and postgres must exist in
pg_user, so we updated the view. Secondly, though, we actually had to
modify system_views.sql because pg_user, as a system catalog, behaves
differently from a standard view and caused more issues with pg_dump.

So here's what we added to system_views.sql:

CREATE VIEW pg_user AS
SELECT usename, usesysid, usecreatedb, usesuper, usecatupd,
'********'::text as passwd, valuntil, useconfig
FROM pg_shadow
WHERE usename IN (
(SELECT current_user),
(
SELECT ps.usename
FROM pg_database pd,pg_shadow ps
WHERE pd.datdba=ps.usesysid
AND datname=current_database()
),
'postgres'
)

Then we ran into the problem of allowing users to dump their own
databases, which requires access to pg_database that we're trying to
prevent at the user level. So we're now setting our schema search
path for all user-created databases to public, pg_catalog, $user.
Then we're creating a view called pg_database as:

CREATE OR REPLACE VIEW pg_db AS
SELECT oid, *
FROM pg_database
WHERE datname=(select current_user)

Now pg_dump should be able to use our restricted version of
pg_database with little trouble, although I don't know (yet) whether
putting a customized replacement view in front of an actual system
catalog in the search path is going to cause any other issues in
other parts of the system.

So, to summarize: we're shooting for a user-isolated PostgreSQL
hosting environment.

In order to accomplish this, we have to hack the following:

1. system_views.sql
2. phpPgAdmin to use the custom pg_grp view (we could probably
perform similar surgery to overshadow pg_group as we did pg_database
with a view in public)

Then we use the built in PostgreSQL privilege system as outlined in
my original post plus modify the schema search path per user database.

At the end of this, it seems like the only hobble (other than having
to hack stuff to achieve user isolation) is that we can no longer get
a list of users as super-user from pg_user.

A couple of years ago, Tom Lane said this with regard to isolating
users for a PostgreSQL-based hosting environment:

http://archives.postgresql.org/pgsql-novice/2003-10/msg00124.php

But to me, that seems like a fairly draconian approach to creating a
hosting environment.

In MySQL, the hack is a privilege called SHOW DATABASES, which can be
set for all databases. Their user setup seems to be wholly different
because they don't seem to provide a cluster-wide mechanism for
viewing users.

Again, I'm wondering whether anyone else in the community has
developed any best practices when it comes to PostgreSQL hosting.

--
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

On Jul 1, 2005, at 3:45 PM, Thomas F. O'Connell wrote:

> 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
>