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.
A database contains one or more named schemas, which
in turn contain tables. Schemas also contain other kinds of named
objects, including data types, functions, and operators. The same
object name can be used in different schemas without conflict; for
example, both schema1
and myschema
can
contain tables named mytable
. Unlike databases,
schemas are not rigidly separated: a user can access objects in any
of the schemas in the database they are connected to, if they have
privileges to do so.
There are several reasons why one might want to use schemas:
To allow many users to use one database without interfering with each other.
To organize database objects into logical groups to make them more manageable.
Third-party applications can be put into separate schemas so they do not collide with the names of other objects.
Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.
To create a schema, use the CREATE SCHEMA command. Give the schema a name of your choice. For example:
CREATE SCHEMA myschema;
To create or access objects in a schema, write a qualified name consisting of the schema name and table name separated by a dot:
schema
.
table
This works anywhere a table name is expected, including the table modification commands and the data access commands discussed in the following chapters. (For brevity we will speak of tables only, but the same ideas apply to other kinds of named objects, such as types and functions.)
Actually, the even more general syntax
database
.
schema
.
table
can be used too, but at present this is just for pro forma compliance with the SQL standard. If you write a database name, it must be the same as the database you are connected to.
So to create a table in the new schema, use:
CREATE TABLE myschema.mytable ( ... );
To drop a schema if it's empty (all objects in it have been dropped), use:
DROP SCHEMA myschema;
To drop a schema including all contained objects, use:
DROP SCHEMA myschema CASCADE;
See 5.14절 for a description of the general mechanism behind this.
Often you will want to create a schema owned by someone else (since this is one of the ways to restrict the activities of your users to well-defined namespaces). The syntax for that is:
CREATE SCHEMAschema_name
AUTHORIZATIONuser_name
;
You can even omit the schema name, in which case the schema name will be the same as the user name. See 5.9.6절 for how this can be useful.
Schema names beginning with pg_
are reserved for
system purposes and cannot be created by users.
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 ( ... );
and:
CREATE TABLE public.products ( ... );
Qualified names are tedious to write, and it's often best not to wire a particular schema name into applications anyway. Therefore tables are often referred to by unqualified names, which consist of just the table name. The system determines which table is meant by following a search path, which is a list of schemas to look in. The first matching table in the search path is taken to be the one wanted. If there is no match in the search path, an error is reported, even if matching table names exist in other schemas in the database.
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.
The first schema named in the search path is called the current schema.
Aside from being the first schema searched, it is also the schema in
which new tables will be created if the CREATE TABLE
command does not specify a schema name.
To show the current search path, use the following command:
SHOW search_path;
In the default setup this returns:
search_path -------------- "$user", public
The first element specifies that a schema with the same name as the current user is to be searched. If no such schema exists, the entry is ignored. The second element refers to the public schema that we have seen already.
The first schema in the search path that exists is the default location for creating new objects. That is the reason that by default objects are created in the public schema. When objects are referenced in any other context without schema qualification (table modification, data modification, or query commands) the search path is traversed until a matching object is found. Therefore, in the default configuration, any unqualified access again can only refer to the public schema.
To put our new schema in the path, we use:
SET search_path TO myschema,public;
(We omit the $user
here because we have no
immediate need for it.) And then we can access the table without
schema qualification:
DROP TABLE mytable;
Also, since myschema
is the first element in
the path, new objects would by default be created in it.
We could also have written:
SET search_path TO myschema;
Then we no longer have access to the public schema without explicit qualification. There is nothing special about the public schema except that it exists by default. It can be dropped, too.
See also 9.26절 for other ways to manipulate the schema search path.
The search path works in the same way for data type names, function names, and operator names as it does for table names. Data type and function names can be qualified in exactly the same way as table names. If you need to write a qualified operator name in an expression, there is a special provision: you must write
OPERATOR(
schema
.
operator
)
This is needed to avoid syntactic ambiguity. An example is:
SELECT 3 OPERATOR(pg_catalog.+) 4;
In practice one usually relies on the search path for operators, so as not to have to write anything so ugly as that.
By default, users cannot access any objects in schemas they do not
own. To allow that, the owner of the schema must grant the
USAGE
privilege on the schema. To allow users
to make use of the objects in the schema, additional privileges
might need to be granted, as appropriate for the object.
A user can also be allowed to create objects in someone else's
schema. To allow that, the CREATE
privilege on
the schema needs to be granted. Note that by default, everyone
has CREATE
and USAGE
privileges on
the schema
public
. This allows all users that are able to
connect to a given database to create objects in its
public
schema.
Some usage patterns call for
revoking that privilege:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
(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 4.1.1절.)
In addition to public
and user-created schemas, 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. If it is not named explicitly in the path then
it is implicitly searched before searching the path's
schemas. This ensures that built-in names will always be
findable. However, 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.
Since system table names begin with pg_
, it is best to
avoid such names to ensure that you won't suffer a conflict if some
future version defines a system table named the same as your
table. (With the default search path, an unqualified reference to
your table name would then be resolved as the system table instead.)
System tables will continue to follow the convention of having
names beginning with pg_
, so that they will not
conflict with unqualified user-table names so long as users avoid
the pg_
prefix.
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.
For any pattern, to install shared applications (tables to be used by everyone, additional functions provided by third parties, etc.), put them into separate schemas. Remember to grant appropriate privileges to allow the other users to access them. Users can then refer to these additional objects by qualifying the names with a schema name, or they can put the additional schemas into their search path, as they choose.
In the SQL standard, the notion of objects in the same schema
being owned by different users does not exist. Moreover, some
implementations do not allow you to create schemas that have a
different name than their owner. In fact, the concepts of schema
and user are nearly equivalent in a database system that
implements only the basic schema support specified in the
standard. Therefore, many users consider qualified names to
really consist of
.
This is how PostgreSQL will effectively
behave if you create a per-user schema for every user.
user_name
.table_name
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.
Of course, some SQL database systems might not implement schemas at all, or provide namespace support by allowing (possibly limited) cross-database access. If you need to work with those systems, then maximum portability would be achieved by not using schemas at all.