This is the sixth day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

Why is it recommended to specify a default SCHEMA for users

To create objects without users, you need to explicitly specify a schema, as in:

B: That's not necessary
CREATE TABLE em.emp(id int); 
B: You can do that
CREATE TABLE emp(id int); 
Copy the code

In PostgreSql, all newly created databases have a public mode by default. All users can create and manage their own objects in this mode. When creating basic objects such as tables and views in a database, if no mode is specified, these objects are also stored in Pubilc mode by default. (except the user name with the same name as the schema name) For details: juejin.cn/post/698719…

The reasons for this happening

This is because the default search path in the PostgresQL database is search_path.

oa=# show search_path;
   search_path
-----------------
 "$user", public
(1 row)
Copy the code

As can be seen from the above search path, users will first look for a SCHEMA with the same name as their user name after connecting to the database. If they can find it, it will be used as the current SCHEMA, and all subsequent objects will be stored in this SCHEMA. (If the user has permission to access the schema)

The user whose name is different from the schema name is not the current schema, even if the user has the permission to access the schema.
oa=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 em     | postgres
 op     | postgres
 public | postgres
 rl     | postgres
(4 rows)
oa=# \c - syd
You are now connected to database "oa" as user "syd".
oa=> select current_schema;
 current_schema
----------------
 public
(1 row)

oa=# grant USAGE on schema rl to syd;
GRANT
oa=# \c - syd
You are now connected to database "oa" as user "syd".
oa=> select current_schema;
 current_schema
----------------
 public
(1 row)

- Test the user with the same name as the schema. The user must have the permission to access the schema. (Two ways to authorize access to a schema)
oa=> \c - em
You are now connected to database "oa" as user "em".
oa=> select current_schema;
 current_schema
----------------
 public
(1 row)

oa=> \dn
  List of schemas
  Name  |  Owner
--------+----------
 em     | postgres
 op     | postgres
 public | postgres
 rl     | postgres
(4 rows)

--1) Change the owner of the schema to this user, and the user naturally has the permission to access the schema
oa=> \c - postgres
You are now connected to database "oa" as user "postgres".
oa=# alter schema em owner to em;
ALTER SCHEMA
oa=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 em     | em
 op     | postgres
 public | postgres
 rl     | postgres
(4 rows)
oa=# \c - em
You are now connected to database "oa" as user "em".
oa=> select current_schema;
 current_schema
----------------
 em
(1 row)

--2) Directly grant user access to schema
oa=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 em     | em
 op     | postgres
 public | postgres
 rl     | rl
(4 rows)

oa=# \c - op
You are now connected to database "oa" as user "op".
oa=> select current_schema;
 current_schema
----------------
 public
(1 row)

oa=> \c - postgres
oa=# grant USAGE on schema op to op;
GRANT
oa=# \c - op
You are now connected to database "oa" as user "op".
oa=> select current_schema;
 current_schema
----------------
 op
(1 row)
Copy the code

How to specify the default schema for common user Syd

oa=# alter user syd set search_path to rl;
ALTER ROLE
oa=# \c - syd
You are now connected to database "oa" as user "syd".
oa=> \dn
  List of schemas
  Name  |  Owner
--------+----------
 em     | em
 op     | postgres
 public | postgres
 rl     | rl
(4 rows)

oa=> select current_schema;
 current_schema
----------------
 rl
(1 row)
Copy the code

conclusion

-- Set the apply_owner user to connect to any database. The current default schema is apply.
alter user apply_owner set search_path to apply; 
Set any user to connect to the CHIS database. The current default schema is apply
alter database chis set search_path to apply; 
- The user name is the same as the schema and the user has the permission to access the modified schema. When the user accesses the database, the current schema is used by default
create database chis; 
\c chis create schema apply; 
create user apply password '11111'; 

grant USAGE on schema apply toapply; oralter schema apply owner to apply;
Copy the code