Resolved myself, don’t panic Mr Mannering.
From: Adam Pearson
Sent: 03 December 2015 11:16 AM
To: 'pgAdmin Support'
Subject: pgAdmin and Postgres_fdw needing superuser access
Hello All,
Has anyone encountered a quirk with PGAdmin and Postgres_fdw requiring superuser access?
For example.
Run the below under my useraccount, sudo and it returns a record set.
select id from foreign_address limit 10;
Run the same code in PGAdmin, my normal user account and it reports
ERROR: password is required
DETAIL: Non-superuser cannot connect if the server does not request a password.
HINT: Target server's authentication method must be changed.
********** Error **********
ERROR: password is required
SQL state: 2F003
Detail: Non-superuser cannot connect if the server does not request a password.
Hint: Target server's authentication method must be changed.
However, if I just run a normal select on a non-foreign table it works.
Also, if I change my account to be a superuser it works. Confuses me slightly, since when setting up Postgres_fdw you specify the connection details in the user mapping.
Is this a problem with pgAdmin, or Postgres_fdw? Seems to indicate an issue with pgAdmin?
Thanks & Regards,
Adam Pearson