1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
2 <!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.10. 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.9. Row Security Policies" /><link rel="next" href="ddl-inherit.html" title="5.11. 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.10. Schemas</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="ddl-rowsecurity.html" title="5.9. 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 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="ddl-inherit.html" title="5.11. 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.10. 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.10.1. Creating a Schema</a></span></dt><dt><span class="sect2"><a href="ddl-schemas.html#DDL-SCHEMAS-PUBLIC">5.10.2. The Public Schema</a></span></dt><dt><span class="sect2"><a href="ddl-schemas.html#DDL-SCHEMAS-PATH">5.10.3. The Schema Search Path</a></span></dt><dt><span class="sect2"><a href="ddl-schemas.html#DDL-SCHEMAS-PRIV">5.10.4. Schemas and Privileges</a></span></dt><dt><span class="sect2"><a href="ddl-schemas.html#DDL-SCHEMAS-CATALOG">5.10.5. The System Catalog Schema</a></span></dt><dt><span class="sect2"><a href="ddl-schemas.html#DDL-SCHEMAS-PATTERNS">5.10.6. Usage Patterns</a></span></dt><dt><span class="sect2"><a href="ddl-schemas.html#DDL-SCHEMAS-PORTABILITY">5.10.7. Portability</a></span></dt></dl></div><a id="id-1.5.4.12.2" class="indexterm"></a><p>
3 A <span class="productname">PostgreSQL</span> database cluster contains
4 one or more named databases. Roles and a few other object types are
5 shared across the entire cluster. A client connection to the server
6 can only access data in a single database, the one specified in the
8 </p><div class="note"><h3 class="title">Note</h3><p>
9 Users of a cluster do not necessarily have the privilege to access every
10 database in the cluster. Sharing of role names means that there
11 cannot be different roles named, say, <code class="literal">joe</code> in two databases
12 in the same cluster; but the system can be configured to allow
13 <code class="literal">joe</code> access to only some of the databases.
15 A database contains one or more named <em class="firstterm">schemas</em>, which
16 in turn contain tables. Schemas also contain other kinds of named
17 objects, including data types, functions, and operators. Within one
18 schema, two objects of the same type cannot have the same name.
19 Furthermore, tables, sequences, indexes, views, materialized views, and
20 foreign tables share the same namespace, so that, for example, an index and
21 a table must have different names if they are in the same schema. The same
22 object name can be used in different schemas without conflict; for
23 example, both <code class="literal">schema1</code> and <code class="literal">myschema</code> can
24 contain tables named <code class="literal">mytable</code>. Unlike databases,
25 schemas are not rigidly separated: a user can access objects in any
26 of the schemas in the database they are connected to, if they have
29 There are several reasons why one might want to use schemas:
31 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
32 To allow many users to use one database without interfering with
34 </p></li><li class="listitem"><p>
35 To organize database objects into logical groups to make them
37 </p></li><li class="listitem"><p>
38 Third-party applications can be put into separate schemas so
39 they do not collide with the names of other objects.
40 </p></li></ul></div><p>
42 Schemas are analogous to directories at the operating system level,
43 except that schemas cannot be nested.
44 </p><div class="sect2" id="DDL-SCHEMAS-CREATE"><div class="titlepage"><div><div><h3 class="title">5.10.1. Creating a Schema <a href="#DDL-SCHEMAS-CREATE" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.12.7.2" class="indexterm"></a><p>
45 To create a schema, use the <a class="xref" href="sql-createschema.html" title="CREATE SCHEMA"><span class="refentrytitle">CREATE SCHEMA</span></a>
46 command. Give the schema a name
47 of your choice. For example:
48 </p><pre class="programlisting">
49 CREATE SCHEMA myschema;
51 </p><a id="id-1.5.4.12.7.4" class="indexterm"></a><a id="id-1.5.4.12.7.5" class="indexterm"></a><p>
52 To create or access objects in a schema, write a
53 <em class="firstterm">qualified name</em> consisting of the schema name and
54 table name separated by a dot:
55 </p><pre class="synopsis">
56 <em class="replaceable"><code>schema</code></em><code class="literal">.</code><em class="replaceable"><code>table</code></em>
58 This works anywhere a table name is expected, including the table
59 modification commands and the data access commands discussed in
60 the following chapters.
61 (For brevity we will speak of tables only, but the same ideas apply
62 to other kinds of named objects, such as types and functions.)
64 Actually, the even more general syntax
65 </p><pre class="synopsis">
66 <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>
68 can be used too, but at present this is just for pro forma
69 compliance with the SQL standard. If you write a database name,
70 it must be the same as the database you are connected to.
72 So to create a table in the new schema, use:
73 </p><pre class="programlisting">
74 CREATE TABLE myschema.mytable (
78 </p><a id="id-1.5.4.12.7.9" class="indexterm"></a><p>
79 To drop a schema if it's empty (all objects in it have been
81 </p><pre class="programlisting">
84 To drop a schema including all contained objects, use:
85 </p><pre class="programlisting">
86 DROP SCHEMA myschema CASCADE;
88 See <a class="xref" href="ddl-depend.html" title="5.15. Dependency Tracking">Section 5.15</a> for a description of the general
89 mechanism behind this.
91 Often you will want to create a schema owned by someone else
92 (since this is one of the ways to restrict the activities of your
93 users to well-defined namespaces). The syntax for that is:
94 </p><pre class="programlisting">
95 CREATE SCHEMA <em class="replaceable"><code>schema_name</code></em> AUTHORIZATION <em class="replaceable"><code>user_name</code></em>;
97 You can even omit the schema name, in which case the schema name
98 will be the same as the user name. See <a class="xref" href="ddl-schemas.html#DDL-SCHEMAS-PATTERNS" title="5.10.6. Usage Patterns">Section 5.10.6</a> for how this can be useful.
100 Schema names beginning with <code class="literal">pg_</code> are reserved for
101 system purposes and cannot be created by users.
102 </p></div><div class="sect2" id="DDL-SCHEMAS-PUBLIC"><div class="titlepage"><div><div><h3 class="title">5.10.2. The Public Schema <a href="#DDL-SCHEMAS-PUBLIC" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.12.8.2" class="indexterm"></a><p>
103 In the previous sections we created tables without specifying any
104 schema names. By default such tables (and other objects) are
105 automatically put into a schema named <span class="quote">“<span class="quote">public</span>”</span>. Every new
106 database contains such a schema. Thus, the following are equivalent:
107 </p><pre class="programlisting">
108 CREATE TABLE products ( ... );
111 </p><pre class="programlisting">
112 CREATE TABLE public.products ( ... );
114 </p></div><div class="sect2" id="DDL-SCHEMAS-PATH"><div class="titlepage"><div><div><h3 class="title">5.10.3. The Schema Search Path <a href="#DDL-SCHEMAS-PATH" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.12.9.2" class="indexterm"></a><a id="id-1.5.4.12.9.3" class="indexterm"></a><a id="id-1.5.4.12.9.4" class="indexterm"></a><p>
115 Qualified names are tedious to write, and it's often best not to
116 wire a particular schema name into applications anyway. Therefore
117 tables are often referred to by <em class="firstterm">unqualified names</em>,
118 which consist of just the table name. The system determines which table
119 is meant by following a <em class="firstterm">search path</em>, which is a list
120 of schemas to look in. The first matching table in the search path
121 is taken to be the one wanted. If there is no match in the search
122 path, an error is reported, even if matching table names exist
123 in other schemas in the database.
125 The ability to create like-named objects in different schemas complicates
126 writing a query that references precisely the same objects every time. It
127 also opens up the potential for users to change the behavior of other
128 users' queries, maliciously or accidentally. Due to the prevalence of
129 unqualified names in queries and their use
130 in <span class="productname">PostgreSQL</span> internals, adding a schema
131 to <code class="varname">search_path</code> effectively trusts all users having
132 <code class="literal">CREATE</code> privilege on that schema. When you run an
133 ordinary query, a malicious user able to create objects in a schema of
134 your search path can take control and execute arbitrary SQL functions as
135 though you executed them.
136 </p><a id="id-1.5.4.12.9.7" class="indexterm"></a><p>
137 The first schema named in the search path is called the current schema.
138 Aside from being the first schema searched, it is also the schema in
139 which new tables will be created if the <code class="command">CREATE TABLE</code>
140 command does not specify a schema name.
141 </p><a id="id-1.5.4.12.9.9" class="indexterm"></a><p>
142 To show the current search path, use the following command:
143 </p><pre class="programlisting">
146 In the default setup this returns:
147 </p><pre class="screen">
152 The first element specifies that a schema with the same name as
153 the current user is to be searched. If no such schema exists,
154 the entry is ignored. The second element refers to the
155 public schema that we have seen already.
157 The first schema in the search path that exists is the default
158 location for creating new objects. That is the reason that by
159 default objects are created in the public schema. When objects
160 are referenced in any other context without schema qualification
161 (table modification, data modification, or query commands) the
162 search path is traversed until a matching object is found.
163 Therefore, in the default configuration, any unqualified access
164 again can only refer to the public schema.
166 To put our new schema in the path, we use:
167 </p><pre class="programlisting">
168 SET search_path TO myschema,public;
170 (We omit the <code class="literal">$user</code> here because we have no
171 immediate need for it.) And then we can access the table without
172 schema qualification:
173 </p><pre class="programlisting">
176 Also, since <code class="literal">myschema</code> is the first element in
177 the path, new objects would by default be created in it.
179 We could also have written:
180 </p><pre class="programlisting">
181 SET search_path TO myschema;
183 Then we no longer have access to the public schema without
184 explicit qualification. There is nothing special about the public
185 schema except that it exists by default. It can be dropped, too.
187 See also <a class="xref" href="functions-info.html" title="9.27. System Information Functions and Operators">Section 9.27</a> for other ways to manipulate
188 the schema search path.
190 The search path works in the same way for data type names, function names,
191 and operator names as it does for table names. Data type and function
192 names can be qualified in exactly the same way as table names. If you
193 need to write a qualified operator name in an expression, there is a
194 special provision: you must write
195 </p><pre class="synopsis">
196 <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>
198 This is needed to avoid syntactic ambiguity. An example is:
199 </p><pre class="programlisting">
200 SELECT 3 OPERATOR(pg_catalog.+) 4;
202 In practice one usually relies on the search path for operators,
203 so as not to have to write anything so ugly as that.
204 </p></div><div class="sect2" id="DDL-SCHEMAS-PRIV"><div class="titlepage"><div><div><h3 class="title">5.10.4. Schemas and Privileges <a href="#DDL-SCHEMAS-PRIV" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.12.10.2" class="indexterm"></a><p>
205 By default, users cannot access any objects in schemas they do not
206 own. To allow that, the owner of the schema must grant the
207 <code class="literal">USAGE</code> privilege on the schema. By default, everyone
208 has that privilege on the schema <code class="literal">public</code>. To allow
209 users to make use of the objects in a schema, additional privileges might
210 need to be granted, as appropriate for the object.
212 A user can also be allowed to create objects in someone else's schema. To
213 allow that, the <code class="literal">CREATE</code> privilege on the schema needs to
214 be granted. In databases upgraded from
215 <span class="productname">PostgreSQL</span> 14 or earlier, everyone has that
216 privilege on the schema <code class="literal">public</code>.
217 Some <a class="link" href="ddl-schemas.html#DDL-SCHEMAS-PATTERNS" title="5.10.6. Usage Patterns">usage patterns</a> call for
218 revoking that privilege:
219 </p><pre class="programlisting">
220 REVOKE CREATE ON SCHEMA public FROM PUBLIC;
222 (The first <span class="quote">“<span class="quote">public</span>”</span> is the schema, the second
223 <span class="quote">“<span class="quote">public</span>”</span> means <span class="quote">“<span class="quote">every user</span>”</span>. In the
224 first sense it is an identifier, in the second sense it is a
225 key word, hence the different capitalization; recall the
226 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>.)
227 </p></div><div class="sect2" id="DDL-SCHEMAS-CATALOG"><div class="titlepage"><div><div><h3 class="title">5.10.5. The System Catalog Schema <a href="#DDL-SCHEMAS-CATALOG" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.12.11.2" class="indexterm"></a><p>
228 In addition to <code class="literal">public</code> and user-created schemas, each
229 database contains a <code class="literal">pg_catalog</code> schema, which contains
230 the system tables and all the built-in data types, functions, and
231 operators. <code class="literal">pg_catalog</code> is always effectively part of
232 the search path. If it is not named explicitly in the path then
233 it is implicitly searched <span class="emphasis"><em>before</em></span> searching the path's
234 schemas. This ensures that built-in names will always be
235 findable. However, you can explicitly place
236 <code class="literal">pg_catalog</code> at the end of your search path if you
237 prefer to have user-defined names override built-in names.
239 Since system table names begin with <code class="literal">pg_</code>, it is best to
240 avoid such names to ensure that you won't suffer a conflict if some
241 future version defines a system table named the same as your
242 table. (With the default search path, an unqualified reference to
243 your table name would then be resolved as the system table instead.)
244 System tables will continue to follow the convention of having
245 names beginning with <code class="literal">pg_</code>, so that they will not
246 conflict with unqualified user-table names so long as users avoid
247 the <code class="literal">pg_</code> prefix.
248 </p></div><div class="sect2" id="DDL-SCHEMAS-PATTERNS"><div class="titlepage"><div><div><h3 class="title">5.10.6. Usage Patterns <a href="#DDL-SCHEMAS-PATTERNS" class="id_link">#</a></h3></div></div></div><p>
249 Schemas can be used to organize your data in many ways.
250 A <em class="firstterm">secure schema usage pattern</em> prevents untrusted
251 users from changing the behavior of other users' queries. When a database
252 does not use a secure schema usage pattern, users wishing to securely
253 query that database would take protective action at the beginning of each
254 session. Specifically, they would begin each session by
255 setting <code class="varname">search_path</code> to the empty string or otherwise
256 removing schemas that are writable by non-superusers
257 from <code class="varname">search_path</code>. There are a few usage patterns
258 easily supported by the default configuration:
259 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
260 Constrain ordinary users to user-private schemas.
261 To implement this pattern, first ensure that no schemas have
262 public <code class="literal">CREATE</code> privileges. Then, for every user
263 needing to create non-temporary objects, create a schema with the
264 same name as that user, for example
265 <code class="literal">CREATE SCHEMA alice AUTHORIZATION alice</code>.
266 (Recall that the default search path starts
267 with <code class="literal">$user</code>, which resolves to the user
268 name. Therefore, if each user has a separate schema, they access
269 their own schemas by default.) This pattern is a secure schema
270 usage pattern unless an untrusted user is the database owner or
271 has been granted <code class="literal">ADMIN OPTION</code> on a relevant role,
272 in which case no secure schema usage pattern exists.
274 In <span class="productname">PostgreSQL</span> 15 and later, the default
275 configuration supports this usage pattern. In prior versions, or
276 when using a database that has been upgraded from a prior version,
277 you will need to remove the public <code class="literal">CREATE</code>
278 privilege from the <code class="literal">public</code> schema (issue
279 <code class="literal">REVOKE CREATE ON SCHEMA public FROM PUBLIC</code>).
280 Then consider auditing the <code class="literal">public</code> schema for
281 objects named like objects in schema <code class="literal">pg_catalog</code>.
282 </p></li><li class="listitem"><p>
283 Remove the public schema from the default search path, by modifying
284 <a class="link" href="config-setting.html#CONFIG-SETTING-CONFIGURATION-FILE" title="19.1.2. Parameter Interaction via the Configuration File"><code class="filename">postgresql.conf</code></a>
285 or by issuing <code class="literal">ALTER ROLE ALL SET search_path =
286 "$user"</code>. Then, grant privileges to create in the public
287 schema. Only qualified names will choose public schema objects. While
288 qualified table references are fine, calls to functions in the public
289 schema <a class="link" href="typeconv-func.html" title="10.3. Functions">will be unsafe or
290 unreliable</a>. If you create functions or extensions in the public
291 schema, use the first pattern instead. Otherwise, like the first
292 pattern, this is secure unless an untrusted user is the database owner
293 or has been granted <code class="literal">ADMIN OPTION</code> on a relevant role.
294 </p></li><li class="listitem"><p>
295 Keep the default search path, and grant privileges to create in the
296 public schema. All users access the public schema implicitly. This
297 simulates the situation where schemas are not available at all, giving
298 a smooth transition from the non-schema-aware world. However, this is
299 never a secure pattern. It is acceptable only when the database has a
300 single user or a few mutually-trusting users. In databases upgraded
301 from <span class="productname">PostgreSQL</span> 14 or earlier, this is the
303 </p></li></ul></div><p>
305 For any pattern, to install shared applications (tables to be used by
306 everyone, additional functions provided by third parties, etc.), put them
307 into separate schemas. Remember to grant appropriate privileges to allow
308 the other users to access them. Users can then refer to these additional
309 objects by qualifying the names with a schema name, or they can put the
310 additional schemas into their search path, as they choose.
311 </p></div><div class="sect2" id="DDL-SCHEMAS-PORTABILITY"><div class="titlepage"><div><div><h3 class="title">5.10.7. Portability <a href="#DDL-SCHEMAS-PORTABILITY" class="id_link">#</a></h3></div></div></div><p>
312 In the SQL standard, the notion of objects in the same schema
313 being owned by different users does not exist. Moreover, some
314 implementations do not allow you to create schemas that have a
315 different name than their owner. In fact, the concepts of schema
316 and user are nearly equivalent in a database system that
317 implements only the basic schema support specified in the
318 standard. Therefore, many users consider qualified names to
320 <code class="literal"><em class="replaceable"><code>user_name</code></em>.<em class="replaceable"><code>table_name</code></em></code>.
321 This is how <span class="productname">PostgreSQL</span> will effectively
322 behave if you create a per-user schema for every user.
324 Also, there is no concept of a <code class="literal">public</code> schema in the
325 SQL standard. For maximum conformance to the standard, you should
326 not use the <code class="literal">public</code> schema.
328 Of course, some SQL database systems might not implement schemas
329 at all, or provide namespace support by allowing (possibly
330 limited) cross-database access. If you need to work with those
331 systems, then maximum portability would be achieved by not using
333 </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.9. 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.11. Inheritance">Next</a></td></tr><tr><td width="40%" align="left" valign="top">5.9. Row Security Policies </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 5.11. Inheritance</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob