A PostgreSQL database cluster contains one or more named databases. Roles and a few other object types are shared across the entire cluster.

A client connection to the server can only access data in a single database, the one specified in the connection request.

A database contains one or more named schemas, which in turn contain tables. Schemas also contain other kinds of named objects, including data types, functions, and operators.

The same object name can be used in different schemas without conflict; for example, both schema1 and myschema can contain tables named mytable.

Unlike databases, schemas are not rigidly separated: a user can access objects in any of the schemas in the database they are connected to, if they have privileges to do so.

Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.

CREATE SCHEMA myschema;
--equivalent to the following
CREATE SCHEMA public.myschema;

CREATE TABLE myschema.mytable (
 -- ...
);
-- drop a schema if it's empty (all objects in it have been dropped)
DROP SCHEMA myschema;
-- drop a schema including all contained objects
DROP SCHEMA myschema CASCADE;

Often you will want to create a schema owned by someone else (since this is one of the ways to restrict the activities of your users to well-defined namespaces).

CREATE SCHEMA schema_name AUTHORIZATION user_name;

You can even omit the schema name, in which case the schema name will be the same as the user name.

Schema names beginning with pg_ are reserved for system purposes and cannot be created by users.

When we created tables without specifying any schema names, by default such tables (and other objects) are automatically put into a schema named public. Every new database contains such a schema.

Qualified names are tedious to write, and it’s often best not to wire a particular schema name into applications anyway. Therefore tables are often referred to by unqualified names, which consist of just the table name.

The system determines which table is meant by following a search path, which is a list of schemas to look in. The first matching table in the search path is taken to be the one wanted. If there is no match in the search path, an error is reported, even if matching table names exist in other schemas in the database.

The ability to create like-named (同名) objects in different schemas complicates writing a query that references precisely the same objects every time. It also opens up the potential for users to change the behavior of other users’ queries, maliciously or accidentally.

Due to the prevalence of unqualified names in queries and their use in PostgreSQL internals, adding a schema to search_path effectively trusts all users having CREATE privilege on that schema. When you run an ordinary query, a malicious user able to create objects in a schema of your search path can take control and execute arbitrary SQL functions as though you executed them.

The first schema named in the search path is called the current schema. Aside from being the first schema searched, it is also the schema in which new tables will be created if the CREATE TABLE command does not specify a schema name.

SHOW search_path;
-- search_path
-- --------------
-- "$user", public

The first element specifies that a schema with the same name as the current user is to be searched. If no such schema exists, the entry is ignored. The second element refers to the public schema.

The first schema in the search path that exists is the default location for creating new objects. That is the reason that by default objects are created in the public schema. When objects are referenced in any other context without schema qualification (table modification, data modification, or query commands) the search path is traversed until a matching object is found. Therefore, in the default configuration, any unqualified access again can only refer to the public schema.

Put the new schema in the path:

SET search_path TO myschema,public;

SET search_path TO myschema;
-- we no longer have access to the public schema without explicit qualification

-- connection string supports search path natively
-- "FABRIC_CA_SERVER_DB_DATASOURCE": "host=127.0.0.1,127.0.0.1 port=12345,25432 user=john password=la@0 dbname=whatever001 sslmode=disable search_path=schema003"

There is nothing special about the public schema except that it exists by default. It can be dropped, too.

By default, users cannot access any objects in schemas they do not own. To allow that, the owner of the schema must grant the USAGE privilege on the schema. To allow users to make use of the objects in the schema, additional privileges might need to be granted, as appropriate for the object.

A user can also be allowed to create objects in someone else’s schema. To allow that, the CREATE privilege on the schema needs to be granted. Note that by default, everyone has CREATE and USAGE privileges on the schema public. This allows all users that are able to connect to a given database to create objects in its public schema.

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

The first “public” is the schema, the second “public” means “every user”. In the first sense it is an identifier, in the second sense it is a key word, hence the different capitalization.

In addition to public and user-created schemas, each database contains a pg_catalog schema, which contains the system tables and all the built-in data types, functions, and operators.

pg_catalog is always effectively part of the search path. If it is not named explicitly in the path then it is implicitly searched before searching the path’s schemas. This ensures that built-in names will always be findable. However, you can explicitly place pg_catalog at the end of your search path if you prefer to have user-defined names override built-in names.

A secure schema usage pattern 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 search_path to the empty string or otherwise removing non-superuser-writable schemas from search_path.

There are a few usage patterns easily supported by the default configuration:

  • Constrain ordinary users to user-private schemas.
    • To implement this, issue REVOKE CREATE ON SCHEMA public FROM PUBLIC, and create a schema for each user with the same name as that user.
    • 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 pg_catalog.
  • Remove the public schema from the default search path, by modifying postgresql.conf or by issuing ALTER ROLE ALL SET search_path = "$user".
    • Everyone retains the ability to create objects in the public schema, but only qualified names (指定了 schema 的) will choose those objects.
    • While qualified table references are fine, calls to functions in the public schema will be unsafe or unreliable . 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 CREATEROLE privilege.
  • 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.
    • This is never a secure pattern. It is acceptable only when the database has a single user or a few mutually-trusting users.

For any pattern, to install shared applications (tables to be used by everyone, additional functions provided by third parties, etc.), put them into separate schemas. Remember to grant appropriate privileges to allow the other users to access them. Users can then refer to these additional objects by qualifying the names with a schema name, or they can put the additional schemas into their search path, as they choose.

In the SQL standard, the notion of objects in the same schema being owned by different users does not exist. Moreover, some implementations do not allow you to create schemas that have a different name than their owner. In fact, the concepts of schema and user are nearly equivalent in a database system that implements only the basic schema support specified in the standard. Therefore, many users consider qualified names to really consist of user_name.table_name. This is how PostgreSQL will effectively behave if you create a per-user schema for every user.

Also, there is no concept of a public schema in the SQL standard. For maximum conformance to the standard, you should not use the public schema.

Of course, some SQL database systems might not implement schemas at all, or provide namespace support by allowing (possibly limited) cross-database access. If you need to work with those systems, then maximum portability would be achieved by not using schemas at all.

References