From: | AC Gomez <antklc(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | permission denied for schema |
Date: | 2020-04-01 13:35:48 |
Message-ID: | CABtmK-h7pVkTko3ay17K-TDDq-tu+ojo1prLYkvsWgGPt5zi9g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I'm trying to write a function that eventually will rotate users. Currently
I have the code below which works and creates a new user using the prior
user which ultimately has the same rights as the master user, ie, can do
everything.
select mysch.dblink('dbname=mydb user=themasteruser password=abc123
connect_timeout=200000 host=localhost', 'CREATE USER newuname WITH PASSWORD
''pass1'' CREATEDB CREATEROLE;GRANT rds_superuser TO newuname;');
DROP SERVER IF EXISTS fs_link_b CASCADE;
CREATE SERVER fs_link_b FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host
'localhost', port '5432', dbname 'mydb ', connect_timeout '200000');
CREATE USER MAPPING FOR newuname SERVER fs_link_b OPTIONS ("user"
'newuname',password 'pass1');
ALTER SERVER fs_link_b OWNER TO newuname;
However, when I make a connection with the above created user and I try to
run the query below:
select * from mysch.dblink('link_b', 'select usename from PG_USER limit
1;') as t(uu text);
I get this error:
> SQL Error [42501]: ERROR: permission denied for schema mysch
If I created the user based on a master user, then should it not have all
rights as the master user as created above? Or Do I need to do a bunch of
individual GRANTS still?
Thanks
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2020-04-01 14:05:46 | Re: Index selection issues with RLS using expressions |
Previous Message | Nicola Contu | 2020-04-01 12:51:07 |