1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>5.9. Schemas</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="ddl-rowsecurity.html" title="5.8. Row Security Policies" /><link rel="next" href="ddl-inherit.html" title="5.10. Inheritance" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">5.9. Schemas</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="ddl-rowsecurity.html" title="5.8. Row Security Policies">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="ddl.html" title="Chapter 5. Data Definition">Up</a></td><th width="60%" align="center">Chapter 5. Data Definition</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="ddl-inherit.html" title="5.10. Inheritance">Next</a></td></tr></table><hr /></div><div class="sect1" id="DDL-SCHEMAS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">5.9. Schemas <a href="#DDL-SCHEMAS" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="ddl-schemas.html#DDL-SCHEMAS-CREATE">5.9.1. Creating a Schema</a></span></dt><dt><span class="sect2"><a href="ddl-schemas.html#DDL-SCHEMAS-PUBLIC">5.9.2. The Public Schema</a></span></dt><dt><span class="sect2"><a href="ddl-schemas.html#DDL-SCHEMAS-PATH">5.9.3. The Schema Search Path</a></span></dt><dt><span class="sect2"><a href="ddl-schemas.html#DDL-SCHEMAS-PRIV">5.9.4. Schemas and Privileges</a></span></dt><dt><span class="sect2"><a href="ddl-schemas.html#DDL-SCHEMAS-CATALOG">5.9.5. The System Catalog Schema</a></span></dt><dt><span class="sect2"><a href="ddl-schemas.html#DDL-SCHEMAS-PATTERNS">5.9.6. Usage Patterns</a></span></dt><dt><span class="sect2"><a href="ddl-schemas.html#DDL-SCHEMAS-PORTABILITY">5.9.7. Portability</a></span></dt></dl></div><a id="id-1.5.4.11.2" class="indexterm"></a><p>
A <span class="productname">PostgreSQL</span> 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.
</p><div class="note"><h3 class="title">Note</h3><p>
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, <code class="literal">joe</code> in two databases
in the same cluster; but the system can be configured to allow
<code class="literal">joe</code> access to only some of the databases.
</p></div><p>
A database contains one or more named <em class="firstterm">schemas</em>, 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 <code class="literal">schema1</code> and <code class="literal">myschema</code> can
contain tables named <code class="literal">mytable</code>. 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.
</p><p>
There are several reasons why one might want to use schemas:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
To allow many users to use one database without interfering with
each other.
</p></li><li class="listitem"><p>
To organize database objects into logical groups to make them
more manageable.
</p></li><li class="listitem"><p>
Third-party applications can be put into separate schemas so
they do not collide with the names of other objects.
</p></li></ul></div><p>
Schemas are analogous to directories at the operating system level,
except that schemas cannot be nested.
</p><div class="sect2" id="DDL-SCHEMAS-CREATE"><div class="titlepage"><div><div><h3 class="title">5.9.1. Creating a Schema <a href="#DDL-SCHEMAS-CREATE" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.11.7.2" class="indexterm"></a><p>
To create a schema, use the <a class="xref" href="sql-createschema.html" title="CREATE SCHEMA"><span class="refentrytitle">CREATE SCHEMA</span></a>
command. Give the schema a name
of your choice. For example:
</p><pre class="programlisting">
CREATE SCHEMA myschema;
</pre><p>
</p><a id="id-1.5.4.11.7.4" class="indexterm"></a><a id="id-1.5.4.11.7.5" class="indexterm"></a><p>
To create or access objects in a schema, write a
<em class="firstterm">qualified name</em> consisting of the schema name and
table name separated by a dot:
</p><pre class="synopsis">
<em class="replaceable"><code>schema</code></em><code class="literal">.</code><em class="replaceable"><code>table</code></em>
</pre><p>
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.)
</p><p>
Actually, the even more general syntax
</p><pre class="synopsis">
<em class="replaceable"><code>database</code></em><code class="literal">.</code><em class="replaceable"><code>schema</code></em><code class="literal">.</code><em class="replaceable"><code>table</code></em>
</pre><p>
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.
</p><p>
So to create a table in the new schema, use:
</p><pre class="programlisting">
CREATE TABLE myschema.mytable (
...
);
</pre><p>
</p><a id="id-1.5.4.11.7.9" class="indexterm"></a><p>
To drop a schema if it's empty (all objects in it have been
dropped), use:
</p><pre class="programlisting">
DROP SCHEMA myschema;
</pre><p>
To drop a schema including all contained objects, use:
</p><pre class="programlisting">
DROP SCHEMA myschema CASCADE;
</pre><p>
See <a class="xref" href="ddl-depend.html" title="5.14. Dependency Tracking">Section 5.14</a> for a description of the general
mechanism behind this.
</p><p>
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:
</p><pre class="programlisting">
CREATE SCHEMA <em class="replaceable"><code>schema_name</code></em> AUTHORIZATION <em class="replaceable"><code>user_name</code></em>;
</pre><p>
You can even omit the schema name, in which case the schema name
will be the same as the user name. See <a class="xref" href="ddl-schemas.html#DDL-SCHEMAS-PATTERNS" title="5.9.6. Usage Patterns">Section 5.9.6</a> for how this can be useful.
</p><p>
Schema names beginning with <code class="literal">pg_</code> are reserved for
system purposes and cannot be created by users.
</p></div><div class="sect2" id="DDL-SCHEMAS-PUBLIC"><div class="titlepage"><div><div><h3 class="title">5.9.2. The Public Schema <a href="#DDL-SCHEMAS-PUBLIC" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.11.8.2" class="indexterm"></a><p>
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 <span class="quote">“<span class="quote">public</span>”</span>. Every new
database contains such a schema. Thus, the following are equivalent:
</p><pre class="programlisting">
CREATE TABLE products ( ... );
</pre><p>
and:
</p><pre class="programlisting">
CREATE TABLE public.products ( ... );
</pre><p>
</p></div><div class="sect2" id="DDL-SCHEMAS-PATH"><div class="titlepage"><div><div><h3 class="title">5.9.3. The Schema Search Path <a href="#DDL-SCHEMAS-PATH" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.11.9.2" class="indexterm"></a><a id="id-1.5.4.11.9.3" class="indexterm"></a><a id="id-1.5.4.11.9.4" class="indexterm"></a><p>
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 <em class="firstterm">unqualified names</em>,
which consist of just the table name. The system determines which table
is meant by following a <em class="firstterm">search path</em>, 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.
</p><p>
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 <span class="productname">PostgreSQL</span> internals, adding a schema
to <code class="varname">search_path</code> effectively trusts all users having
<code class="literal">CREATE</code> 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.
</p><a id="id-1.5.4.11.9.7" class="indexterm"></a><p>
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 <code class="command">CREATE TABLE</code>
command does not specify a schema name.
</p><a id="id-1.5.4.11.9.9" class="indexterm"></a><p>
To show the current search path, use the following command:
</p><pre class="programlisting">
SHOW search_path;
</pre><p>
In the default setup this returns:
</p><pre class="screen">
search_path
--------------
"$user", public
</pre><p>
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.
</p><p>
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.
</p><p>
To put our new schema in the path, we use:
</p><pre class="programlisting">
SET search_path TO myschema,public;
</pre><p>
(We omit the <code class="literal">$user</code> here because we have no
immediate need for it.) And then we can access the table without
schema qualification:
</p><pre class="programlisting">
DROP TABLE mytable;
</pre><p>
Also, since <code class="literal">myschema</code> is the first element in
the path, new objects would by default be created in it.
</p><p>
We could also have written:
</p><pre class="programlisting">
SET search_path TO myschema;
</pre><p>
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.
</p><p>
See also <a class="xref" href="functions-info.html" title="9.26. System Information Functions and Operators">Section 9.26</a> for other ways to manipulate
the schema search path.
</p><p>
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
</p><pre class="synopsis">
<code class="literal">OPERATOR(</code><em class="replaceable"><code>schema</code></em><code class="literal">.</code><em class="replaceable"><code>operator</code></em><code class="literal">)</code>
</pre><p>
This is needed to avoid syntactic ambiguity. An example is:
</p><pre class="programlisting">
SELECT 3 OPERATOR(pg_catalog.+) 4;
</pre><p>
In practice one usually relies on the search path for operators,
so as not to have to write anything so ugly as that.
</p></div><div class="sect2" id="DDL-SCHEMAS-PRIV"><div class="titlepage"><div><div><h3 class="title">5.9.4. Schemas and Privileges <a href="#DDL-SCHEMAS-PRIV" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.11.10.2" class="indexterm"></a><p>
By default, users cannot access any objects in schemas they do not
own. To allow that, the owner of the schema must grant the
<code class="literal">USAGE</code> privilege on the schema. By default, everyone
has that privilege on the schema <code class="literal">public</code>. To allow
users to make use of the objects in a schema, additional privileges might
need to be granted, as appropriate for the object.
</p><p>
A user can also be allowed to create objects in someone else's schema. To
allow that, the <code class="literal">CREATE</code> privilege on the schema needs to
be granted. In databases upgraded from
<span class="productname">PostgreSQL</span> 14 or earlier, everyone has that
privilege on the schema <code class="literal">public</code>.
Some <a class="link" href="ddl-schemas.html#DDL-SCHEMAS-PATTERNS" title="5.9.6. Usage Patterns">usage patterns</a> call for
revoking that privilege:
</p><pre class="programlisting">
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
</pre><p>
(The first <span class="quote">“<span class="quote">public</span>”</span> is the schema, the second
<span class="quote">“<span class="quote">public</span>”</span> means <span class="quote">“<span class="quote">every user</span>”</span>. 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 <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS" title="4.1.1. Identifiers and Key Words">Section 4.1.1</a>.)
</p></div><div class="sect2" id="DDL-SCHEMAS-CATALOG"><div class="titlepage"><div><div><h3 class="title">5.9.5. The System Catalog Schema <a href="#DDL-SCHEMAS-CATALOG" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.11.11.2" class="indexterm"></a><p>
In addition to <code class="literal">public</code> and user-created schemas, each
database contains a <code class="literal">pg_catalog</code> schema, which contains
the system tables and all the built-in data types, functions, and
operators. <code class="literal">pg_catalog</code> is always effectively part of
the search path. If it is not named explicitly in the path then
it is implicitly searched <span class="emphasis"><em>before</em></span> searching the path's
schemas. This ensures that built-in names will always be
findable. However, you can explicitly place
<code class="literal">pg_catalog</code> at the end of your search path if you
prefer to have user-defined names override built-in names.
</p><p>
Since system table names begin with <code class="literal">pg_</code>, 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 <code class="literal">pg_</code>, so that they will not
conflict with unqualified user-table names so long as users avoid
the <code class="literal">pg_</code> prefix.
</p></div><div class="sect2" id="DDL-SCHEMAS-PATTERNS"><div class="titlepage"><div><div><h3 class="title">5.9.6. Usage Patterns <a href="#DDL-SCHEMAS-PATTERNS" class="id_link">#</a></h3></div></div></div><p>
Schemas can be used to organize your data in many ways.
A <em class="firstterm">secure schema usage pattern</em> 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 <code class="varname">search_path</code> to the empty string or otherwise
removing schemas that are writable by non-superusers
from <code class="varname">search_path</code>. There are a few usage patterns
easily supported by the default configuration:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
Constrain ordinary users to user-private schemas.
To implement this pattern, first ensure that no schemas have
public <code class="literal">CREATE</code> privileges. Then, for every user
needing to create non-temporary objects, create a schema with the
same name as that user, for example
<code class="literal">CREATE SCHEMA alice AUTHORIZATION alice</code>.
(Recall that the default search path starts
with <code class="literal">$user</code>, which resolves to the user
name. Therefore, if each user has a separate schema, they access
their own schemas by default.) This pattern is a secure schema
usage pattern unless an untrusted user is the database owner or
has been granted <code class="literal">ADMIN OPTION</code> on a relevant role,
in which case no secure schema usage pattern exists.
</p><p>
In <span class="productname">PostgreSQL</span> 15 and later, the default
configuration supports this usage pattern. In prior versions, or
when using a database that has been upgraded from a prior version,
you will need to remove the public <code class="literal">CREATE</code>
privilege from the <code class="literal">public</code> schema (issue
<code class="literal">REVOKE CREATE ON SCHEMA public FROM PUBLIC</code>).
Then consider auditing the <code class="literal">public</code> schema for
objects named like objects in schema <code class="literal">pg_catalog</code>.
</p></li><li class="listitem"><p>
Remove the public schema from the default search path, by modifying
<a class="link" href="config-setting.html#CONFIG-SETTING-CONFIGURATION-FILE" title="20.1.2. Parameter Interaction via the Configuration File"><code class="filename">postgresql.conf</code></a>
or by issuing <code class="literal">ALTER ROLE ALL SET search_path =
"$user"</code>. Then, grant privileges to create in the public
schema. Only qualified names will choose public schema objects. While
qualified table references are fine, calls to functions in the public
schema <a class="link" href="typeconv-func.html" title="10.3. Functions">will be unsafe or
unreliable</a>. 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 has been granted <code class="literal">ADMIN OPTION</code> on a relevant role.
</p></li><li class="listitem"><p>
Keep the default search path, and grant privileges to create in the
public schema. 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. In databases upgraded
from <span class="productname">PostgreSQL</span> 14 or earlier, this is the
default.
</p></li></ul></div><p>
</p><p>
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.
</p></div><div class="sect2" id="DDL-SCHEMAS-PORTABILITY"><div class="titlepage"><div><div><h3 class="title">5.9.7. Portability <a href="#DDL-SCHEMAS-PORTABILITY" class="id_link">#</a></h3></div></div></div><p>
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
<code class="literal"><em class="replaceable"><code>user_name</code></em>.<em class="replaceable"><code>table_name</code></em></code>.
This is how <span class="productname">PostgreSQL</span> will effectively
behave if you create a per-user schema for every user.
</p><p>
Also, there is no concept of a <code class="literal">public</code> schema in the
SQL standard. For maximum conformance to the standard, you should
not use the <code class="literal">public</code> schema.
</p><p>
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.
</p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="ddl-rowsecurity.html" title="5.8. Row Security Policies">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="ddl.html" title="Chapter 5. Data Definition">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="ddl-inherit.html" title="5.10. Inheritance">Next</a></td></tr><tr><td width="40%" align="left" valign="top">5.8. Row Security Policies </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 5.10. Inheritance</td></tr></table></div></body></html>
|
