postgres 模式schema

1) Schemas

There are several reasons why one might want to use schemas:

• To allow many users to use one database without interfering with each other.

• To organize database objects into logical groups to make them more manageable.

• Third-party applications can be put into separate schemas so they do not collide with the names of other objects.

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

1-1) CREATE SCHEMA myschema

CREATE SCHEMA myschema;

schema.table

database.schema.table

CREATE TABLE myschema.mytable (
 ...
);

DROP SCHEMA myschema;

# To drop a schema including all contained objects
DROP SCHEMA myschema CASCADE; 

CREATE SCHEMA schema_name AUTHORIZATION user_name;

1-2) Public schema

By default such tables (and other objects) are automatically put into a schema named “public”.

The following two statements are equivalent:

CREATE TABLE products ( ... ); 
CREATE TABLE public.products ( ... );

1-3) The Schema Search Path

To show the current search path, use the following command:

show search_path;

In the default setup this returns:

 search_path
--------------
 "$user", public

To put our new schema in the path, we use:

SET search_path TO myschema,public;

And then we can access the table without schema qualification:

DROP TABLE mytable;

Also, since myschema is the first element in the path, new objects would by default be created in it.

1-4) Schemas and Privileges

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.

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

The first “public” is the schema, the second “public” means “every user”.

1-5) Usage Patterns

Schemas can be used to organize your data in many ways. There are a few usage patterns that are recommended and are easily supported by the default configuration:

• If you do not create any schemas then all users access the public schema implicitly. This simulates the situation where schemas are not available at all. This setup is mainly recommended when there is only a single user or a few cooperating users in a database. This setup also allows smooth transition from the non-schema-aware world.

• You can create a schema for each user with the same name as that user. Recall that the default search path starts with $user, which resolves to the user name. Therefore, if each user has a separate schema, they access their own schemas by default.
  If you use this setup then you might also want to revoke access to the public schema (or drop it altogether), so users are truly constrained to their own schemas.

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