Schemas#

A database contains one or more schemas, which in turn contain tables, data types, functions, operators and other named objects. You can use the same object name in different schemas without conflict.

A client connection to the postgres server can only access a single database, specified in the connection request. But users can access objects in any of the schemas in the database, if granted privileges.

Use cases:

  • To have multiple users in a database, without interference.

  • To organise database objects into logical groups.

  • To avoid name collisions i.e. from 3rd-party apps.

Schemas are analogous to directories in file-systems, but can’t be nested.

Creating a Schema#

Use the CREATE SCHEMA command with the desired schema name:

mydb=> CREATE SCHEMA services;
CREATE SCHEMA

The schema name can’t start with pg_ (system reserved). Omitting the schema name sets it same as the current user name.

You can create a schema owned by someone else e.g. to restrict user activities to well-defined namespaces:

CREATE SCHEMA some_schema AUTHORIZATION some_user;

To create or access objects in a specific schema, write a a qualified name e.g. schema.object_name, database.schema.object_name.

mydb=> CREATE TABLE services.deliveries (
mydb(>   transaction_id     text,
mydb(>   recepient_address  text,
mydb(>   date_dispatched    date,
mydb(>   completed_at       timestamp
mydb(> );
CREATE TABLE
mydb=> INSERT INTO services.deliveries (transaction_id, recepient_address, date_dispatched, completed_at) 
mydb->   VALUES ('12345', '678, abc way', '2023-08-10', '2023-08-10 15:06');
INSERT 0 1

Deleting a Schema#

DROP SCHEMA some_schema;  -- if it's empty
DROP SCHEMA some_schema CASCADE;  -- drop all contained objects as well

The Schema Search Path#

Database objects are often referred to by unqualified names for convenience. The system determines which object is meant by following a search_path - a list of schemas to look in. Then the first matching object is taken.

The first schema in search_path is the current schema. It is searched first.

mydb=> SHOW search_path;
   search_path   
-----------------
 "$user", public
(1 row)

$user refers to the schema with the current user’s name. If it doesn’t exist (default), it’s ignored.

An error is raised if no match is found, even if the object exists in some other schema.

mydb=> SELECT * FROM deliveries;  -- schema "services" not in search path
ERROR:  relation "deliveries" does not exist
LINE 1: SELECT * FROM deliveries;
                      ^
mydb=> SELECT * FROM services.deliveries;  -- schema-qualified name needed for now
 transaction_id | recepient_address | date_dispatched |    completed_at     
----------------+-------------------+-----------------+---------------------
 12345          | 678, abc way      | 2023-08-10      | 2023-08-10 15:06:00
(1 row)

You can edit the schema search path with:

mydb=> SET search_path TO services,public;  -- search in services schema first
SET
mydb=> SELECT * FROM deliveries;
 transaction_id | recepient_address | date_dispatched |    completed_at     
----------------+-------------------+-----------------+---------------------
 12345          | 678, abc way      | 2023-08-10      | 2023-08-10 15:06:00
(1 row)

The first schema that exists is the default location for creating new objects. This is why most objects are by default created in the public schema. There is nothing special about the public schema except that it exists by default. It can be dropped, too.

Important

In the SQL standard:

  • There is no concept of a public schema. For maximum conformance to the standard, you should not use the public schema.

  • The notion of objects in the same schema being owned by different users does not exist.

Caution

Due to the prevalence of unqualified names in queries and their use in PostgreSQL internals, adding a schema to search_path effectively trusts all users having CREATE privilege on that schema.

When you run an ordinary query, a malicious user able to create objects in a schema of your search path can take control and execute arbitrary SQL functions as though you executed them.

Privileges#

Users can’t access any objects in schemas they don’t own (default), unless they’re granted the USAGE privilege. The CREATE privilege is needed to create new objects.

For PostgreSQL 14 and below, all users have CREATE and USAGE privileges on the schema public. These can be revoked with:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE USAGE ON SCHEMA public FROM PUBLIC;

The System Catalog Schema#

Each database contains a pg_catalog schema, which contains the system tables and all the built-in data types, functions, and operators.

pg_catalog is always effectively part of the search path, to ensure built-in names are always findable. You can explicitly place pg_catalog at the end of your search path if you prefer to have user-defined names override built-in names.

Usage Patterns#

A secure schema usage pattern prevents untrusted users from meddling with others’ queries.

Options include:

  • Constrain ordinary users to user-private schemas.

    • REVOKE CREATE ON SCHEMA public FROM PUBLIC.

    • Create a separate schema for each user, with the user’s name so it’s first in search_path ($user).

    • Secure unless untrusted user is the database owner or holds the CREATEROLE privilege.

  • Remove public schema from the default search path.

    • Modify postgresql.conf or use ALTER ROLE ALL SET search_path = $user.

    • Now users must use qualified names to access/create objects in public.

    • Calls to functions in public are still unsafe.

    • Also secure unless untrusted user is the database owner or holds the CREATEROLE privilege.

In any case, to install shared applications (tables for everyone, additional functions from 3rd-parties, …), put them in separate schemas, and grant appropriate privileges.

Tip

A quick hack to secure your queries would be to set search_path to '', or otherwise remove schemas other non-superusers can write to.