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.
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