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

removingnon-superuser-writableschemas

removingschemas that arewritableby non-superusers

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.

A database owner can attack the database's users via "CREATE SCHEMA

<para>

Constrain ordinary users to user-private schemas.

To implement this pattern, first ensure that no schemas have

public <literal>CREATE</literal> privileges. Then, for every user

needing to create non-temporary objects, create a schema with the

same name as that user, for example

<literal>CREATE SCHEMA alice AUTHORIZATION alice</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.) 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>

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

first issue <literal>REVOKE CREATE ON SCHEMA public FROM

PUBLIC</literal>. Then, for every user needing to create non-temporary

objects, create a schema 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>. 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>

<para>

If the database originated in an upgrade

from <productname>PostgreSQL</productname> 14 or earlier,

the <literal>REVOKE</literal> is essential. Otherwise, the default

configuration follows this pattern; ordinary users can create only

temporary objects until a privileged user furnishes a schema.

In <productname>PostgreSQL</productname> 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 <literal>CREATE</literal>

privilege from the <literal>public</literal> schema (issue

<literal>REVOKE CREATE ON SCHEMA public FROM PUBLIC</literal>).

Then consider auditing the <literal>public</literal> schema for

objects named like objects in schema <literal>pg_catalog</literal>.

</para>

<!-- "DROP SCHEMA public" is inferior to this REVOKE, because pg_dump

doesn't preserve that DROP. -->

</listitem>

<listitem>