postgres 基于Schema 权限访问探讨
01,环境配置
创建用户,和schema
postgres=# create user test1 with password 'test1'; CREATE ROLE postgres=# create user test2 with password 'test2'; CREATE ROLE postgres=# create schema u1; CREATE SCHEMA postgres=# create schema u2; CREATE SCHEMA postgres=# insert into u1.txt1 values (1,'hello') ; INSERT 0 1 postgres=# insert into u1.txt2 values (2,'world') ; INSERT 0 1 postgres=# insert into u2.txt1 values (1,'hello') ; INSERT 0 1 postgres=# insert into u2.txt2 values (2,'world') ; INSERT 0 1 创建环境
02,权限访问
postgres=# select current_user -- 查看当前的schema postgres-# ; current_user -------------- postgres (1 row) postgres=# show search_path -- 看到当前是public schema postgres-# ; search_path ----------------- "$user", public (1 row) postgres=# \dt -- 查看当前的表,发现并没有刚刚创建的表 List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | test | table | postgres
postgres=# \c postgres test1 ; --登入到 test1 用户 You are now connected to database "postgres" as user "test1". postgres=> select current_user; ---查看当前用户 current_user -------------- test1 (1 row) postgres=> \dt; -- 发现当前只能读取public的表 List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | test | table | postgres (1 row) postgres=> show search_path ; ---看当前schema search_path ----------------- "$user", public (1 row) postgres=> set search_path = 'u1'; ---切换到u1 SET postgres=> show search_path ; -查看 search_path ------------- u1 (1 row) postgres=> \dt; --发现并没有 表出来,我不是创建的时候指定了吗? Did not find any relations. postgres=> set search_path = 'u1'; SET postgres=> show search_path ; search_path ------------- u1 (1 row) postgres=> \dt; Did not find any relations.
postgres=> select * from u1.test1; --- 查询,没有权限
ERROR: permission denied for schema u1
LINE 1: select * from u1.test1;
我们通过 postgres 用户来看下
postgres=> \c postgres postgres You are now connected to database "postgres" as user "postgres". postgres=# show search_path postgres-# ; search_path ----------------- "$user", public (1 row) postgres=# \d+ List of relations Schema | Name | Type | Owner | Size | Description --------+------+-------+----------+-------+------------- public | test | table | postgres | 16 kB | (1 row) postgres=# set search_path = u1; SET postgres=# \d+ List of relations Schema | Name | Type | Owner | Size | Description --------+------+-------+----------+------------+------------- u1 | txt1 | table | postgres | 8192 bytes | u1 | txt2 | table | postgres | 8192 bytes |
发现是没有问题的。是不是权限不够呢?
那我授权下
postgres=# grant SELECT on u1.txt1 to test1 ; GRANT postgres=# \c postgres test1; You are now connected to database "postgres" as user "test1". postgres=> \d+ List of relations Schema | Name | Type | Owner | Size | Description --------+------+-------+----------+-------+------------- public | test | table | postgres | 16 kB | (1 row) postgres=> set search_path = u1; SET postgres=> \d+ Did not find any relations. postgres=> select * from u1.txt1 ; ERROR: permission denied for schema u1 LINE 1: select * from u1.txt1 ; ^
发现还是权限不够
这时候其实是少了一个权限
postgres=# grant USAGE on SCHEMA u1 to test1 ; GRANT postgres=# \c postgres test1 ; You are now connected to database "postgres" as user "test1". postgres=> \dt ; List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | test | table | postgres (1 row) postgres=> set search_path = u1 ; SET postgres=> \dt ; List of relations Schema | Name | Type | Owner --------+------+-------+---------- u1 | txt1 | table | postgres u1 | txt2 | table | postgres
postgres=> select * from txt1
postgres-> ;
id | name
----+-------
1 | hello
1 | hello
(2 rows)
这样设置就可以了
所以一般使用schema 的时候注意:
1 需要用postgres 授权指定的schema 的使用(USAGE)权限给特定用户
2 然后授权postgres 需要的权限到特定用户
缺一不可
人生就像一滴水,非要落下才后悔!
--kingle