diff options
| -rw-r--r-- | doc/src/sgml/ddl.sgml | 80 |
1 files changed, 40 insertions, 40 deletions
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 0be07747489..3546e390a8f 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -3010,56 +3010,57 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC; <title>Usage Patterns</title> <para> - Schemas can be used to organize your data in many ways. There are a few - usage patterns easily supported by the default configuration, only one of - which suffices when database users mistrust other database users: + Schemas can be used to organize your data in many ways. + A <firstterm>secure schema usage pattern</firstterm> 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 <varname>search_path</varname> to the empty string or otherwise + removing non-superuser-writable schemas + from <varname>search_path</varname>. There are a few usage patterns + easily supported by the default configuration: <itemizedlist> <listitem> <!-- "DROP SCHEMA public" is inferior to this REVOKE, because pg_dump - doesn't preserve that DROP. --> + doesn't preserve that DROP. + + A database owner can attack the database's users via "CREATE SCHEMA + trojan; ALTER DATABASE $mydb SET search_path = trojan, public;". A + CREATEROLE user can issue "GRANT $dbowner TO $me" and then use the + database owner attack. --> <para> Constrain ordinary users to user-private schemas. To implement this, issue <literal>REVOKE CREATE ON SCHEMA public FROM PUBLIC</literal>, - and create a schema for each user with the same name as that user. If - affected users had logged in before this, consider auditing the public + and create a schema for each user with the same name as that user. + Recall that the default search path starts + with <literal>$user</literal>, 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 <literal>pg_catalog</literal>. Recall that the default search - path starts with <literal>$user</literal>, which resolves to the user - name. Therefore, if each user has a separate schema, they access their - own schemas by default. + schema <literal>pg_catalog</literal>. This pattern is a secure schema + usage pattern unless an untrusted user is the database owner or holds + the <literal>CREATEROLE</literal> privilege, in which case no secure + schema usage pattern exists. </para> - </listitem> - - <listitem> <para> - Remove the public schema from each user's default search path - using <literal>ALTER ROLE <replaceable>user</replaceable> SET - search_path = "$user"</literal>. 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 <link linkend="typeconv-func">will be - unsafe or unreliable</link>. Also, a user holding - the <literal>CREATEROLE</literal> privilege can undo this setting and - issue arbitrary queries under the identity of users relying on the - setting. If you create functions or extensions in the public schema or - grant <literal>CREATEROLE</literal> to users not warranting this - almost-superuser ability, use the first pattern instead. </para> </listitem> <listitem> <para> - Remove the public schema from <varname>search_path</varname> in - <link linkend="config-setting-configuration-file"><filename>postgresql.conf</filename></link>. - The ensuing user experience matches the previous pattern. In addition - to that pattern's implications for functions - and <literal>CREATEROLE</literal>, this trusts database owners - like <literal>CREATEROLE</literal>. If you create functions or - extensions in the public schema or assign - the <literal>CREATEROLE</literal> - privilege, <literal>CREATEDB</literal> privilege or individual database - ownership to users not warranting almost-superuser access, use the - first pattern instead. + Remove the public schema from the default search path, by modifying + <link linkend="config-setting-configuration-file"><filename>postgresql.conf</filename></link> + or by issuing <literal>ALTER ROLE ALL SET search_path = + "$user"</literal>. 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 <link linkend="typeconv-func">will be unsafe or + unreliable</link>. 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 <literal>CREATEROLE</literal> privilege. </para> </listitem> @@ -3067,10 +3068,9 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC; <para> 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, any user - can issue arbitrary queries under the identity of any user not electing - to protect itself individually. This pattern is acceptable only when - the database has a single user or a few mutually-trusting users. + 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. </para> </listitem> </itemizedlist> |