schema and user creation - Scenario

postgres=# \c edbstore u01

psql (10.9, server 11.7)
WARNING: psql major version 10, server major version 11.
         Some psql features might not work.
You are now connected to database "edbstore" as user "u01".
edbstore=>
edbstore=>
edbstore=> create schema hr;
ERROR:  permission denied for database edbstore
edbstore=>
edbstore=>
edbstore=> \c edbstore postgres
psql (10.9, server 11.7)
WARNING: psql major version 10, server major version 11.
         Some psql features might not work.
You are now connected to database "edbstore" as user "postgres".
edbstore=#
edbstore=#
edbstore=# create schema hr authorization u01;
CREATE SCHEMA
edbstore=#
edbstore=#
edbstore=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 hr     | u01
 public | postgres
(2 rows)
edbstore=#
edbstore=# create schema accounts authorization u02;
CREATE SCHEMA
edbstore=#
edbstore=# \dn
   List of schemas
   Name   |  Owner
----------+----------
 accounts | u02
 hr       | u01
 public   | postgres
(3 rows)
edbstore=# \c edbstore u01
psql (10.9, server 11.7)
WARNING: psql major version 10, server major version 11.
         Some psql features might not work.
You are now connected to database "edbstore" as user "u01".
edbstore=>
edbstore=>
edbstore=>
edbstore=>
edbstore=>
edbstore=>
edbstore=>
edbstore=>  show search_path;
   search_path
-----------------
 "$user", public
(1 row)
edbstore=>
edbstore=>
edbstore=> set search_path to hr;
SET
edbstore=>
edbstore=> create table hr_empinfo(empid int, empname varchar);
CREATE TABLE
edbstore=>
edbstore=>
edbstore=>
edbstore=> insert into hr_empinfo values(1001,'postgres');
INSERT 0 1
edbstore=>
edbstore=>
edbstore=> \dt
          List of relations
 Schema |    Name    | Type  | Owner
--------+------------+-------+-------
 hr     | hr_empinfo | table | u01
(1 row)
edbstore=>
edbstore=>
edbstore=>

postgres=#

postgres=# \c edbstore u01

psql (10.9, server 11.7)

WARNING: psql major version 10, server major version 11.

         Some psql features might not work.

You are now connected to database "edbstore" as user "u01".

edbstore=>

edbstore=>

edbstore=> create schema hr;

ERROR:  permission denied for database edbstore

edbstore=>

edbstore=>

edbstore=> \c edbstore postgres

psql (10.9, server 11.7)

WARNING: psql major version 10, server major version 11.

         Some psql features might not work.

You are now connected to database "edbstore" as user "postgres".

edbstore=#

edbstore=#

edbstore=# create schema hr authorization u01;

CREATE SCHEMA

edbstore=#

edbstore=#

edbstore=# \dn

  List of schemas

  Name  |  Owner

--------+----------

 hr     | u01

 public | postgres

(2 rows)

edbstore=#

edbstore=# create schema accounts authorization u02;

CREATE SCHEMA

edbstore=#

edbstore=# \dn

   List of schemas

   Name   |  Owner

----------+----------

 accounts | u02

 hr       | u01

 public   | postgres

(3 rows)

edbstore=# \c edbstore u01

psql (10.9, server 11.7)

WARNING: psql major version 10, server major version 11.

         Some psql features might not work.

You are now connected to database "edbstore" as user "u01".

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=>  show search_path;

   search_path

-----------------

 "$user", public

(1 row)

edbstore=>

edbstore=>

edbstore=> set search_path to hr;

SET

edbstore=>

edbstore=> create table hr_empinfo(empid int, empname varchar);

CREATE TABLE

edbstore=>

edbstore=>

edbstore=>

edbstore=> insert into hr_empinfo values(1001,'postgres');

INSERT 0 1

edbstore=>

edbstore=>

edbstore=> \dt

          List of relations

 Schema |    Name    | Type  | Owner

--------+------------+-------+-------

 hr     | hr_empinfo | table | u01

(1 row)

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=> grant select on all tables in schema hr to u02;

GRANT

edbstore=>

edbstore=>

edbstore=>

edbstore=> \c edbstore u02

psql (10.9, server 11.7)

WARNING: psql major version 10, server major version 11.

         Some psql features might not work.

You are now connected to database "edbstore" as user "u02".

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=> set search_path to hr;

SET

edbstore=>

edbstore=>

edbstore=> \dt

Did not find any relations.

edbstore=>

edbstore=>

edbstore=>  \c edbstore u01

psql (10.9, server 11.7)

WARNING: psql major version 10, server major version 11.

         Some psql features might not work.

You are now connected to database "edbstore" as user "u01".

edbstore=>

edbstore=>

edbstore=>

edbstore=> \dt

Did not find any relations.

edbstore=>

edbstore=> \dn

   List of schemas

   Name   |  Owner

----------+----------

 accounts | u02

 hr       | u01

 public   | postgres

(3 rows)

edbstore=> \c edbstore u01

psql (10.9, server 11.7)

WARNING: psql major version 10, server major version 11.

         Some psql features might not work.

You are now connected to database "edbstore" as user "u01".

edbstore=>

edbstore=>

edbstore=>

edbstore=> show search_path;

   search_path

-----------------

 "$user", public

(1 row)

edbstore=>

edbstore=>

edbstore=> set search_path to hr;

SET

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=> \dt

          List of relations

 Schema |    Name    | Type  | Owner

--------+------------+-------+-------

 hr     | hr_empinfo | table | u01

(1 row)

edbstore=>

edbstore=>

edbstore=>

edbstore=> \c edbstore u02

psql (10.9, server 11.7)

WARNING: psql major version 10, server major version 11.

         Some psql features might not work.

You are now connected to database "edbstore" as user "u02".

edbstore=>

edbstore=>

edbstore=>

edbstore=> \dn

   List of schemas

   Name   |  Owner

----------+----------

 accounts | u02

 hr       | u01

 public   | postgres

(3 rows)

edbstore=>

edbstore=>

edbstore=>  set search_path to hr;

SET

edbstore=>

edbstore=>

edbstore=> \dt

Did not find any relations.

edbstore=>

edbstore=>

edbstore=> \c edbstore u01

psql (10.9, server 11.7)

WARNING: psql major version 10, server major version 11.

         Some psql features might not work.

You are now connected to database "edbstore" as user "u01".

edbstore=>

edbstore=>

edbstore=>

edbstore=> grant usage on schema hr to u02;

GRANT

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=> \c edbstore u02

psql (10.9, server 11.7)

WARNING: psql major version 10, server major version 11.

         Some psql features might not work.

You are now connected to database "edbstore" as user "u02".

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=> set search_path to hr;

SET

edbstore=>

edbstore=>

edbstore=> \dt

          List of relations

 Schema |    Name    | Type  | Owner

--------+------------+-------+-------

 hr     | hr_empinfo | table | u01

(1 row)

edbstore=>

edbstore=>

edbstore=>

edbstore=> select * from hr_empinfo;

 empid | empname

-------+----------

  1001 | postgres

(1 row)

edbstore=> \c edbstore u02

psql (10.9, server 11.7)

WARNING: psql major version 10, server major version 11.

         Some psql features might not work.

You are now connected to database "edbstore" as user "u02".

edbstore=>

edbstore=> set search_path to hr;

SET

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=>

edbstore=>  \dt

          List of relations

 Schema |    Name    | Type  | Owner

--------+------------+-------+-------

 hr     | hr_empinfo | table | u01

(1 row)

edbstore=>

edbstore=>

edbstore=> select * from hr_empinfo;

 empid | empname

-------+----------

  1001 | postgres

(1 row)

edbstore=>

edbstore=> \z hr_empinfo

                               Access privileges

 Schema |    Name    | Type  | Access privileges | Column privileges | Policies

--------+------------+-------+-------------------+-------------------+----------

 hr     | hr_empinfo | table | u01=arwdDxt/u01  +|                   |

        |            |       | u02=r/u01         |                   |

(1 row)

edbstore=> select * from hr.hr_empinfo;

 empid | empname

-------+----------

  1001 | postgres

(1 row)

edbstore=>

edbstore=>