Users, Databases, Schemas, and how they fit together in Postgresql

When first learning Postgres, I was pretty confused by how the various pieces of the database all fit together. Coming from an Oracle background, wrapping my head around how different it was took some effort. So, I have created this simple “dictionary guide” of sorts, to teach how things interact. Included at the end are useful code snippets which should be all you need to get things working.

TERMS

Cluster

Analogous to an Instance in Oracle, this is the collection of Databases, Users, and Tablespaces that exists on a server.

User

Exists inside the cluster, and receives permissions to log in to, and use, objects inside the various databases and schemas.

Database

Exists inside the cluster, on the same level as the users, and contains schemas, extensions, and more, which I will not be discussing in this post.

Schema

Exists inside a database, and contains database objects, such as tables, indexes, and functions.

Tablespace

This is an OS directory, which contains the physical storage of the database files.

Tips

In order to log into the cluster, a user must have the LOGIN permission, granted by SQL statements.

By default, as soon as a user is logged in to a cluster, he has permissions to view all objects in all databases in the cluster. He ALSO has the permission to create, use, and drop objects in the PUBLIC schema in any database. This is because all users are granted the PUBLIC role, which has grants on all of the PUBLIC schemas.

To limit the access users have, you must manually edit the permissions on each database to revoke all public access. To limit access on databases created in the future, you must edit the permissions on the database called template1 which exists in every cluster. The reason for this is that the create database command actually clones the template1 database. Even after editing template1 and creating a database, you may still want to run a script which ensures that your permissions are locked down as desired. Once you revoke access from a database, you must regrant all necessary priveleges on databases and schemas.

A good practice regarding tablespaces is to have one tablespace per database, (this is to make management easier, and to tune your database performance — For example, don’t have a write heavy database tablespace on an SSD disk.)and which has the same name as the database it serves, and to have all you tablespaces in one dedicated directory. You should pick a directory on your server that is large and extendable, as your datafiles may grow very large, since there is no limit on how big they can get, other than your OS. However, take care not to put them in a directory which will not always be available, such as an external hard drive. Within the PGDATA, there is a directory called pg_tblspc which contains symbolic links to all your tablespaces. DO NOT put your tablespaces inside the PGDATA, as this breaks backups using pg_basebackup, and can cause you to accidentally delete your tablespaces during upgrades.

A directory must exist and be empty before being used as a tablespace.

A good practice regarding schemas and databases is to have one database per application, and one schema per application component. You can have a user who shares the database name, which will be used in your application.

CODE SNIPPETS

In logical order, you will want to:

Create the tablespace directory:

Enusre that postgres has OS permissions to write to said directory.

As superuser, in the cluster:

Create a database:

The following two commands should be run on all existing databases which you want to secure, and on template1. You will have to re-grant manually all users who need access.

Ensure that each database’s permissions are tight:

(run from inside the cluster)

Ensure that each PUBLIC schema is unusable to every user:

(This must be run from inside each database)

Create a user:

Grant the user permissions to log onto the database, use the public schema (as necessary) and use the tablespace:

In order to connect with pgadmin, you will need to grant connect on the maintenance DB to each user. By default this is the Postgres database.

Create a schema:

(Run this from inside the database where you want the schema to be created.)

The schema will receive the name of, and be owned by, the user specified in <username>. This is to ensure that the search path always works.

To keep all users from connecting to a given database, you can run:

Devops DBA. This blog chronicles my “gotcha” moments — Each post contains an answer I would have like to have found when searching for those pesky errors I get.