Monday 21 October 2019

Postgres Drop table : ERROR: must be owner of relation table_name


In Postgres, The right to drop an object is not treated with any grantable privilege. It is only achived by the ownership. How .. ?




So, let say I have an object named article_post, created by user “user2”
User2 tried to grant privilege but it won’t allow the another user to drop his object.


nexprd=> create table article_post(article_name varchar(20));
CREATE TABLE
 
nexprd=> grant delete on article_post to nikhil ;            <----- 
GRANT
nexprd=> grant all privileges on article_post to nikhil ;    <-----
GRANT
 
 
nexprd=> select current_user;
 current_user
--------------
 nikhil
(1 row)
 
nexprd=> drop table article_post ; 
ERROR:  must be owner of relation article_post        <----- Not allowing to drop
nexprd=> drop table article_post ;
ERROR:  must be owner of relation article_post
 


The error tells that, I don't own the table (or relation)  I’m  trying to modify.

So, the user ‘Nikhil’ wasn't a direct/indirect member of the new owning role...


nexprd=> \d
                  List of relations
 Schema |        Name         |   Type   |   Owner
--------+---------------------+----------+-----------
 public | account             | table    | postgres
 public | account_user_id_seq | sequence | postgres
 public | article             | table    | postgres
 public | article_post        | table    | user2    <<=====
 public | articles            | table    | john
 public | k1                  | table    | postgres
 public | persons             | table    | postgres
 public | persons_id_seq      | sequence | postgres
 public | test_id_seq         | sequence | postgres
(9 rows)



So the solution was quite simple - I've just done this grant:


nexprd=> grant user2 to nikhil ;
GRANT ROLE


Note: You cannot grant object ownership as :


nexprd=> grant article_post to nikhil ;
ERROR:  role "article_post" does not exist


nexprd=> drop table article_post ;
DROP TABLE
nexprd=>


Same can be achieved using PgAdmin.

Login to PgAdmin as user >> Right click on username, Click on 'Properties'




A diglog box will be opened in console, 

Click on membership tab, and select the owner of object.




Now, usergroup 'Nikhil' will be able to drop the object.





No comments:

Post a Comment