Database Schema Recommendations for an Application

Within a PostgreSQL database cluster the basic methods for separating and name spacing objects is through Managing Databases and Schema Data Definitions.

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 database 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.

Unix commands:

  zfs create -o mountpoint=/pgdata/app1_data pgdatapool/app1_data
  zfs set refquota=10G pgdatapool/app1_data
  zfs set refreference=10G pgdatapool/app1_data
  zfs create -o mountpoint=/pgdata/app1_index pgindexpool/app1_index
  zfs set refquota=10G pgindexpool/app1_index
  zfs set refreference=10G pgindexpool/app1_index

SQL commands:

  create tablespace app1_data location '/pgdata/app1_data';
  create tablespace app1_index location '/pgdata/app1_index';

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.

  create user app1 nologin;
  grant create on tablespace app1_data to app1;
  grant create on tablespace app1_index to app1;

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 user app1_pool password 'app1_pool';
  grant app1_role to app1_pool;

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.

  create schema app1 authorization app1;
  grant usage on schema app1 to app1_role;
  grant usage on schema app1 to app1_query_role;

Disclaimer

These are the recommendations from an experienced DBA. They can be followed or ignored. Personally by following these few simple methods of managing application schemas, it has made my life easier.

Database Schema Recommendations for an Application

Contents