Background environment

The characteristics of the schema

• Schema concepts are like namespaces
• Schema can no longer be nested under schema
• Objects such as tables and functions are stored in different schemas
• Object names cannot be repeated in the same schema, but can be repeated in different schemas

Use the role of schema

• Easy to manage multiple users sharing a database, but can be independent of each other.
• Convenient management of many objects, more logical
• Easy compatibility with some third-party applications, object creation is a schema

For example, if you want to design a complex system, it is made up of many modules, and sometimes the modules need to be independent. It is obviously not appropriate for each module to have a separate database. At this time, the use of schema to classify objects between modules, and then the appropriate permission control for users, so that the logic is very clear.

Schema similarities and differences in common databases

Most databases have a schema or equivalent, but the meaning and operations are different.

• In PostgreSQL, a database can have multiple schemas. You can specify an owner for the schema. If no owner is specified, the current user is the default owner for the schema.
• Instead of creating a schema in an Oracle database, the system creates a schem with the same name for the user and uses it as the default shcema. That is, the number of schemas is the same as the number of users, and the name of the schema is the same as the name of the user.
• There is no schema in MySQL, so creating a database has the same effect as creating a schema. The database in MySQL is a schema.

The software environment for this test is as follows

CentOS 7 x64

PostgreSQL 11.1

Access matrix

We need to create a database in PostgreSQL and create multiple schemas under this DB. Each schema has its own owner, and the DB owner can operate on all schemas.

The simple permission relationship matrix is as follows

Initializing the database

Superuser creates users and databases

PSQL --username=admin --dbname=postgres --password PSQL --username=admin --dbname=postgres --passwordCopy the code

-- Create a new user. This user will become Master
drop user if exists db_demo_owner;
create user db_demo_owner with password 'xxx';

Create a child user
drop user if exists schema_owner_01;
create user schema_owner_01 with password 'xxx';

- Set the permission group for the primary user to have the permission of the sub-users
-- db_demo_owner is member of schema_OWner_01. Db_demo_owner has the schema_OWner_01 permission
grant schema_owner_01 to db_demo_owner;

-- Another child user
drop user if exists schema_owner_02;
create user schema_owner_02 with password 'xxx';
grant schema_owner_02 to db_demo_owner;
Copy the code

Create a test DB and assign it to the primary user
drop database if exists db_demo;
create database db_demo with encoding='utf8' owner=db_demo_owner;
Copy the code

Primary user create schema (emphasis)

PSQL --username=db_demo_owner --dbname=db_demo --passwordCopy the code

-- Do not specify schema owner, default is current user (primary user)
drop schema s00 cascade;
create schema s00 ;	

Create a schema with the primary user and set the subuser to owner
drop schema s01 cascade;
create schema s01 authorization schema_owner_01 ;

drop schema s02 cascade;
create schema s02 authorization schema_owner_02 ;
Copy the code

The primary user creates the table

- Primary user login
psql --username=db_demo_owner --dbname=db_demo --password

The primary user creates table T00 in each schema
create table s00.t00(id int);
insert into s00.t00 values(1);	

create table s01.t00(id int);
insert into s01.t00 values(1);

create table s02.t00(id int);
insert into s02.t00 values(1);
Copy the code

Subusers create tables

Different subusers create tables in their own schemas

- Subuser 1 logs in
psql --username=schema_owner_01 --dbname=db_demo --password	

create table s01.t01(id int);
insert into s01.t01 values(1);
Copy the code

- Subuser 2 logs in
psql --username=schema_owner_02 --dbname=db_demo --password	

create table s02.t02(id int);
insert into s02.t02 values(1);
Copy the code

Validation of the Owner

Confirm the db owner

- the main users are the DB Owner postgres = # \ l db_demo List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ---------+---------------+----------+-------------+-------------+------------------- db_demo | db_demo_owner | UTF8 | En_US. Utf-8 | en_US. Utf-8 | (1 row), the main users in the group of inside, That already have child user permissions postgres = # \ owner du * * a List of roles Role name | Attributes | Member of -----------------+------------+----------------------------------- db_demo_owner | | {schema_owner_01,schema_owner_02} schema_owner_01 | | {} schema_owner_02 | | {}Copy the code

Confirm the schema owner

- three schema points belonging to different users db_demo = > \ dn s * a List of schemas Name | Owner -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - s00 | db_demo_owner s01 | schema_owner_01 s02 | schema_owner_02 (3 rows)Copy the code

Confirm the table owner

- five table points belong to different users db_demo = > \ dt s *. The List of relations Schema | Name | Type | Owner --------+------+-------+----------------- s00 | t00 | table | db_demo_owner s01 | t00 | table | db_demo_owner s01 | t01 | table | schema_owner_01 s02 | t00 | table | db_demo_owner s02 | t02 | table | schema_owner_02 (5 rows)Copy the code

Verifying access rights

The child users

Without separate authorization, subusers can access only their own tables

PSQL --username= schemA_owner_01 --dbname=db_demo --password -- no permission db_demo=> select * from s00.t00; ERROR: permission denied for schema s00 db_demo=> select * from s02.t02; ERROR: permission denied for schema s02 -- db_demo=> select * from s01. id ---- 1 (1 row)Copy the code

The main user

The primary user has access to all tables

PSQL --username=db_demo_owner --dbname=db_demo --password -- have permissions db_demo=> select * from s00.t00; id ---- 1 (1 row) db_demo=> select * from s01.t01; id ---- 1 (1 row) db_demo=> select * from s02.t02; id ---- 1 (1 row)Copy the code

Change the schema owner

S02 used to belong to Owner02, but now it belongs to Owner01

- the main user login PSQL - username = db_demo_owner - dbname = db_demo - password - to change former db_demo - > \ dn s02 List of schemas Name | Owner -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- s02 | schema_owner_02 row (1) - change the schema owner to another child users alter schema s02 owner to schema_owner_01; - changes after db_demo = > \ dn s02 List of schemas Name | Owner -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- s02 | schema_owner_01 row (1)Copy the code

Important: Simply changing owner does not change the permissions of an existing table. You must explicitly grant permissions

Change the owner of the specified table
alter table s02.t02 owner to schema_owner_01;

Or batch authorize all tables in the schema without changing the owner
grant all on all tables in schema s02 to schema_owner_01;
Copy the code

Read permissions

After the previous operations, subuser 02 no longer has any access rights. We want this subuser to have read-only permissions on all schemas.

- Primary user login
psql --username=db_demo_owner --dbname=db_demo --password

-- Important note: This method only applies to existing tables. Tables created later do not automatically have read-only permission
grant usage on schema s00, s01, s02 to schema_owner_02;
grant select on all tables in schema s00, s01, s02 to schema_owner_02;
Copy the code

If we want all new tables created in the future to be automatically read-only (not valid for existing tables), we can use the following statement.

The official instructions are here

-- Valid for all tables created under schema S00, S01, and S02 after 01
alter default privileges 
	for user schema_owner_01, schema_owner_02 
	in schema s00, s01, s02 
	grant select on tables to schema_owner_02;
Copy the code