#postgres官网有段关于讲解对象权限解释
上图标记部分,讲到关于对象权限问题,大概意识为创建对象时,会为其指定一个所有者。所有者通常是执行创建语句的角色。对于大多数类型的对象,初始状态是只有所有者(或超级用户)可以对对象执行任何操作。若要允许其他角色使用它,必须授予特权。
关于这里的特权没有给出更为详细说明,我从国外帖子上找到一部分相关这的解释为superuser权限。
#首先讲讲postgres数据库关于表权限问题?
在postgres数据库中,表的权限并没有像oracle数据库的alter any table、select any table、drop any table等等这样的权限,所以大家都很困惑这种权限怎么写、怎么授权的问题;这就和postgres数据库本身属性有关系,首先在postgres数据库建表如果不指定schema的情况,创建表都会在数据库默认的public模式下,表所属的Owner是你当前用户;如果创建表时指定 schema,并且schema的也指定owner,那表会建议到你指定的Schema下,表所属的Owner还是你当前用户。这个和oracle不一样,在oracle中使用A用户在B用户下建表,表的Owner是B。
#关于表Owner有问题解决方法
如果的你觉的表Owner有问题,通过以下方式进行修改:
1.可以通过权限高的用户或是拥有表owner用户权限的用户执行alter table tablename owner to new_owner的命令行进行更改
2.可以通过drop table tablename命令删除表后,连接到正确的用户下进行重新建表。
3.如果你想要将某个用户下的对象全部修改成另外用户,你可以执行REASSIGN OWNED BY OLDNAME TO NEWNAME的方式进行,不过这种方式修改的范围比较大,详情请阅读我上次发布的贴子
案例:
本案例中:数据库中antdb用户是具有superuser权限超级用户,数据库列外两个用户ngad_result,aijh是普通用户;aijh用户具有all权限在ngad_result schema下中的表。
yzxdb=> \dn ---获取schema信息
List of schemas
Name | Owner
-------------+-------------
ngad_result | ngad_result1.使用ngad_result用户登录yzxdb数据库,查询库里所有表信息,发现库中所有表的Owner都是ngad_result,我现在有需要想让test2表的Owner变成aijh用户,看看以下操作怎么实现。
方法一:通过高权限用户修改表Owner
yzxdb=# \c yzxdb ngad_result
You are now connected to database "yzxdb" as user "ngad_result".
yzxdb=> \d+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
-------------+-------+-------+-------------+-------------+------------+-------------
ngad_result | a | table | ngad_result | permanent | 16 kB |
ngad_result | test1 | table | ngad_result | permanent | 8192 bytes |
ngad_result | test2 | table | ngad_result | permanent | 16 kB |
(3 rows)
yzxdb=> alter table test2 owner to aijh;
ERROR: must be member of role "aijh"注:我们发现通过ngad_result用户修改test2表报错,报必须是角色“aijh”的成员错误。
通过高权限用户修改:
antdb=# \c yzxdb antdb
You are now connected to database "yzxdb" as user "antdb".
yzxdb=# alter table ngad_result.test2 owner to aijh;
ALTER TABLE ----成功了
yzxdb=# \conninfo
You are connected to database "yzxdb" as user "antdb" via socket in "/tmp" at port "5432".我们切换ngad_result 用户,看表权限:
yzxdb=# \c yzxdb ngad_result
You are now connected to database "yzxdb" as user "ngad_result".
yzxdb=> \d+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
-------------+-------+-------+-------------+-------------+------------+-------------
ngad_result | a | table | ngad_result | permanent | 16 kB |
ngad_result | test1 | table | ngad_result | permanent | 8192 bytes |
ngad_result | test2 | table | aijh | permanent | 16 kB |
(3 rows)方法二:如果我们没有高权限用户,怎么办呢?可以通过授权特殊授权后进行alter table操作。
我们接着上个案例进行,我们想把test2表的owner修改成原来的owner为ngad_result,怎么办?可以通过方法一高权限用户进行修改,另外可以通过以下这种方式进行修改。
yzxdb=> \d+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
-------------+-------+-------+-------------+-------------+------------+-------------
ngad_result | a | table | ngad_result | permanent | 16 kB |
ngad_result | test1 | table | ngad_result | permanent | 8192 bytes |
ngad_result | test2 | table | aijh | permanent | 16 kB |
(3 rows)yzxdb=> \conninfo
You are connected to database "yzxdb" as user "ngad_result" via socket in "/tmp" at port "5432".yzxdb=> alter table test2 owner to ngad_result;
ERROR: must be owner of table test2注:我们发现报的错和方案一中的报错不一致,报必须是表test2的所有者。
我们通过另一种方法将表owner用户授权给ngad_result方式实现。
yzxdb=# grant aijh to ngad_result;
GRANT ROLE
yzxdb=# \duS+ ngad_result
List of roles
Role name | Attributes | Member of | Description
-------------+------------+-----------+-------------
ngad_result | | {aijh} |我们切换ngad_result 用户,执行alter table命令:
yzxdb=> \conninfo
You are connected to database "yzxdb" as user "ngad_result" via socket in "/tmp" at port "5432".yzxdb=> alter table test2 owner to ngad_result;
ALTER TABLE ---成功了
yzxdb=> \conninfo
You are connected to database "yzxdb" as user "ngad_result" via socket in "/tmp" at port "5432".
yzxdb=> \d+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
-------------+-------+-------+-------------+-------------+------------+-------------
ngad_result | a | table | ngad_result | permanent | 16 kB |
ngad_result | test1 | table | ngad_result | permanent | 8192 bytes |
ngad_result | test2 | table | ngad_result | permanent | 16 kB |
(3 rows)备注:方案二是一种用户赋权用户的方式,将用户下对象权限授权给另外用户的方法实现,在postgres官方上并没有将关于这种方法,在国外的帖子中,这种方案有被使用。有感兴趣的同学可以找找国外的帖子或是英文书籍进行了解。
#关于用户修改其他用户表结构问题?
做为dba你很可能遇到使用一个A用户去修改一个B用户下的表结构或是表字段,并报错的问题;这个问题归根结底还是表Owner问题引发导致。接下来我们聊聊修改其他用户表结构或是字段方法
1.一般是通过表的owner用户进行修改,对于postgres数据库设计理念,自己维护自己的表,和其他用户互不干扰。
2.通过一个具有Superuser权限的用户去修改任何一会用户下的表结构或是字段类型。
3.通过将表owner用户授权给其他用户,那其他用户就可以修改owner用户的表啦。
案例:
方法一: 通过owner用户自己修改自己表结构
yzxdb=> \conninfo
You are connected to database "yzxdb" as user "ngad_result" via socket in "/tmp" at port "5432".
yzxdb=> \d+ test2
Table "ngad_result.test2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
name | character varying(20) | | | | extended | |
shenggao | numeric(12,0) | | | | main | |
addr | text | | | | extended | |
Access method: heapyzxdb=> alter table test2 add info text;
ALTER TABLE
yzxdb=> \d+ test2
Table "ngad_result.test2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
name | character varying(20) | | | | extended | |
shenggao | numeric(12,0) | | | | main | |
addr | text | | | | extended | |
info | text | | | | extended | |
Access method: heapyzxdb=> alter table test2 ALTER COLUMN info TYPE varchar(100);
ALTER TABLE
yzxdb=> yzxdb=> \d+ test2
Table "ngad_result.test2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+------------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
name | character varying(20) | | | | extended | |
shenggao | numeric(12,0) | | | | main | |
addr | text | | | | extended | |
info | character varying(100) | | | | extended | |
Access method: heap
方法二:通过具有Superuser用户进行修改,我这使用antdb用户
yzxdb=# \conninfo
You are connected to database "yzxdb" as user "antdb" via socket in "/tmp" at port "5432".
yzxdb=# alter table ngad_result.test2 drop info;
ALTER TABLE
yzxdb=# \d+ ngad_result.test2
Table "ngad_result.test2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
name | character varying(20) | | | | extended | |
shenggao | numeric(12,0) | | | | main | |
addr | text | | | | extended | |
Access method: heapyzxdb=# alter table ngad_result.test2 add info text;
ALTER TABLE
yzxdb=# \d+ ngad_result.test2
Table "ngad_result.test2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
name | character varying(20) | | | | extended | |
shenggao | numeric(12,0) | | | | main | |
addr | text | | | | extended | |
info | text | | | | extended | |
Access method: heapyzxdb=# alter table ngad_result.test2 ALTER COLUMN info TYPE varchar(100);
ALTER TABLE
yzxdb=# \d+ ngad_result.test2
Table "ngad_result.test2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+------------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
name | character varying(20) | | | | extended | |
shenggao | numeric(12,0) | | | | main | |
addr | text | | | | extended | |
info | character varying(100) | | | | extended | |
Access method: heap
方法三: 通过将表owner用户授权给其他用户
yzxdb=# grant ngad_result to aijh;
GRANT ROLE
yzxdb=# \duS+ aijh
List of roles
Role name | Attributes | Member of | Description
-----------+------------+-----------------------------+-------------
aijh | | {ngad_result} |yzxdb=> \conninfo
You are connected to database "yzxdb" as user "aijh" via socket in "/tmp" at port "5432".
yzxdb=> \d+ ngad_result.test2
Table "ngad_result.test2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+------------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
name | character varying(20) | | | | extended | |
shenggao | numeric(12,0) | | | | main | |
addr | text | | | | extended | |
info | character varying(100) | | | | extended | |
Access method: heap
yzxdb=> alter table ngad_result.test2 drop info ;
ALTER TABLE
yzxdb=> \d+ ngad_result.test2
Table "ngad_result.test2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
name | character varying(20) | | | | extended | |
shenggao | numeric(12,0) | | | | main | |
addr | text | | | | extended | |
Access method: heapyzxdb=> alter table ngad_result.test2 add info text;
ALTER TABLE
yzxdb=> \d+ ngad_result.test2
Table "ngad_result.test2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
name | character varying(20) | | | | extended | |
shenggao | numeric(12,0) | | | | main | |
addr | text | | | | extended | |
info | text | | | | extended | |
Access method: heapyzxdb=> alter table ngad_result.test2 ALTER COLUMN info TYPE varchar(100);
ALTER TABLE
yzxdb=> \d+ ngad_result.test2
Table "ngad_result.test2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+------------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
name | character varying(20) | | | | extended | |
shenggao | numeric(12,0) | | | | main | |
addr | text | | | | extended | |
info | character varying(100) | | | | extended | |
Access method: heap