Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes14kdownloads
ddl-schemas.html329 linesDownload Raw Back to html
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.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>3   A <span class="productname">PostgreSQL</span> database cluster contains4   one or more named databases.  Roles and a few other object types are5   shared across the entire cluster.  A client connection to the server6   can only access data in a single database, the one specified in the7   connection request.8  </p><div class="note"><h3 class="title">Note</h3><p>9    Users of a cluster do not necessarily have the privilege to access every10    database in the cluster.  Sharing of role names means that there11    cannot be different roles named, say, <code class="literal">joe</code> in two databases12    in the same cluster; but the system can be configured to allow13    <code class="literal">joe</code> access to only some of the databases.14   </p></div><p>15   A database contains one or more named <em class="firstterm">schemas</em>, which16   in turn contain tables.  Schemas also contain other kinds of named17   objects, including data types, functions, and operators.  The same18   object name can be used in different schemas without conflict; for19   example, both <code class="literal">schema1</code> and <code class="literal">myschema</code> can20   contain tables named <code class="literal">mytable</code>.  Unlike databases,21   schemas are not rigidly separated: a user can access objects in any22   of the schemas in the database they are connected to, if they have23   privileges to do so.24  </p><p>25   There are several reasons why one might want to use schemas:26 27   </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>28      To allow many users to use one database without interfering with29      each other.30     </p></li><li class="listitem"><p>31      To organize database objects into logical groups to make them32      more manageable.33     </p></li><li class="listitem"><p>34      Third-party applications can be put into separate schemas so35      they do not collide with the names of other objects.36     </p></li></ul></div><p>37 38   Schemas are analogous to directories at the operating system level,39   except that schemas cannot be nested.40  </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>41    To create a schema, use the <a class="xref" href="sql-createschema.html" title="CREATE SCHEMA"><span class="refentrytitle">CREATE SCHEMA</span></a>42    command.  Give the schema a name43    of your choice.  For example:44</p><pre class="programlisting">45CREATE SCHEMA myschema;46</pre><p>47   </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>48    To create or access objects in a schema, write a49    <em class="firstterm">qualified name</em> consisting of the schema name and50    table name separated by a dot:51</p><pre class="synopsis">52<em class="replaceable"><code>schema</code></em><code class="literal">.</code><em class="replaceable"><code>table</code></em>53</pre><p>54    This works anywhere a table name is expected, including the table55    modification commands and the data access commands discussed in56    the following chapters.57    (For brevity we will speak of tables only, but the same ideas apply58    to other kinds of named objects, such as types and functions.)59   </p><p>60    Actually, the even more general syntax61</p><pre class="synopsis">62<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>63</pre><p>64    can be used too, but at present this is just for pro forma65    compliance with the SQL standard.  If you write a database name,66    it must be the same as the database you are connected to.67   </p><p>68    So to create a table in the new schema, use:69</p><pre class="programlisting">70CREATE TABLE myschema.mytable (71 ...72);73</pre><p>74   </p><a id="id-1.5.4.11.7.9" class="indexterm"></a><p>75    To drop a schema if it's empty (all objects in it have been76    dropped), use:77</p><pre class="programlisting">78DROP SCHEMA myschema;79</pre><p>80    To drop a schema including all contained objects, use:81</p><pre class="programlisting">82DROP SCHEMA myschema CASCADE;83</pre><p>84    See <a class="xref" href="ddl-depend.html" title="5.14. Dependency Tracking">Section 5.14</a> for a description of the general85    mechanism behind this.86   </p><p>87    Often you will want to create a schema owned by someone else88    (since this is one of the ways to restrict the activities of your89    users to well-defined namespaces).  The syntax for that is:90</p><pre class="programlisting">91CREATE SCHEMA <em class="replaceable"><code>schema_name</code></em> AUTHORIZATION <em class="replaceable"><code>user_name</code></em>;92</pre><p>93    You can even omit the schema name, in which case the schema name94    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.95   </p><p>96    Schema names beginning with <code class="literal">pg_</code> are reserved for97    system purposes and cannot be created by users.98   </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>99    In the previous sections we created tables without specifying any100    schema names.  By default such tables (and other objects) are101    automatically put into a schema named <span class="quote">“<span class="quote">public</span>”</span>.  Every new102    database contains such a schema.  Thus, the following are equivalent:103</p><pre class="programlisting">104CREATE TABLE products ( ... );105</pre><p>106    and:107</p><pre class="programlisting">108CREATE TABLE public.products ( ... );109</pre><p>110   </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>111    Qualified names are tedious to write, and it's often best not to112    wire a particular schema name into applications anyway.  Therefore113    tables are often referred to by <em class="firstterm">unqualified names</em>,114    which consist of just the table name.  The system determines which table115    is meant by following a <em class="firstterm">search path</em>, which is a list116    of schemas to look in.  The first matching table in the search path117    is taken to be the one wanted.  If there is no match in the search118    path, an error is reported, even if matching table names exist119    in other schemas in the database.120   </p><p>121    The ability to create like-named objects in different schemas complicates122    writing a query that references precisely the same objects every time.  It123    also opens up the potential for users to change the behavior of other124    users' queries, maliciously or accidentally.  Due to the prevalence of125    unqualified names in queries and their use126    in <span class="productname">PostgreSQL</span> internals, adding a schema127    to <code class="varname">search_path</code> effectively trusts all users having128    <code class="literal">CREATE</code> privilege on that schema.  When you run an129    ordinary query, a malicious user able to create objects in a schema of130    your search path can take control and execute arbitrary SQL functions as131    though you executed them.132   </p><a id="id-1.5.4.11.9.7" class="indexterm"></a><p>133    The first schema named in the search path is called the current schema.134    Aside from being the first schema searched, it is also the schema in135    which new tables will be created if the <code class="command">CREATE TABLE</code>136    command does not specify a schema name.137   </p><a id="id-1.5.4.11.9.9" class="indexterm"></a><p>138    To show the current search path, use the following command:139</p><pre class="programlisting">140SHOW search_path;141</pre><p>142    In the default setup this returns:143</p><pre class="screen">144 search_path145--------------146 "$user", public147</pre><p>148    The first element specifies that a schema with the same name as149    the current user is to be searched.  If no such schema exists,150    the entry is ignored.  The second element refers to the151    public schema that we have seen already.152   </p><p>153    The first schema in the search path that exists is the default154    location for creating new objects.  That is the reason that by155    default objects are created in the public schema.  When objects156    are referenced in any other context without schema qualification157    (table modification, data modification, or query commands) the158    search path is traversed until a matching object is found.159    Therefore, in the default configuration, any unqualified access160    again can only refer to the public schema.161   </p><p>162    To put our new schema in the path, we use:163</p><pre class="programlisting">164SET search_path TO myschema,public;165</pre><p>166    (We omit the <code class="literal">$user</code> here because we have no167    immediate need for it.)  And then we can access the table without168    schema qualification:169</p><pre class="programlisting">170DROP TABLE mytable;171</pre><p>172    Also, since <code class="literal">myschema</code> is the first element in173    the path, new objects would by default be created in it.174   </p><p>175    We could also have written:176</p><pre class="programlisting">177SET search_path TO myschema;178</pre><p>179    Then we no longer have access to the public schema without180    explicit qualification.  There is nothing special about the public181    schema except that it exists by default.  It can be dropped, too.182   </p><p>183    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 manipulate184    the schema search path.185   </p><p>186    The search path works in the same way for data type names, function names,187    and operator names as it does for table names.  Data type and function188    names can be qualified in exactly the same way as table names.  If you189    need to write a qualified operator name in an expression, there is a190    special provision: you must write191</p><pre class="synopsis">192<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>193</pre><p>194    This is needed to avoid syntactic ambiguity.  An example is:195</p><pre class="programlisting">196SELECT 3 OPERATOR(pg_catalog.+) 4;197</pre><p>198    In practice one usually relies on the search path for operators,199    so as not to have to write anything so ugly as that.200   </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>201    By default, users cannot access any objects in schemas they do not202    own.  To allow that, the owner of the schema must grant the203    <code class="literal">USAGE</code> privilege on the schema.  By default, everyone204    has that privilege on the schema <code class="literal">public</code>.  To allow205    users to make use of the objects in a schema, additional privileges might206    need to be granted, as appropriate for the object.207   </p><p>208    A user can also be allowed to create objects in someone else's schema.  To209    allow that, the <code class="literal">CREATE</code> privilege on the schema needs to210    be granted.  In databases upgraded from211    <span class="productname">PostgreSQL</span> 14 or earlier, everyone has that212    privilege on the schema <code class="literal">public</code>.213    Some <a class="link" href="ddl-schemas.html#DDL-SCHEMAS-PATTERNS" title="5.9.6. Usage Patterns">usage patterns</a> call for214    revoking that privilege:215</p><pre class="programlisting">216REVOKE CREATE ON SCHEMA public FROM PUBLIC;217</pre><p>218    (The first <span class="quote">“<span class="quote">public</span>”</span> is the schema, the second219    <span class="quote">“<span class="quote">public</span>”</span> means <span class="quote">“<span class="quote">every user</span>”</span>.  In the220    first sense it is an identifier, in the second sense it is a221    key word, hence the different capitalization; recall the222    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>.)223   </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>224    In addition to <code class="literal">public</code> and user-created schemas, each225    database contains a <code class="literal">pg_catalog</code> schema, which contains226    the system tables and all the built-in data types, functions, and227    operators.  <code class="literal">pg_catalog</code> is always effectively part of228    the search path.  If it is not named explicitly in the path then229    it is implicitly searched <span class="emphasis"><em>before</em></span> searching the path's230    schemas.  This ensures that built-in names will always be231    findable.  However, you can explicitly place232    <code class="literal">pg_catalog</code> at the end of your search path if you233    prefer to have user-defined names override built-in names.234   </p><p>235    Since system table names begin with <code class="literal">pg_</code>, it is best to236    avoid such names to ensure that you won't suffer a conflict if some237    future version defines a system table named the same as your238    table.  (With the default search path, an unqualified reference to239    your table name would then be resolved as the system table instead.)240    System tables will continue to follow the convention of having241    names beginning with <code class="literal">pg_</code>, so that they will not242    conflict with unqualified user-table names so long as users avoid243    the <code class="literal">pg_</code> prefix.244   </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>245    Schemas can be used to organize your data in many ways.246    A <em class="firstterm">secure schema usage pattern</em> prevents untrusted247    users from changing the behavior of other users' queries.  When a database248    does not use a secure schema usage pattern, users wishing to securely249    query that database would take protective action at the beginning of each250    session.  Specifically, they would begin each session by251    setting <code class="varname">search_path</code> to the empty string or otherwise252    removing schemas that are writable by non-superusers253    from <code class="varname">search_path</code>.  There are a few usage patterns254    easily supported by the default configuration:255    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>256       Constrain ordinary users to user-private schemas.257       To implement this pattern, first ensure that no schemas have258       public <code class="literal">CREATE</code> privileges.  Then, for every user259       needing to create non-temporary objects, create a schema with the260       same name as that user, for example261       <code class="literal">CREATE SCHEMA alice AUTHORIZATION alice</code>.262       (Recall that the default search path starts263       with <code class="literal">$user</code>, which resolves to the user264       name. Therefore, if each user has a separate schema, they access265       their own schemas by default.)  This pattern is a secure schema266       usage pattern unless an untrusted user is the database owner or267       has been granted <code class="literal">ADMIN OPTION</code> on a relevant role,268       in which case no secure schema usage pattern exists.269      </p><p>270       In <span class="productname">PostgreSQL</span> 15 and later, the default271       configuration supports this usage pattern.  In prior versions, or272       when using a database that has been upgraded from a prior version,273       you will need to remove the public <code class="literal">CREATE</code>274       privilege from the <code class="literal">public</code> schema (issue275       <code class="literal">REVOKE CREATE ON SCHEMA public FROM PUBLIC</code>).276       Then consider auditing the <code class="literal">public</code> schema for277       objects named like objects in schema <code class="literal">pg_catalog</code>.278      </p></li><li class="listitem"><p>279       Remove the public schema from the default search path, by modifying280       <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>281       or by issuing <code class="literal">ALTER ROLE ALL SET search_path =282       "$user"</code>.  Then, grant privileges to create in the public283       schema.  Only qualified names will choose public schema objects.  While284       qualified table references are fine, calls to functions in the public285       schema <a class="link" href="typeconv-func.html" title="10.3. Functions">will be unsafe or286       unreliable</a>.  If you create functions or extensions in the public287       schema, use the first pattern instead.  Otherwise, like the first288       pattern, this is secure unless an untrusted user is the database owner289       or has been granted <code class="literal">ADMIN OPTION</code> on a relevant role.290      </p></li><li class="listitem"><p>291       Keep the default search path, and grant privileges to create in the292       public schema.  All users access the public schema implicitly.  This293       simulates the situation where schemas are not available at all, giving294       a smooth transition from the non-schema-aware world.  However, this is295       never a secure pattern.  It is acceptable only when the database has a296       single user or a few mutually-trusting users.  In databases upgraded297       from <span class="productname">PostgreSQL</span> 14 or earlier, this is the298       default.299      </p></li></ul></div><p>300   </p><p>301    For any pattern, to install shared applications (tables to be used by302    everyone, additional functions provided by third parties, etc.), put them303    into separate schemas.  Remember to grant appropriate privileges to allow304    the other users to access them.  Users can then refer to these additional305    objects by qualifying the names with a schema name, or they can put the306    additional schemas into their search path, as they choose.307   </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>308    In the SQL standard, the notion of objects in the same schema309    being owned by different users does not exist.  Moreover, some310    implementations do not allow you to create schemas that have a311    different name than their owner.  In fact, the concepts of schema312    and user are nearly equivalent in a database system that313    implements only the basic schema support specified in the314    standard.  Therefore, many users consider qualified names to315    really consist of316    <code class="literal"><em class="replaceable"><code>user_name</code></em>.<em class="replaceable"><code>table_name</code></em></code>.317    This is how <span class="productname">PostgreSQL</span> will effectively318    behave if you create a per-user schema for every user.319   </p><p>320    Also, there is no concept of a <code class="literal">public</code> schema in the321    SQL standard.  For maximum conformance to the standard, you should322    not use the <code class="literal">public</code> schema.323   </p><p>324    Of course, some SQL database systems might not implement schemas325    at all, or provide namespace support by allowing (possibly326    limited) cross-database access.  If you need to work with those327    systems, then maximum portability would be achieved by not using328    schemas at all.329   </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>
codekingpro/portable-devtools · Team Ai