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.
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.
Name | Description |
---|---|
pg_toast |
Contains TOAST tables |
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.