Shared Database Hosting

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.
 #host samerole all hosting_network md5
 host samerole all 192.168.0.0/24 md5
==== 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
 psql -U postgres template1 -f - <<EOT
 
 CREATE ROLE <DBNAME> NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOLOGIN;
 CREATE ROLE <DBMAINUSER> NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ENCRYPTED PASSWORD '<DBMAINUSERPASS>';
 GRANT <DBNAME> TO <DBMAINUSER>;
 CREATE DATABASE <DBNAME> WITH OWNER=<DBMAINUSER>;
 REVOKE ALL ON DATABASE <DBNAME> FROM public;
 
 EOT
 psql -U postgres <DBNAME> -f - <<EOT
 
 GRANT ALL ON SCHEMA public TO <DBMAINUSER> WITH GRANT OPTION;
 
 EOT
==== Create an additional user ====
 psql -U postgres <DBNAME> -f - <<EOT
 
 CREATE ROLE <DBEXTRAUSER> NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ENCRYPTED PASSWORD '<DBEXTRAUSERPASS>';
 GRANT USAGE ON SCHEMA public TO <DBEXTRAUSER>;
 GRANT CONNECT,TEMPORARY ON DATABASE <DBNAME> TO <DBEXTRAUSER>;
 GRANT <DBNAME> TO <DBEXTRAUSER>;
 
 EOT
==== Remove an additional user ====
 psql -U postgres <DBNAME> -f - <<EOT
 
 -- REMOVE ALL PERMISSIONS FROM ALL OBJECTS OWNED BY DBEXTRAUSER
 -- TERMINATE CONNECTIONS OF DBEXTRAUSER
 REASSIGN OWNED BY <DBEXTRAUSER> TO <DBMAINUSER>;
 DROP ROLE <DBEXTRAUSER>
 
 EOT
==== Remove a database + main user ====
 psql -U postgres template1 -f - <<EOT
 
 -- TERMINATE CONNECTIONS OF ALL USERS CONNECTED TO <DBNAME>
 DROP DATABASE <DBNAME>;
 DROP ROLE <DBMAINUSER>
 DROP ROLE <DBNAME>
 EOT
== PostgreSQL issues that affect shared hosting ==
This is meant to be a list of problem areas where the current
PostgreSQL version leaves something to be desired with respect to
shared hosting requirements.
# pg_catalog allows access to database and user names for everyone, however access to this schema cannot be disabled without breaking essential features
# no support for limiting database sizes
# database local usernames are not fully supported yet (md5 auth)
# removing all rights granted to user (prerequisite to deleting that user) must be done for each single object (no "REASSIGN" equivalent)
# No built-in support for setting user, query or database priorities
Category:Administration