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

1. Complete isolation of customers from each other
2. 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.

1. pg_catalog allows access to database and user names for everyone, however access to this schema cannot be disabled without breaking essential features
2. no support for limiting database sizes
3. database local usernames are not fully supported yet (md5 auth)
4. removing all rights granted to user (prerequisite to deleting that user) must be done for each single object (no "REASSIGN" equivalent)
5. No built-in support for setting user, query or database priorities