Thread: permission denied for schema
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?
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
On 4/1/20 6:35 AM, AC Gomez wrote: > 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 Looks to me the issue is that whatever user is running: select * from mysch.dblink('link_b', 'select usename from PG_USER limit 1;') as t(uu text); does not have access to objects in schema mysch on the originating server. > > 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 -- Adrian Klaver adrian.klaver@aklaver.com