postgresql中schema概念

最新推荐文章于 2024-07-04 09:25:27 发布

weixin_33997389

最新推荐文章于 2024-07-04 09:25:27 发布

阅读量3k

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/hippora/blog/379540

版权

2019独角兽企业重金招聘Python工程师标准>>>

schema概念有点像命名空间或者把它想像成一个文件系统中的目录,差别就是这个schema下不能再有schema嵌套.
各个对象比如表,函数等存放在各个schema下,同一个schema下不能有重复的对象名字,但在不同schema下可以重复.

使用schema的作用

方便管理多个用户共享一个数据库,但是又可以互相独立.
方便管理众多对象,更有逻辑性
方便兼容某些第三方应用程序,创建对象时是有schema的

比如要设计一个复杂系统.由众多模块构成,有时候模块间又需要有独立性.各模块存放单独的数据库显然是不合适的. 这时候使用schema来分类各模块间的对象,再对用户进行适当的权限控制.这样逻辑也非常清晰.

创建schema

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

在schema中创建对象

db01=# create table schema01.t1(id int);
CREATE TABLE
db01=# insert into schema01.t1 values(1);
INSERT 0 1
db01=# select * from t1;
ERROR:  relation "t1" does not exist
LINE 1: select * from t1;
                      ^
db01=# select * from schema01.t1;
 id 
----
  1
(1 row)
db01=# select * from db01.schema01.t1;
 id 
----
  1
(1 row)

默认是在public这个schema下.因此要带上schema名称.数据库名字如果要带上,只能是当前连接的数据库!!

删除schema

db01=# drop schema schema01;
ERROR:  cannot drop schema schema01 because other objects depend on it
DETAIL:  table schema01.t1 depends on schema schema01
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
db01=# drop schema schema01 cascade;
NOTICE:  drop cascades to table schema01.t1
DROP SCHEMA

schema下有对象如果需要一起删除,需要带上cascade关键字.有点像使用rmdir删除目录一样,文件夹下有东西不然删除.

创建schema指定owner

默认是谁创建的schema,owner就是谁,当然也可以指定

db01=# create schema s01 authorization hippo;
CREATE SCHEMA
db01=# create schema authorization hippo;
CREATE SCHEMA
db01=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 hippo  | hippo
 public | postgres
 s01    | hippo
(3 rows)

指定了owner,不指定schema,则schema名字与owner一致

在oracle中,默认创建用户的时候,创建了一个和用户名一样的schema下,并且互相绑定.这里有点类似

schema 的搜索路径(Search Path)

如果每个schema下都有同名的对象(比如表t),我查询的是哪个schema下的表呢

pg下通过一个搜索路径参数来控制, search_path,我们先来模拟些数据

db01=# \c - hippo
You are now connected to database "db01" as user "hippo".
db01=# create table public.t1(id text);
CREATE TABLE
db01=# insert into public.t1 values('public');
INSERT 0 1
db01=# create table s01.t1(id text);
CREATE TABLE
db01=# insert into s01.t1 values('s01');
INSERT 0 1
db01=# create table hippo.t1(id text);
CREATE TABLE
db01=# insert into hippo.t1 values('hippo');
INSERT 0 1
db01=> select * from pg_tables where tablename = 't1';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers 
------------+-----------+------------+------------+------------+----------+-------------
 public     | t1        | hippo      |            | f          | f        | f
 s01        | t1        | hippo      |            | f          | f        | f
 hippo      | t1        | hippo      |            | f          | f        | f
(3 rows)

测试一下

db01=> show search_path;
  search_path   
----------------
 "$user",public
(1 row)

db01=> select * from t1;
  id   
-------
 hippo
(1 row)

db01=> set search_path = 's01';
SET
db01=> select * from t1;
 id  
-----
 s01
(1 row)

如果不在搜索路径里面,即使存在其他schema下,也会找不到.

db01=> drop table t1;
DROP TABLE
db01=> select * from t1;
ERROR:  relation "t1" does not exist
LINE 1: select * from t1;
                      ^

创建的表存放哪个schema也跟search_path有关

db01=> set search_path=ssssss,s01;
SET
db01=> create table t2(id int);
CREATE TABLE
db01=> \dt        --ssssss这个schema不存在
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 s01    | t2   | table | hippo
(1 row)

db01=> set search_path='';
SET
db01=> create table t3(id int);
ERROR:  no schema has been selected to create in

跟你是哪个用户无关,现在知道为什么之前建的表为什么都是在public下了吧

schema 与权限

只有login权限的用户,对于owner不是自己的schema是没有任何权限的

db01=> \c - postgres
db01=# create schema s02;
CREATE SCHEMA
db01=# create role u02 login;
CREATE ROLE
db01=# create table s02.t1(id int);
CREATE TABLE
db01=# \c - u02
You are now connected to database "db01" as user "u02".
db01=> select * from s02.t1;
ERROR:  permission denied for schema s02
LINE 1: select * from s02.t1;
                      ^
db01=> create table s02.t2(id int);
ERROR:  permission denied for schema s02

赋权usage权限

db01=> \c - postgres
You are now connected to database "db01" as user "postgres".
db01=# grant usage on schema s02 to u02;
GRANT
db01=# \c - u02;
You are now connected to database "db01" as user "u02".
db01=> select * from s02.t1;
ERROR:  permission denied for relation t1

注意,这里提示错误已经不一样了.是没有相应的查询权限

db01=# grant select on all tables in schema s02 to u02;
GRANT
db01=# \c - u02
You are now connected to database "db01" as user "u02".
db01=> select * from s02.t1;
 id 
----
(0 rows)

系统默认将usage,create权限授权给了PUBLIC(所有用户),所以之前新建用户就可以创建表就是这个原因

db01=> create table public.t2(id int);
CREATE TABLE
db01=# revoke usage,create on schema public from public;
REVOKE
db01=# \c - u02
You are now connected to database "db01" as user "u02".
db01=> \dt
No relations found.
db01=> select * fromt t2;
ERROR:  syntax error at or near "fromt"
LINE 1: select * fromt t2;
                 ^

系统schema, pg_catalog

pg_catalog存放了各系统表,内置函数等等,它总是在搜索路径中,需要通过current_schemas看到

db01=> \c - hippo
You are now connected to database "db01" as user "hippo".
db01=> show search_path;
  search_path   
----------------
 "$user",public
(1 row)

db01=> select current_schemas(true);
      current_schemas      
---------------------------
 {pg_catalog,hippo,public}
(1 row)

db01=> set search_path='';
SET
db01=> select current_schemas(true);
 current_schemas 
-----------------
 {pg_catalog}
(1 row)

schema 使用范例

db02=> \c - postgres
You are now connected to database "db02" as user "postgres".
db02=# drop schema public;
DROP SCHEMA
db02=# create role jack login;
CREATE ROLE
db02=# create role joe login;
CREATE ROLE
db02=# create schema authorization jack;
CREATE SCHEMA
db02=# create schema authorization joe;
CREATE SCHEMA
db02=# grant usage on schema jack to joe;
GRANT
db02=# grant usage on schema joe;
GRANT
db02=# grant select on all tables in schema jack to joe;
GRANT
db02=# grant select on all tables in schema joe to jack;
GRANT

删除public这个schema,限制用户只在自己的schema下进行活动,防止在public下占用过多的空间
可以创建一些有限制权限的用户,比如只有增删改查权限的用户只能访问特定的schema,记得修改它的search_path grant select on all tables in schema .. to .. 语句只影响当前schema下的表,之后创建的表不受影响,这个需要注意.

//END

转载于:https://my.oschina.net/hippora/blog/379540