您好,欢迎光临! 请 |

PostgreSQL授予/回收其他用户访问当前用户下所有表的权限小结

作者: | 分类:PostgreSQL | Tag: | 评论:0 
字号:T|T

Q1:PostgreSQL数据库中,如何授予其他用户可以查询当前用户下的所有表的权限?

A1:分2个步骤。首先,赋予其他用户访问当前用户下所有表的查询权限;其次,其他用户要有使用当前用户的schema的权限。

例子:当前库为testdb,当前用户为testu1,schema为test_schema,表为t1,t2。如何让其他用户testu2有查询t1、t2表的权限?

当前数据库版本为EDB 9.3,以testu1连接到testdb数据库,在test_schema下有2张表,t1和t2,各有1条记录。

testdb=> select version;
                                                       version                                                       
---------------------------------------------------------------------------------------------------------------------
 EnterpriseDB 9.3.11.33 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-55), 64-bit
(1 row)

testdb=> \c
You are now connected to database "testdb" as user "testu1".
testdb=> \dn
      List of schemas
    Name     |    Owner     
-------------+--------------
 public      | enterprisedb
 test_schema | testu1
(2 rows)

testdb=> \d            
          List of relations
   Schema    | Name | Type  | Owner  
-------------+------+-------+--------
 test_schema | t1   | table | testu1
 test_schema | t2   | table | testu1
(2 rows)

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

testdb=> select * from t2;
 id 
----
  2
(1 row)

testdb=>

同时,testu2用户连接testdb时,不能访问testu1在test_schema下的表。提示,对模式 test_schema 权限不够。

testdb=> \c
You are now connected to database "testdb" as user "testu2".
testdb=> \dn
      List of schemas
    Name     |    Owner     
-------------+--------------
 public      | enterprisedb
 test_schema | testu1
(2 rows)

testdb=> select * from test_schema.t1;
错误:  对模式 test_schema 权限不够
LINE 1: select * from test_schema.t1;
                      ^
testdb=>

接下来,testu1用户授权给testu2使用test_schema的权限。

testdb=> \c
You are now connected to database "testdb" as user "testu1".
testdb=> grant USAGE on SCHEMA test_schema to testu2;
GRANT
testdb=>

授权之后,看看testu2能否访问testu1在test_schema下的表?

 
postgres=# \c testdb testu2;
You are now connected to database "testdb" as user "testu2".
testdb=> select * from test_schema.t1;
错误:  对关系 t1 权限不够
testdb=>

不再提示对模式 test_schema 权限不够,而是对表的查询权限不够。
接下来,授予testu2对于test_schema下所有表的查询权限:

 
testdb=> \c
You are now connected to database "testdb" as user "testu1".
testdb=> grant SELECT on all tables in schema test_schema to testu2;
GRANT
testdb=>

最后,以testu2用户登录testdb数据库时,可以顺利访问testu1用户在test_schema下的所有表了:

testdb=> \c
You are now connected to database "testdb" as user "testu2".
testdb=> select * from test_schema.t1;
 id 
----
  1
(1 row)

testdb=> select * from test_schema.t2;
 id 
----
  2
(1 row)

testdb=>

Q2:对于已经授予某个用户查询当前用户下的指定schema下所有表的情况下,当前用户下在指定schema下创建的新表,其他用户是否有权限访问?如果没有,该怎么赋予其他用户查询当前用户新建的表?

A2:对于新建的表,其他用户没有查询权限。可以通过alter default privileges来赋予访问新建的表的权限。具体如下:

testu2用户在testdb数据库下的test_schema下新建t3表,testu2自己正常访问没问题。而testu2连接testdb数据库,则不能查询新建的t3表。

testdb=> \c
You are now connected to database "testdb" as user "testu1".
testdb=> create table t3(id int);
CREATE TABLE
testdb=> insert into t3 values(3);
INSERT 0 1
testdb=> select * from t3;
 id 
----
  3
(1 row)

testdb=> \c testdb testu2
You are now connected to database "testdb" as user "testu2".
testdb=> select * from test_schema.t3;
错误:  对关系 t3 权限不够
testdb=>

可以通过下述2种方法中的任意一种,来使testu2用户可以查询test1用户新建的t3表:

方法1

 testdb=> \c testdb testu1
You are now connected to database "testdb" as user "testu1".
testdb=> grant SELECT on all tables in schema test_schema to testu2;
GRANT
testdb=> \c testdb testu2
You are now connected to database "testdb" as user "testu2".
testdb=> select * from test_schema.t3;                              
 id 
----
  3
(1 row)

testdb=>

或者,方法2:

 
testdb=> \c testdb testu1
You are now connected to database "testdb" as user "testu1".
testdb=> revoke SELECT on t3 from testu2;
REVOKE
testdb=> \c testdb testu2
You are now connected to database "testdb" as user "testu2".
testdb=> select * from test_schema.t3;                  
错误:  对关系 t3 权限不够
testdb=> \c testdb testu1
You are now connected to database "testdb" as user "testu1".
testdb=> grant SELECT on t3 to testu2;
GRANT
testdb=> \c testdb testu2
You are now connected to database "testdb" as user "testu2".
testdb=> select * from test_schema.t3;  
 id 
----
  3
(1 row)

testdb=>

显然,上述的2种方法都不是最好的方法。不可能对于每一张新建的表都重新执行一次赋权操作。通过alter default privileges赋权操作来实现。

 
testdb=> \c testdb testu1
You are now connected to database "testdb" as user "testu1".
testdb=> alter default privileges in schema test_schema grant SELECT on tables to testu2;
ALTER DEFAULT PRIVILEGES
testdb=> create table t4(id int);
CREATE TABLE
testdb=> insert into t4 values(4);
INSERT 0 1
testdb=> select * from t4;
 id 
----
  4
(1 row)

testdb=> \c testdb testu2
You are now connected to database "testdb" as user "testu2".
testdb=> select * from test_schema.t4;                                                   
 id 
----
  4
(1 row)

testdb=>

此外,可以通过\ddp命令来查看默认权限[\describe default privileges,我猜测这条命令的简写意义所在]:

testdb=> \ddp+
            Default access privileges
 Owner  |   Schema    | Type  | Access privileges 
--------+-------------+-------+-------------------
 testu1 | test_schema | table | testu2=r/testu1
(1 row)

testdb=> 

Q3:对于已经拥有默认权限的用户的情况下,如何删除?即,如何处理类似DETAIL: privileges for default privileges on new relations belonging to role testu1 in schema test_schema的错误?

A3:需要3个步骤。1,回收schema的usage权限;2,回收对所有表的查询权限;3,执行类似alter default privileges来回收默认权限后再删除。

即使通过最高权限的数据库管理员来删除testu2用户也报错,甚至加上cascade也删除不成功。

postgres=# \c
You are now connected to database "postgres" as user "enterprisedb".
postgres=# drop user testu2;
错误:  无法删除"testu2"因为有其它对象倚赖它
DETAIL:  在数据库 testdb中的6个对象
postgres=# drop user testu2 cascade;
错误:  无法删除"testu2"因为有其它对象倚赖它
DETAIL:  在数据库 testdb中的6个对象
postgres=#

依次回收schema的usage权限、回收所有表的查询权限、最后通过alter default privileges来回收默认权限,同时通过\ddp查看的默认权限为空:

testdb=> \c
You are now connected to database "testdb" as user "testu1".
testdb=> revoke USAGE on SCHEMA test_schema from testu2;
REVOKE
testdb=> revoke SELECT on all tables in schema test_schema from testu2;
REVOKE
testdb=> alter default privileges in schema test_schema revoke SELECT on tables from testu2;
ALTER DEFAULT PRIVILEGES
testdb=> \ddp
         Default access privileges
 Owner | Schema | Type | Access privileges 
-------+--------+------+-------------------
(0 rows)

testdb=> 

最后,通过管理员来顺利删除testu2用户:

postgres=# drop user testu2;
DROP ROLE
postgres=# 

小结:
1 把当前用户下某个schema下的表的查询权限赋给其他用户,既需要把schema的usage权限给其他用户,也要赋予其他用户对于表的查询权限(可以指定特定表来赋权查询操作,也可以赋予一次性赋予所有表的查询权限);
2 对于将来新建表的查询权限想要一次性的赋予权限,则需要通过alter default privileges赋予默认权限来操作;
3 回收权限的话,需要回收schema的usage权限,也要回收表的查询权限,还有回收默认权限;
4 可以通过\ddp来查看默认赋权信息。

顶一下
(0)
100%
踩一下
(0)
100%

发表评论

插入图片

NOTICE1:请申请gravatar头像,没有头像的评论可能不会被回复|头像相关帮助!