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:

mkdir /your/designated/directory/dbname_tbs

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

As superuser, in the cluster:

CREATE TABLESPACE dbname_tbs
OWNER postgres
LOCATION '/your/designated/directory/dbname_tbs';
ALTER TABLESPACE dbname_tbs
OWNER TO postgres;

Create a database:

CREATE DATABASE dbname
WITH
OWNER = postgres
ENCODING = 'UTF8'
TABLESPACE = tablespace_name
CONNECTION LIMIT = -1;

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)

REVOKE ALL ON DATABASE dbname FROM public;

Ensure that each PUBLIC schema is unusable to every user:

(This must be run from inside each database)

REVOKE ALL ON SCHEMA public;

Create a user:

CREATE ROLE dbname
WITH
NOLOGIN
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
INHERIT
CONNECTION LIMIT -1
PASSWORD 'xxxxxx';

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

GRANT CONNECT ON DATABASE dbname TO username;
GRANT CREATE ON TABLESPACE tablspacename TO username;
GRANT ALL ON SCHEMA public TO username;
-- From here grant on each object type as necessary
-- Here I have used the ALL keyword,for full usage of GRANT see Postgres documentation
GRANT ALL ON ALL TABLES IN SCHEMA PUBLIC TO username;
GRANT ALL ON ALL SEQUENCES IN SCHEMA PUBLIC TO username;

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

CREATE SCHEMA AUTHORIZATION username;

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:

ALTER DATABASE dbname WITH allow_connections FALSE;