Thread: pgAdmin and Postgres_fdw needing superuser access

pgAdmin and Postgres_fdw needing superuser access

From

Adam Pearson

Date:

03 December 2015, 14:18:15

<div class="WordSection1"><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">HelloAll,</span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">               Has anyone encountered a quirk with PGAdmin
andPostgres_fdw requiring superuser access?</span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><pclass="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Forexample.</span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><pclass="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Runthe below under my useraccount, sudo and it returns a
recordset.</span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><pclass="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">selectid from foreign_address limit 10;</span><p
class="MsoNormal"><spanlang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><p
class="MsoNormal"><spanlang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Run the same code in
PGAdmin,my normal user account and it reports</span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><pclass="MsoNormal"><i><span lang="EN-US"
style="font-size:8.0pt;font-family:"Calibri","sans-serif"">ERROR: password is required</span></i><p
class="MsoNormal"><i><spanlang="EN-US" style="font-size:8.0pt;font-family:"Calibri","sans-serif"">DETAIL: 
Non-superusercannot connect if the server does not request a password.</span></i><p class="MsoNormal"><i><span
lang="EN-US"style="font-size:8.0pt;font-family:"Calibri","sans-serif"">HINT:  Target server's authentication method
mustbe changed.</span></i><p class="MsoNormal"><i><span lang="EN-US"
style="font-size:8.0pt;font-family:"Calibri","sans-serif"">**********Error **********</span></i><p
class="MsoNormal"><i><spanlang="EN-US" style="font-size:8.0pt;font-family:"Calibri","sans-serif""> </span></i><p
class="MsoNormal"><i><spanlang="EN-US" style="font-size:8.0pt;font-family:"Calibri","sans-serif"">ERROR: password is
required</span></i><pclass="MsoNormal"><i><span lang="EN-US"
style="font-size:8.0pt;font-family:"Calibri","sans-serif"">SQLstate: 2F003</span></i><p class="MsoNormal"><i><span
lang="EN-US"style="font-size:8.0pt;font-family:"Calibri","sans-serif"">Detail: Non-superuser cannot connect if the
serverdoes not request a password.</span></i><p class="MsoNormal"><i><span lang="EN-US"
style="font-size:8.0pt;font-family:"Calibri","sans-serif"">Hint:Target server's authentication method must be
changed.</span></i><pclass="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><pclass="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">However,if I just run a normal select on a non-foreign
tableit works.</span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><pclass="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Also,if I change my account to be a superuser it works. 
Confusesme slightly, since when setting up Postgres_fdw you specify the connection details in the user mapping. 
</span><pclass="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><p
class="MsoNormal"><spanlang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Is this a problem with
pgAdmin,or Postgres_fdw?  Seems to indicate an issue with pgAdmin?</span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><pclass="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Thanks& Regards,</span><p class="MsoNormal"><span
lang="EN-US"style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Adam Pearson</span></div>

Re: pgAdmin and Postgres_fdw needing superuser access

From

Adam Pearson

Date:

03 December 2015, 18:35:59

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

Re: pgAdmin and Postgres_fdw needing superuser access

From

Per Wigren

Date:

03 December 2015, 19:18:27

Great, but please tell us what the problem was and how you solved it, so when other people with the same problem find your mail in the archives in the future they will know what to do.

Regards, Per

tors 3 dec. 2015 kl 16:36 skrev Adam Pearson <Adam.Pearson@4finance.com>:

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

Urgent Urgent help needed !!!

From

John Banfield

Date:

08 December 2015, 14:36:40

Can anyone tell me if a log is kept when a backup is done with PGAdmin 3

We backed up a database then deleted the Database, now can not find the backup !!!

Obviously this is a urgent appeal for guidance

Regards, John

Re: Urgent Urgent help needed !!!

From

Colin Beckingham

Date:

08 December 2015, 14:59:46

Suggestion:

Use pgadmin to connect to the same server, select a different db and click on backup. You may find that in the filename field pgadmin will default to the same filename that was used previously, perhaps the one that was used for your backup if no other backup has been done since. At least you will get an idea of the path to backups - then search that directory for odd names other than *.backup which might not appear automatically in the restore window.

On 12/08/2015 06:36 AM, John Banfield wrote:

Can anyone tell me if a log is kept when a backup is done with PGAdmin 3
We backed up a database then deleted the Database, now can not find the backup !!!
Obviously this is a urgent appeal for guidance
Regards, John