schema权限控制

默认情况下，用户不能访问不属于他们的模式中的任何对象。要允许这样做，模式的所有者必须授予该模式的USAGE特权。为了允许用户使用模式中的对象，可能需要为对象授予额外的权限。

还允许用户在其他人的模式中创建对象。要允许这样做，需要授予模式上的CREATE特权。注意，默认情况下，每个人在公共模式上都有CREATE和USAGE特权。这允许所有能够连接到给定数据库的用户在其公共模式中创建对象。一些使用模式要求撤销该特权:

db3=> \c db3 postgres
You are now connected to database "db3" as user "postgres".
db3=# create schema sb3 ;
CREATE SCHEMA
db3=# create table sb3.sb4(id int);
CREATE TABLE
db3=# \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | sb3  | table | postgres
 public | t    | table | postgres
 public | t1   | table | test
(3 rows)

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

db3=# set search_path ="$user", public,sb3;
SET
db3=# \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | sb3  | table | postgres
 public | t    | table | postgres
 public | t1   | table | test
 sb3    | sb4  | table | postgres
(4 rows)

--test用户无法访问不属于test的模式中的任何对象
db3=# \c db3 test
You are now connected to database "db3" as user "test".
db3=> \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | sb3  | table | postgres
 public | t    | table | postgres
 public | t1   | table | test
(3 rows)

--切换到owner或者supper用户进行授权
db3=>  \c db3 postgres
You are now connected to database "db3" as user "postgres".

--授予他们可以访问的权限，模式的所有者必须授予该模式的USAGE特权
db3=# grant USAGE on SCHEMA sb3 to test;
GRANT
db3=# \c db3 test
You are now connected to database "db3" as user "test".
db3=> \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | sb3  | table | postgres
 public | t    | table | postgres
 public | t1   | table | test
(3 rows)

--依然无法访问sb3模式下的对象，对search_path进行设置
db3=> show search_path ;
   search_path
-----------------
 "$user", public
(1 row)

db3=> set search_path ="$user", public,sb3;
SET
db3=> show search_path ;
     search_path
----------------------
 "$user", public, sb3
(1 row)

db3=> \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | sb3  | table | postgres
 public | t    | table | postgres
 public | t1   | table | test
 sb3    | sb4  | table | postgres
(4 rows)

--为了允许用户使用模式中的对象，可能需要为对象授予额外的权限。
db3=> create table sb3.test(id int);
ERROR:  permission denied for schema sb3
LINE 1: create table sb3.test(id int);
                     ^
db3=> \c db3 postgres
You are now connected to database "db3" as user "postgres".

--授予create的权限
db3=# grant CREATE ON SCHEMA sb3 to test;
GRANT
db3=# \c db3 test
You are now connected to database "db3" as user "test".
db3=> create table sb3.test(id int);
CREATE TABLE

--收回create的权限
--第一个“public”是模式，第二个“public”是每一个用户。在第一种意义上，它是一个标识符，在第二种意义上，它是一个关键字，因此大小写不同;回顾第4.1.1节的指导方针。
db3=> \c db3 postgres
You are now connected to database "db3" as user "postgres".
db3=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE

--回收某个用户上的public权限
（db3=# REVOKE CREATE ON SCHEMA public FROM test;）

db3=# \c db3 test
You are now connected to database "db3" as user "test".
db3=> create table public.pub(id int);
ERROR:  permission denied for schema public

参考连接：https://www.postgresql.org/docs/14/ddl-schemas.html#DDL-SCHEMAS-PUBLIC