Schemas

Overview

A schema is a namespace that contains named database objects such as tables, views, indexes, data types, functions, stored procedures, and operators. A database can include one or more schemas. Schemas belong to the logical layer of the data model.

Logical layer of ADPG cluster
Logical layer of ADPG cluster
Logical layer of ADPG cluster
Logical layer of ADPG cluster

The distribution of objects in different schemas can be helpful in the following cases:

  • to allow many users to use one database without interfering with each other;

  • to divide objects into logical groups to manage them effectively;

  • put third-party applications into separate schemas, so they do not collide with the names of other objects.

Schemas cannot be nested within each other.

Manage schemas

Use the CREATE SCHEMA command to create a schema:

CREATE SCHEMA schema1;

Schema names beginning with pg_ are reserved for system purposes and cannot be created by users.

To create or access objects in a schema, write a fully qualified name consisting of the schema name and the table name separated by a dot:

CREATE TABLE schema1.table1 (
    column1 serial PRIMARY KEY,
    column2 VARCHAR (50) UNIQUE NOT NULL
);

It is possible to use the same object names in different schemas without conflicts. Create a new schema and add table1 to it:

CREATE SCHEMA schema2;

CREATE TABLE schema2.table1 (
    column1 serial PRIMARY KEY,
    column2 VARCHAR (50) UNIQUE NOT NULL
);

Utilize qualified names to access these tables:

SELECT * FROM schema1.table1;

SELECT * FROM schema2.table1;

You can use the ALTER SCHEMA command to rename a schema or change its owner. For example:

ALTER SCHEMA schema2 RENAME TO myschema;

To drop a schema, execute the DROP SCHEMA command. The schema should be empty for this operation. If you need to delete a schema with all contained objects, use the CASCADE keyword:

DROP SCHEMA myschema CASCADE;

For more information about the deletion of dependent objects, see Dependency tracking.

The default schema

If you create a table or any other object without specifying a schema name, PostgreSQL puts this object into the public schema. Each new database contains this schema and uses it by default. The following code lines are equivalent:

CREATE TABLE books ( ... );

CREATE TABLE public.books ( ... );

The command below returns the current schema:

SELECT current_schema();

The result:

current_schema
----------------
 public
(1 row)

To list all available schemas, use the psql \dn meta-command.

The result:

   List of schemas
   Name    |  Owner
-----------+----------
public     | postgres
schema1    | postgres

Alternatively, select the nspname column from the pg_namespace catalog:

SELECT nspname FROM pg_namespace;

The result:

      nspname
--------------------
 pg_toast
 pg_catalog
 public
 information_schema

In addition to public, the result includes PostgreSQL system schemas. The table below describes them.

System schemas
Name Description

pg_toast

Contains TOAST tables

information_schema

Consists of a set of views with information about the current database objects

pg_catalog

Contains the system tables and all the built-in data types, functions, and operators

The schema search path

In most cases, we refer to tables by unqualified names (without schema names) in queries. The system uses a search path to determine which table should be processed. The search path is a list of schemas to look in. PostgreSQL operates with the first matching table. If there is no match in the search path schemas, an error occurs, even if matching table names exist in other schemas that the search path does not include.

To display the current search path, use the following command:

SHOW search_path;

The result:

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

The first element "$user" refers to a schema with the same name as the current user. If this schema does not exist, PostgreSQL ignores this entry. The second element is the public schema. The first existing schema in the search path is the default location for new objects. That is why objects are created in the public schema by default.

To add a new schema in the search path, use the following command:

SET search_path TO schema1, public;

Since schema1 is the first element in the path, new objects will be created in it.

The search path also works in the same way for data type, function, and operator names.

Note the pg_catalog schema mentioned above is a part of the search path. You can use the current_schemas function to check it. This function returns an array of the schema names from the effective search path, in their priority order. If the boolean argument is true, implicitly-searched system schemas are included in the result.

SELECT current_schemas(true);

The result:

       current_schemas
------------------------------
 {pg_catalog,public}

If pg_catalog is not explicitly included in the search path, PostgreSQL searches in it before inspecting path schemas. This behavior ensures that built-in objects are always be found. However, you can explicitly place pg_catalog at the end of your search path if you want user-defined object names to override built-in names.

Privileges on schemas

Users cannot access any objects in schemas they do not own. To allow that, the schema owner must grant the USAGE privilege on the schema. You may need to grant additional privileges on objects to allow users to manipulate objects in the schema. For example, if you want users to be able to create objects, grant them the CREATE privilege on the schema.

Everyone has CREATE and USAGE privileges on the public schema.

Usage patterns

The Secure schema usage pattern prevents untrusted users from changing queries of other users. When a database does not use this pattern, users who want to securely execute queries need to perform protective operations at the start of each session. They should set the search path to the empty string or remove schemas that are writable by non-superusers from the search path.

Secure schema usage pattern

Constrain ordinary users to user-private schemas and revoke their CREATE privilege from the public schema. To implement this, execute the following command:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Create a schema for each user with their name. Since the default search path starts with $user, which resolves to the user name, each user has a separate schema, and they access their own schemas. When applying this pattern to a database that untrusted users may already be connecting to, check if there are any objects in the public schema with the same names as the objects in the pg_catalog schema.

This pattern is secure unless an untrusted user is the database owner or has the CREATEROLE privilege.

Delete the public schema from the search path

Execute the following command to remove the public schema from the default search path:

ALTER ROLE ALL SET search_path = "$user";

Everyone retains the ability to create objects in the public schema, but only qualified names can be used for these objects. While qualified table references are safe, function calls in the public schema are unsafe and unreliable. If you create functions or extensions in the public schema, use the first pattern instead.

This pattern is secure unless an untrusted user is the database owner or has the CREATEROLE privilege.

The default pattern

If you keep the default settings, all users access the public schema implicitly. This simulates the situation where schemas are not available. However, this is not a secure pattern. It is acceptable only if the database has a single user or a few trusted users.

Found a mistake? Seleсt text and press Ctrl+Enter to report it