<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 theuser

own schemas by default.

schema <literal>pg_catalog</literal>.This pattern is a secure schema

usage pattern unless an untrusted user is thedatabase owner or holds

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>.

like <literal>CREATEROLE</literal>. If you createfunctionsor

ownership to users not warranting almost-superuser access, usethe

first pattern instead.

Remove the public schema fromthe default search path, by modifying

<link linkend="config-setting-configuration-file"><filename>postgresql.conf</filename></link>

While qualified table references are fine, calls tofunctionsin the

pattern, this is secure unless an untrusted user isthe database owner

or holds the <literal>CREATEROLE</literal> privilege.

</para>

</listitem>

<listitem>

<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>