PostgreSQL的模式管理(八)
PostgreSQL 系列博文讲解(B站同步介绍视频:https://space.bilibili.com/282421760)
支持一下,关注一波微信公众号:【 钥道不止 】
1. schema
1.1. schema的理解
schema(模式)可以理解为数据库下的一个命名空间或目录,一个库下可有多个schema,但不能重名。不同的schema下可以有相同的表、函数等对象且互相是不冲突的。只要有权限,每个schema的对象是可以互相调用的。
1.2. PG使用schema的原因
- 允许多个用户在使用同一数据库时彼此互不干扰
- 把数据库对象放在不同的模式下,然后组织成逻辑组,便于管理
- 第三方应用可以放在不同的模式中,这样就不会和其他对象的名字冲突
1.3. 注意项
- MySQL数据库迁移到PG中,假设MySQL实例中有三个数据库,在迁移到PG时,应该在PG创建三个模式与MySQL实例中的三个数据库相对应,而不是在PG创建三个数据库与之对应。如果需要在这些数据库上实现最大限度的移植性,或许不应该使用模式。
- PG的Database、模式这些概念与Oracle、MySQL的不同。
2.public schema
PG默认公共模式 public ,任何用户都能在该模式下创建对象。相当于任何用户都有这个schema的查询权限。
#让数据库中所有用户都能查询表mytab
GRANT select ON TABLE mytab TO public;
#回收 public 权限
REVOKE CREATE ON SCHEMA public from public;
#授权 public 权限
GRANT CREATE ON SCHEMA public to public;
根据 SQL 标准,模式的拥有者总是拥有其中的所有对象。
#创建schema
create schema schema_name authorization user_name;
#修改属主
alter schema schema_name owner to user_name2;
#修改模式名
alter schema schema_name rename to new_name;
#删除schema,cascade级联删除,即该schema模式下的所有对象都删除
drop schema schema_name (cascade);
- 模式只能由其拥有者或超级用户删除。
- 即使拥有者不拥有该模式中的某些对象,也能删除该模式(cascade)。
# psql连接数据库后查看数据库的schema
\dn
# 查看当前模式搜索路径
show search_path;
# 查看当前模式
select current_schema;
# 进入schemaname模式
SET search_path TO schemaname;
# 修改角色的默认搜索路径
alter role username set search_path='schemaname';
# 查询指定模式下的表
select tablename from pg_tables where schemaname = 'xxoo' order by tablename;
# 多模式访问,查询不加模式(修改后需要退出重进),写在前的模式会隐藏后面模式的同名对象
grant all on schema SCHEMANAME to USERNAME;
alter user USERNAME set search_path to SCHEMANAME1,SCHEMANAME2,public;
DDL语句的权限是在schema上的,每个schema总是属于一个数据库,所以要让用户A能够在用户B的schema中创建表,则需要B用户给A用户赋权
GRANT CREATE ON SCHEMA schema_b TO user_a;