PostgreSql 默认 SCHEMA 再探究

语言: CN / TW / HK

theme: channing-cyan

这是我参与11月更文挑战的第6天,活动详情查看:2021最后一次更文挑战

为何建议为用户指定默认 SCHEMA

为了不用用户创建对象时,需要显示地指定 schema,如:

sql --不需要这样 CREATE TABLE em.emp(id int); --可以这样 CREATE TABLE emp(id int); PostgreSql中,所有新建的数据库都会默认自带一个 public 模式,所有用户均可在这个模式下创建管理自己的对象,且在数据库中创建表、视图等基本对象时,若没有指定模式,这些对象默认也会存放在 pubilc 模式下。(用户名与模式名同名除外) 详情参考:https://juejin.cn/post/6987199440620781575

上述情况发生的原因

上述原因在于 postgresql 数据库中的默认搜索路径:search_path。

```sql oa=# show search_path; search_path


"$user", public (1 row) ``` 观察上述搜索路径可看到,用户连入数据库后首先会找寻跟自己用户名同名的 SCHEMA,若能找到,就它作为当前 schema,后续创建的所有对象都会存入到这个 schema 下。(前提是用户要有访问这个schema 的权限)

```sql --测试名字与schema名不同的用户,即使有访问 schema 的权限,连入时也不是当前 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)

--测试与 schema 同名用户,需要有访问该 schema 的权限,连入时才为当前 schema。(两种授权访问 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)更改 schema 的 owner 为这个用户,用户自然就具有了访问 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)直接授予用户访问 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) ```

如何指定上述情况中普通用户 syd 的默认 schema

```sql 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) ```

总结

```sql --设置 apply_owner 用户连入任何数据库,当前默认 schema 都为 apply(数据库中没有这 schema 时,默认 schema 为 public)
alter user apply_owner set search_path to apply; --设置任何用户连入 chis 数据库,当前默认 schema 都为 apply alter database chis set search_path to apply; --用户名与 schema 同名,且用户具有访问改 schema 的权限,用户连入数据库时,默认即为当前 schema create database chis; \c chis create schema apply; create user apply password '11111';

grant USAGE on schema apply to apply; 或 alter schema apply owner to apply; ```