postgresql只有owner或usersuper許可權才能修改表結構或drop表

lusklusklusk發表於2020-03-25

官方文件的一段說明
For most kinds of objects, the initial state is that only the owner (or a superuser) can do anything with the object.
The right to modify or destroy an object is always the privilege of the owner only.
對於大多數型別的物件,初始狀態是隻有所有者(或超級使用者)可以對物件執行任何操作。
修改或銷燬物件的權利始終只是所有者的特權。



表s1.u1_table的owner是u1,使用者postgres是超級管理員,使用者u2擁有表s1.u1_table的所有許可權和schema s1的所有許可權,使用者u2都無法修改該表的表結構或drop該表,只有超級管理員或s1.u1_table的owner才能修改該表的表結構或drop該表

t1=> \c
You are now connected to database "t1" as user "u1".
t1=> create table s1.u1_table(hid int);
CREATE TABLE
t1=> \c - postgres
You are now connected to database "t1" as user "postgres".
t1=# select usename,usesuper from pg_user where usename='postgres';
 usename  | usesuper
----------+----------
 postgres | t
t1=# select * from pg_tables where tablename='u1_table';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
 s1         | u1_table  | u1         |            | f          | f        | f           | f
t1=# create user u2 password '123456';
CREATE ROLE
t1=# grant all privileges on schema s1 to u2;
GRANT
t1=# grant all privileges on table s1.u1_table to u2;
GRANT
t1=# \c - u2;
You are now connected to database "t1" as user "u2".
t1=> alter table s1.u1_table add hid2 int;
ERROR:  must be owner of table u1_table
t1=> drop table s1.u1_table;
ERROR:  must be owner of table u1_table
t1=> \c - postgres
You are now connected to database "t1" as user "postgres".
t1=# alter table s1.u1_table add hid2 int;
ALTER TABLE
t1=# drop table s1.u1_table;
DROP TABLE

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30126024/viewspace-2682540/,如需轉載,請註明出處,否則將追究法律責任。

相關文章