5.9. Schemas

A PostgreSQL database cluster contains one or more named databases. Roles and a few other object types are shared across the entire cluster. A client connection to the server can only access data in a single database, the one specified in the connection request.


Users of a cluster do not necessarily have the privilege to access every database in the cluster. Sharing of role names means that there cannot be different roles named, say, joe in two databases in the same cluster; but the system can be configured to allow joe access to only some of the databases.


There are several reasons why one might want to use schemas:





5.9.1. Creating a Schema






Actually, the even more general syntax



So to create a table in the new schema, use:

CREATE TABLE myschema.mytable (


DROP SCHEMA myschema;

To drop a schema including all contained objects, use:


See Section 5.14 for a description of the general mechanism behind this.


CREATE SCHEMA schema_name AUTHORIZATION user_name;

You can even omit the schema name, in which case the schema name will be the same as the user name. See Section 5.9.6 for how this can be useful.


5.9.2. The Public Schema

In the previous sections we created tables without specifying any schema names. By default such tables (and other objects) are automatically put into a schema named public. Every new database contains such a schema. Thus, the following are equivalent:

CREATE TABLE products ( ... );


CREATE TABLE public.products ( ... );

5.9.3. The Schema Search Path


The ability to create like-named objects in different schemas complicates writing a query that references precisely the same objects every time. It also opens up the potential for users to change the behavior of other users' queries, maliciously or accidentally. 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.


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

SHOW search_path;

In the default setup this returns:

 "$user", public



To put our new schema in the path, we use:

SET search_path TO myschema,public;


DROP TABLE mytable;


We could also have written:

SET search_path TO myschema;


See also Section 9.26 for other ways to manipulate the schema search path.



This is needed to avoid syntactic ambiguity. An example is:

SELECT 3 OPERATOR(pg_catalog.+) 4;


5.9.4. Schemas and Privileges




(The first public is the schema, the second public means every user. In the first sense it is an identifier, in the second sense it is a key word, hence the different capitalization; recall the guidelines from Section 4.1.1.)

5.9.5. The System Catalog Schema



5.9.6. Usage Patterns

Schemas can be used to organize your data in many ways. A secure schema usage pattern prevents untrusted users from changing the behavior of other users' queries. When a database does not use a secure schema usage pattern, users wishing to securely query that database would take protective action at the beginning of each session. Specifically, they would begin each session by setting search_path to the empty string or otherwise removing non-superuser-writable schemas from search_path. There are a few usage patterns easily supported by the default configuration:

  • Constrain ordinary users to user-private schemas. To implement this, issue REVOKE CREATE ON SCHEMA public FROM PUBLIC, and create a schema for each user with the same name as that user. Recall that the default search path starts with $user, which resolves to the user name. Therefore, if each user has a separate schema, they access their own schemas by default. After adopting this pattern in a database where untrusted users had already logged in, consider auditing the public schema for objects named like objects in schema pg_catalog. This pattern is a secure schema usage pattern unless an untrusted user is the database owner or holds the CREATEROLE privilege, in which case no secure schema usage pattern exists.

  • Remove the public schema from the default search path, by modifying postgresql.conf or by issuing ALTER ROLE ALL SET search_path = "$user". Everyone retains the ability to create objects in the public schema, but only qualified names will choose those objects. While qualified table references are fine, calls to functions in the public schema will be unsafe or unreliable. If you create functions or extensions in the public schema, use the first pattern instead. Otherwise, like the first pattern, this is secure unless an untrusted user is the database owner or holds the CREATEROLE privilege.

  • Keep the default. All users access the public schema implicitly. This simulates the situation where schemas are not available at all, giving a smooth transition from the non-schema-aware world. However, this is never a secure pattern. It is acceptable only when the database has a single user or a few mutually-trusting users.


5.9.7. Portability


Also, there is no concept of a public schema in the SQL standard. For maximum conformance to the standard, you should not use the public schema.
