PGPostgreSQL Schema

Table of Contents

1 Concept of Schema

Relationship between database schema tables

The reason for introducing schema

Create Schema

View Schema

Delete Schema

2Public Schema

3 Schema’s Rope Path

4 Schema and permissions

5 System Catalog Schema

6 How to use it (best practices)

7 Portability

The concept of 1 Schema

A PostgreSQL database cluster contains one or more databases. Roles and some other object types are shared across the cluster, and clients connecting to the server can only access data in a single database, the one specified in the connection request.

A database contains one or more schemas, which contain tables. Schemas also contain other types of named objects, including data types, functions, and operators. The same object name can be used in different schemas without conflict. For example, schema1 and myschema can both contain an object named mytable surface. Unlike databases, schemas are not strictly isolated: a user can access all schema objects in the database to which they are connected, as long as they have sufficient permissions.

Key points:

A cluster can contain multiple databases, each database can contain multiple schemas, and the schema contains tables and other objects

Relationship between database schema tables

database
Each PG service can contain multiple independent databases

schema
If databases are compared to a country, then the schema is a number of independent provinces. Most objects belong to a schema, and schemas belong to databases. When creating a new database, PG will automatically create a schema named public for it. If the searc_path variable is not set, PG will put all objects you create into public schema by default. If the number of tables is small, this is no problem, but if you have thousands of tables, then we still recommend that you classify them into different schemas.

surface
In any database, tables are the core object type. In PG, the table first belongs to a certain schema, and the schema belongs to a certain database, thus forming a three-level storage structure. PG tables support two very powerful functions. The first is inheritance, that is, a table can have parent tables and child tables. This hierarchical structure can greatly simplify database design and save you a lot of repeated query code. The second is that when creating a table, the system will automatically create a corresponding custom data type for the table.

Reason for introducing schema

  • Allows multiple users to use a database without interfering with each other.

  • Organize database objects into logical groups for easier management.

  • Objects from third-party applications can be placed in separate schemas so that they do not conflict with the names of other objects.

Create Schema

# Switch to db3 database
postgres=# \c db3
You are now connected to database "db3" as user "postgres".
db3=#

#Create schema
db3=# create SCHEMA myschema;
CREATE SCHEMA


#Create table cities in myschema
CREATE TABLE db3.myschema.cities (
    name varchar(80),
    location point
);


# Create a schema that is owned by someone else (since this is one of the ways to limit user actions to a well-defined namespace). Its syntax is:
db3=# CREATE SCHEMA myschema3 AUTHORIZATION role3;
CREATE SCHEMA
db3=#
db3=#
db3=# \dn
   List of schemas
   Name|Owner
---------- + ----------
 myschema | postgres
 myschema2 | postgres
 myschema3 | role3
 public|postgres
(4 rows)

View Schema

db3=# \dn
   List of schemas
   Name|Owner
---------- + ----------
 myschema | postgres
 myschema2 | postgres
 public|postgres
(3 rows)

Delete Schema

# Deleting a schema that is not empty will report an error

db3=# drop schema myschema;
ERROR: cannot drop schema myschema because other objects depend on it
DETAIL: table cities depends on schema myschema
HINT: Use DROP ... CASCADE to drop the dependent objects too.

# To delete a schema and all objects contained in it, use

db3=# DROP SCHEMA myschema CASCADE;
NOTICE: drop cascades to table cities
DROP SCHEMA

2 Public Schema

Create a table without specifying any schema name. By default, these tables (and other objects) are automatically placed in a schema named “public”. Any new database contains such a schema. Therefore, the following commands are equivalent:

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

The first schema in the search path is called the current schema. In addition to being the first schema searched, if the CREATE TABLE command does not specify a schema name, it will be the schema in which the newly created table is located.

# Display the current search path
db3=# SHOW search_path;
   search_path
------------------
 "$user", public
(1 row)

The first element indicates that a schema with the same name as the current user will be searched. If this schema does not exist, this item will be ignored. The second element points to the public schema we have already seen.

The first pattern in the search path is the default storage location where new objects are created. This is why objects are created in public schema by default. When an object is referenced in any other context without schema qualification (table modification, data modification, or query command), the search path is traversed until a matching object is found. Therefore, in the default configuration, any unqualified access will only be directed to the public schema.

# Modify search path
SET search_path TO myschema,public;

SET search_path TO myschema;

4 Schema and Permissions

5 System Catalog Schema

In addition to public and user-created schemas, every database includes a pg_catalog schema, which contains the system tables and all built-in data types, functions, and operators . pg_catalog is always a valid part of the search path. If the pattern is not included explicitly in the path, it will be searched before the pattern in the path. This ensures that built-in names are always found. However, if we wish to override the built-in name with a user-defined name, we can explicitly place pg_catalog at the end of the search path.

Since system table names begin with pg_, it is best to avoid using such names to avoid conflicts with system table names that may appear in future versions. System tables will continue to start with pg_ so that they do not conflict with unrestricted user table names.

6 How to use (best practice)

Schemas can organize data in a variety of ways. A safe mode usage prevents untrusted users from changing the query behavior of other users. When a database is not used in safe mode, users who wish to query the database securely will take protective actions at the beginning of each session. Specifically, they set search_path to an empty string before starting a session, or remove non-superuser writable modes in search_path. Some of the following usage methods can be easily implemented under the default configuration.

  • Constrain ordinary users to their private scenarios. To do this, execute REVOKE CREATE ON SCHEMA public FROM PUBLIC and create a schema for each user named with their username. Recall that $user at the beginning of the default search path resolves to the username. So if each user has a separate schema, by default they access their own schema. After using this method in a database where an untrusted user has logged in, consider checking the public schema for objects with similar names to objects in the schema pg_catalog. This method is a safe mode usage unless the untrusted user is the database owner or has CREATEROLE permission, in which case there will be no safe mode usage.

  • Remove the public schema from the default search path by modifying postgresql.conf or executing ALTER ROLE ALL SET search_path ="$user". Each role still retains the ability to create objects in the public schema, but only matching names will select those objects. Although conforming table references are safe, calling functions in the public schema is unsafe or unreliable. If you want to create functions or extensions in public schema, use the first way instead. Otherwise, as with the first usage, this is safe unless the untrusted user is the database owner or has CREATEROLE permission.

  • Keep the default. All users have implicit access to public schema. This simulates the situation of not using a mode at all and can be used to smooth the transition from a modeless world. However, this is by no means a safe way to use it. This is only acceptable if the database has only a single user or a small number of users who trust each other.

For either schema, if you want to install shared applications (tables that everyone uses, extra functions provided by third parties, etc.), put them in separate schemas. Remember to grant appropriate permissions to allow other users to access them. Users can then reference these additional objects by qualifying their names with the schema name, or they can put the additional schemas in their own search paths.

7 Portability