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.
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.
- Complete isolation of customers from each other
- Standardized procedures for creating and deleting databases and users
- 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
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
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;
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>
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)
- no built in way to terminate connections to a database (cf. MS SQL "KILL" command)
(NOTE: in current releases, this can be done from the command line by killing the process of the specific connection. Starting in 8.4, you can use the built in pg_terminate_backend(pid int) function)
- 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