Shared Database Hosting

From PostgreSQL wiki

The objective of this page is to provide a place to exchange ideas on how
best to run PostgreSQL in a shared hosting environment as suggested here.
If you are interested in debating the intrinsics of running PostgreSQL under similiar preconditions, please contact me directly by email at jacob at internet24 dot de, as I only check this site rather infrequently and the change notification system doesn't seem to work for me.

Defining shared hosting

For the purpose
of this document, shared database hosting is the process of running PostgreSQL clusters for the benifit of third parties (henceforth called customers) by a DBMS management authority (the hosting provider). Customers have no organizational relationship
to each other but each one has a relationship to the hosting provider. Customers typically require complete control over a small number of databases but do not want to manage their own PostgreSQL cluster. Sharing the resources of a single cluster system among many customers is economically attractive.

Goals of a shared hosting setup

Complete isolation of customers from each other

Standardized procedures for creating and deleting databases and users

Approaches

Approach 1

Manage cluster only via access to a super user account

no permanent modification of pg_hba.conf etc. required

central managing system can manage several clusters without the need for additional control channels

Customers cannot directly create new roles and databases

Each customer database is given an associated main user

acting as a database local super user

typically creates and owns all objects

The can be a number of additional users per database

Rights of these users are granted by the main user

pg_hba.conf

We allow TCP/IP access to all databases in
the cluster using md5 authentication from
the local hosting network. A role can only
login if it is member of a role that has the
same name as the database.

template1

We modify template1 to revoke all rights
from "PUBLIC" to the public schema, to prevent
access to the public schema of indiviudial customer
databases by other customers. Also we add support
for PL/PGSQL.

psql -U postgres template1 -f - << EOT
REVOKE ALL ON DATABASE template1 FROM public;
REVOKE ALL ON SCHEMA public FROM public;
GRANT ALL ON SCHEMA public TO postgres;
CREATE LANGUAGE plpgsql;
EOT

If you deem it acceptable to break certain PostgreSQL
management applications (pgadmin, phppgadmin, parts of psql's functionality),
then you could also revoke some more rights from pg_catalog to "resolve" problem
1 in the last section of this document:

psql -U postgres template1 -f - << EOT
REVOKE ALL ON pg_user FROM public;
REVOKE ALL ON pg_roles FROM public;
REVOKE ALL ON pg_group FROM public;
REVOKE ALL ON pg_authid FROM public;
REVOKE ALL ON pg_auth_members FROM public;
REVOKE ALL ON pg_database FROM public;
REVOKE ALL ON pg_tablespace FROM public;
REVOKE ALL ON pg_settings FROM public;

EOT

Creating a new database + main user

We create a role without any special access
rights named after our planned database name
and a login role for our main user
that becomes a member of the former role. Then
we create the database with the main user
as its owner. Finally we grant all rights
to the public schema in the new database
to the main user