\c dbtest postgres
CREATE USER user1 WITH PASSWORD 'xxx';
CREATE USER user2 WITH PASSWORD 'xxx';
CREATE USER user3 WITH PASSWORD 'xxx';
CREATE USER user4 WITH PASSWORD 'xxx';
CREATE USER user5 WITH PASSWORD 'xxx';
CREATE USER user6 WITH PASSWORD 'xxx';

dbtest=# create schema user3 authorization user3;
CREATE SCHEMA
dbtest=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 user3 | user3
 public | postgres
(2 rows)

dbtest=# create schema authorization user6;
CREATE SCHEMA
dbtest=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 user3 | user3
 user6 | user6
 public | postgres
(3 rows)
dbtest=# create schema authorization user2;
CREATE SCHEMA
dbtest=# create schema authorization user5;
CREATE SCHEMA
dbtest=# create schema authorization user1;
CREATE SCHEMA
dbtest=# create schema authorization user4;
CREATE SCHEMA
dbtest=# \dn
   List of schemas
   Name   |  Owner   
----------+----------
 user3   | user3
 user6   | user6
 user2   | user2
 user5 | user5
 user1   | user1
 public   | postgres
 user4   | user4
(7 rows)

dbtest=# \dg
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 user3    |                                                            | {}
 user6    |                                                            | {}
 user2    |                                                            | {}
 user5  |                                                            | {}
 user1    |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 pubuser   | No inheritance                                             | {}
 user4    |                                                            | {}

dbtest=# show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

dbtest=> \c
You are now connected to database "dbtest" as user "user3".
dbtest=> select user;
  user  
--------
 user3
(1 row)

dbtest-> \c dbtest user3
You are now connected to database "dbtest" as user "user3".
dbtest-> \dt
                   List of relations
 Schema |            Name             | Type  |  Owner  
--------+-----------------------------+-------+---------
 user3 | test                        | table | user3
 public | TEST_CBSCCBTS        | table | pubuser
 public | TEST_CBSCCBTSRRU     | table | pubuser
 public | TEST_CBSCG3CELL      | table | pubuser

 
 
dbtest=> \c dbtest postgres
You are now connected to database "dbtest" as user "postgres".
dbtest=# select * from dbtest.user3.test;
 id 
----
(0 rows)

dbtest=# select count(*) from t;
 count 
-------
     0
(1 row)

dbtest=# select count(*) from dbtest.public.t;
 count 
-------
     0
(1 row)

dbtest=> \c dbtest postgres
You are now connected to database "dbtest" as user "postgres".
dbtest=# grant select on all tables in schema public to user6;
GRANT
dbtest=# \z t
                               Access privileges
 Schema | Name | Type  |    Access privileges    | Column privileges | Policies 
--------+------+-------+-------------------------+-------------------+----------
 public | t    | table | pubuser=arwdDxt/pubuser+|                   | 
        |      |       | user6=r/pubuser        |                   | 
(1 row)

dbtest=# \z test
                               Access privileges
 Schema | Name | Type  |    Access privileges    | Column privileges | Policies 
--------+------+-------+-------------------------+-------------------+----------
 public | test | table | pubuser=arwdDxt/pubuser+|                   | 
        |      |       | user6=r/pubuser        |                   | 
(1 row)

dbtest=# \c dbtest user6
You are now connected to database "dbtest" as user "user6".
dbtest=> select count(*) from dbtest.public.t;
 count 
-------
     0
(1 row)

dbtest=> select count(*) from t;
 count 
-------
     0
(1 row)

dbtest=> \c dbtest postgres;
You are now connected to database "dbtest" as user "postgres".
dbtest=# revoke select on all tables in schema public from user6;
REVOKE
dbtest=# \c dbtest user6;
You are now connected to database "dbtest" as user "user6".
dbtest=> select count(*) from t;
ERROR:  permission denied for table t
dbtest=> select count(*) from dbtest.public.t;
ERROR:  permission denied for table t

postgresql授权和撤销参考：
https://blog.csdn.net/qq_40907977/article/details/102901979

建完新库后，避免被别的用户访问：

revoke connect on database xxx from public;

alter user test set default_transaction_read_only=on; 
grant all on database crm_db to test; 
把库授权给别的用户访问：
grant select on all tables in schema public to test;   // 起作用的是这句 要进入crm_db 操作,在那个db环境执行就授哪个db的权 
grant usage on schema xxx to test;
必须加上面，否则还是无权限。

更多权限参考：

GRANT ALL PRIVILEGES ON SCHEMA foo TO GROUP bar;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA foo TO GROUP bar;

相关：https://blog.csdn.net/laiyangwen1120/article/details/52678936

PostgreSQL最佳实践：用户权限管理

https://www.modb.pro/db/26499