Relationships between PostgreSQL table spaces, schemas, tables, users/roles

The relationship between PostgreSQL table space , schema, table, user/Role is the main content of this article, table space, database, schema, table, user, role relationship in the end what is it? Let's start by introducing this process.

Experiment out the relationship between roles and users

In PostgreSQL, there are two confusing concepts: roles/Users. It is easy to confuse the two concepts because for PostgreSQL it is exactly the same two objects. The only difference is when you create it:

1. I created the character kanon:create role Kanon PASSWORD ' Kanon ' with the following psql; then I use the newly created role Kanon login, PostgreSQL gives a deny message: Fatal:role ' Kanon ' is Not permitted to log in. Indicates that the role does not have logon rights and the system refuses to log on.

2. I used the following psql to create the user kanon2:create users Kanon PASSWORD ' kanon2 '; then I log in with Kanon2 and log in successfully. Is there a difference between the two? Look at the document, another paragraph stating: "Create USER is the same as create ROLE except that it implies LOGIN." ----Create user is identical to create role except that it has login permissions by default.

In order to verify this sentence, modify the permissions of Kanon, add login permission: ALTER ROLE Kanon login; log in again with Kanon, success! Well, it's clear: Create ROLE kanon PASSWORD ' Kanon ' LOGIN equals create USER kanon PASSWORD ' Kanon '. That's the difference between role/user.

The relationship between database and schema

Schema is a logical division of a database.

At the same time as the database was created, a schema--public has been created for the database by default, which is also the default mode for that database. All objects created for this database (tables, functions, attempts, indexes, sequences, and so on) are common in this pattern.
The experiment is as follows:

1. Create a database DBTT----create databases DBTT;

2. Log in to the DBTT database with the Kanon role to view all the modes in the DBTT database:/DN; The display result is only public one mode.

3. Create a test table----the CREATE TABLE test (ID integer not NULL);

4. View a list of current databases:/D; The result is that the table test belongs to the schema public. That is, the test table is created by default in public mode.

5. Create a new schema Kanon, corresponding to the login user kanon:create SCHEMA kanon OWNER Kanon;

6. Create a test table again, this time this table to indicate the mode----CREATE TABLE kanon.test (ID integer not NULL);

7. View a list of current databases:/D; The result is that the table test belongs to the pattern Kanon. That is, the test table is created in the Kanon mode. The conclusion is that the database is segmented by schema (schema), a database has at least one schema, and all objects inside the database (object) are created in the schema. After the user logs on to the system, connected to a database, is through the database Search_path to find the schema of the search order, can be ordered by the command show Search_path, the specific order can also be set Search_path to ' Schema_ Name ' to modify the order.

The official recommendation is this: After the administrator creates a specific database, you should create a different user name mode for all users who can connect to the database, and then set Search_path to "$user".
This way, when a user connects, the lookup or the defined object is positioned by default in a pattern with the same name. This is a good design architecture.

The relationship between Tablespace and database

Database creation Statement CREATE DATABASE dbname The default database owner is the role that is currently creating databases, and the default tablespace is the default tablespace--pg_default for the system.
Why is that so? Because in PostgreSQL, the creation of data is done by cloning a database template, which is the same mechanism as SQL Server.

Because the CREATE DATABASE dbname does not specify a template for the databases, the system will clone the TEMPLATE1 database by default and get a new database dbname. (By default, the new database is created by cloning the standard system database template1).

The default tablespace for the Template1 database is Pg_default, which is created when the database is initialized, so all objects in the template1 are cloned to the new database synchronously.
The relatively complete syntax should look like this: CREATE DATABASE dbname OWNER kanon TEMPLATE template1 tablespace tablespacename;

Let's do an experiment to verify the following:

1. Connect to the TEMPLATE1 database and create a table as a token: the CREATE TABLE Tbl_flag (ID integer NOT NULL), insert data into the table inserts into Tbl_flag VALUES (1);

2. Creating a tablespace: Create tablespace tskanon OWNER kanon location '/tmp/data/tskanon '; Before this you should make sure that the directory/tmp/data/tskanon exists and that the directory is empty.

3. Create a database that indicates that the table space for the database is just created tskanon:create database Dbkanon TEMPLATE template1 ownere kanon tablespace Tskanon;

4. View information for all databases in the system:/L; it can be found that the table space of the Dbkanon database is Tskanon, the owner is Kanon;

5. Connect to the Dbkanon database to view all table structures:/D; it can be found that in the database just created a table tbl_flag, view the table data, the output of a column, the value of 1, indicating that the database is actually cloned from Template1.

After careful analysis, it is not difficult to conclude: in PostgreSQL, the tablespace is a directory, which stores the various physical files of the database it contains.

Summarize:

A tablespace is a storage area that can store multiple databases in a single table space, although PostgreSQL does not recommend this, but we do so in a very practical way. A database does not know the direct storage table structure and other objects, but in the database logic to create at least one schema, in the schema to create a table and other objects, the different patterns assigned to the different roles, you can achieve the separation of permissions, but also through authorization to achieve the sharing of objects between the pattern, and, Another feature is that public mode can store objects that everyone needs to access.

In this way, our nets are formed. However, since a table can specify a tablespace when it is created, is it possible to give a table a table space outside of the database table space where it resides? The answer is YES! It is perfectly possible to do so: then this is not a violation of the table belongs to the schema, and the schema belongs to the database, the database finally exists in the specified table space this network model?! Yes, it does seem counterintuitive, but it does make sense, and in reality, we often need to do this: table space on a slower disk for table data, and table space on a fast disk for the table's index.

But we look at the pattern that the table belongs to remains the same, and it still belongs to the specified pattern. So it's not counterintuitive. In fact, PostgreSQL does not restrict a table to a particular table space, and we think so because when the relationship is progressive, a concept is changed: the schema is logically present, and it is not constrained by the table space.

The knowledge about the relationship between PostgreSQL table spaces, patterns, tables, users/roles is introduced here, and I hope this introduction will get you something!

Relationships between PostgreSQL table spaces, schemas, tables, users/roles