Database Schema Recommendations for an Application

The recommendation is to create a single database with multiple named schemas. This is different than a common (and older) practice of creating multiple databases and storing the objects within the "public" schema. Additionally, it is recommended to remove the public schema.

These are some of the advantages to following this recommendation:

Cross schema object access is possible from a single database connection.

Granting access to a schema is executed through a GRANT statement versus a reconfiguration of the pg_hba.conf file.

Schemas are the ANSI standard for object separation and name spacing.

Managing only one database within a single server (PostgreSQL cluster).

An advantage to using the separate database method is sessions connected to a database cannot cross database boundaries. Separation of objects is more complete. However, if the desire is have more separation of the objects (i.e., different environments), it is recommended to create a new server.

Note: Seperation at the databse level can be used for multi-tenant hosting when combined with the db_user_namespace configuration parameter. Schema seperation again becomes the important feature for seperating the database components within each of the hosted databases.

Creating Tablespaces

Create a set of tablespaces appropriate for each application. This provides for the physical separation of storage from one application to another. It also can provide for separation of storage within an application. Different storage of tables and indexes would enhance management flexibility and could provide additional performance.

PostgreSQL expects the host operating system to provide device management. Create a file system for each tablespace to separate and control the tablespace storage. The following is an example using ZFS with storage pools named pgdatapool and pgindexpool. Two 10 GB tablespaces will be used for an application, one for tables and one for indexes.

Creating Accounts and Roles

PostgreSQL database objects have an account (role) ownership. Create an account synonymous with the schema that will name space the objects. This account will be used as the owner for the objects. For security purposes do not allow anyone to connect to the account owning the application database objects. This account will need access to the tablespaces previously created. When application objects are created make sure the ownership is properly set.

Grant database object privileges to users or application accounts through the use of roles. Create a set of roles appropriate for an application. For simplicity create at least two roles, one to use for read/write privileges for the application and one for query privileges. As database objects are created for the application perform the appropriate grants to the roles.

create role app1_role;
create role app1_query_role;

Applications generally connect to the database using pooled (shared) accounts from application servers. Limit the knowledge of those accounts and their credentials. If possible, use definitions within the pg_hba.conf to make sure those accounts can only connect to the database from the defined application servers.

Create Application Schemas

Create the application schema and grant the application users and roles the appropriate privileges. Make sure to create the application objects within the defined schema and set the appropriate object ownership.