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;');

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?


Grant usage on schema mysch to newuname;

Grants on a database object doesn't GRANT rights to the schemas.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.