Re: permission denied for schema

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(dot)klaver(at)aklaver(dot)com