Using Postgres Schemas

By Aaron O. Ellis

Schemas are a shockingly underappreciated feature of relational databases. With minimal overhead, they enable segmentation and isolation of tables within a database.

If you are interested in using schemas with Postgres, I recommend starting with the official documentation’s fantastic introduction. Although I will reiterate some of the introduction below, I will mainly focus on the exceptional behavior and issues that arise when using schemas.

For starters, if you are a Postgres user, then you are already using a schema! By default, Postgres places tables into the public schema. The hierarchy is simple: databases can have multiple schemas, and schemas can have multiple tables:

Database Database Schema Schema Schema Schema Table Table Table Table Table Table Table Table

Or working backwards: a table belongs to one and only one schema and a schema belongs to one and only one database. Different schemas, however, can have tables with the same name:

CREATE SCHEMA "private";
CREATE TABLE "private"."example" (id SERIAL PRIMARY KEY);
CREATE TABLE "public"."example" (id SERIAL PRIMARY KEY);

When querying with the unqualified table name example, precedence will be determined by the user’s search_path, which you can view with SHOW search_path;

       search_path
--------------------------
 "$user", public, private

You can alter the search_path for the current user’s session with:

SET search_path TO "$user", "private", "public";

Note that this is only for the current session. Starting a new session, for instance by re-connecting to the database, will reset the search_path to the user’s default. To permanently set the default search_path, you’ll need to alter the user’s role:

ALTER ROLE postgres SET search_path = "$user", "private", "public";

But the above will only take affect when a new session is started! To set the search_path on the current session and all future sessions you’ll have to run both:

SET search_path TO "$user", "private", "public";
ALTER ROLE postgres SET search_path = "$user", "private", "public";

Additionally, any changes made to search_path in a transaction will be ignored if the transaction is rolled back.

Fully Qualified Names

You can avoid issues with search_path by using a table’s fully qualified name with the schema and tables names separated by a .:

INSERT INTO private.example DEFAULT VALUES;

When quoting names, the schema and table should be quoted separately:

INSERT INTO "private"."example" DEFAULT VALUES;

Quoting the entire qualified name in our current example will error:

INSERT INTO "private.example" DEFAULT VALUES;

ERROR:  relation "private.example" does not exist

Note that . is a perfectly valid character for names as long as it is quoted, but also an endless source of madness:

CREATE TABLE "private"."private.example" (id SERIAL PRIMARY KEY);

You can set a user’s search_path to an empty string to force queries to use these fully qualified names. Note that this includes the \d command and its ilk: you’ll need to either use a wildcard or specify a schema to return results, such as \d public.*.

If you don’t know a table’s schema, a variety of commands will display it, including \dt in the left-most column:

 Schema  | Name    | Type  |  Owner
---------+---------+-------+----------
 private | example | table | postgres
 public  | example | table | postgres

You can also list all schemas in a database with \dn:

  Name   |  Owner
---------+----------
 private | postgres
 public  | postgres

Or query every table in a schema with:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'private';

Privileges

Like all objects in Postgres, schemas will be owned by the role that created them unless otherwise specified. The following will create a schema with a different owner:

CREATE USER website WITH PASSWORD 'secret';
CREATE SCHEMA orm AUTHORIZATION website;

The above will make the website user the owner of the orm schema, allowing it to create objects in that schema. Remember to update the search_path for each user, otherwise commands with unqualified names may return unexpected results.

But just because website is the owner of the orm schema, that doesn’t make it the owner of every object in the schema! When connected as the postgres superuser (or any user other than website that has privileges to create in the orm schema) we can create a table:

CREATE TABLE orm.privileged (id SERIAL PRIMARY KEY);

And the website user will be unable to read from it:

SELECT * FROM orm.privileged;

ERROR:  permission denied for table privileged

Running \dt reveals the issue:

 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 orm    | example       | table | website
 orm    | privileged    | table | postgres

In order to use this new table, the postgres user must grant access to the website user:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA orm TO website;

The above grants all privileges. For specific privileges, please refer to the full list in the Postgres documentation.

We can confirm that these new privileges have been set with the \z command:

 Schema |         Name         |   Type   |     Access privileges     
--------+----------------------+----------+---------------------------
 orm    | privileged           | table    | postgres=arwdDxt/postgres+
        |                      |          | website=arwdDxt/postgres
 orm    | privileged_id_seq    | sequence |

There is still an issue with the website user’s privileges, however, which becomes apparent when we attempt an insert:

INSERT INTO orm.privileged DEFAULT VALUES;

ERROR:  permission denied for sequence privileged_id_seq

As we can see from the output of the \z command above, the website user doesn’t have access to the sequence that generates the primary key! The postgres user also needs to grant access to it with:

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA orm TO website;

And even though we’ve granted all privileges to all tables and sequences currently in the orm schema, any subsequent objects created by the postgres user will need the above commands repeated before they can be accessed by the website user.

Thankfully, Postgres has the following commands that will grant all privileges to the website user for all future tables and sequences created by the postgres user in the orm schema:

ALTER DEFAULT PRIVILEGES FOR USER postgres IN SCHEMA orm GRANT ALL ON TABLES TO website;
ALTER DEFAULT PRIVILEGES FOR USER postgres IN SCHEMA orm GRANT ALL ON SEQUENCES TO website;

Debugging

Schemas are a powerful feature that can provide segmentation and isolation to a database with minimal overhead. Their quirks, however, can cause a variety of frustrating errors, such as users being unable to modify, access, or even know that their tables exist. If you’re starting with schemas, remember:

  • Update your search_path - order matters!
  • Know the owner of the schema and its objects: use the \dn+ and \dt commands
  • Check access privileges for objects with the \z command and update as needed

Happy hacking!