Обсуждение: Accessing other databases with DBLink when leaving user/password empty

Hi,

I have the following problem when trying to access other PostgreSQL
databases with DBLink. I followed the instructions on

http://www.postgresonline.com/journal/index.php?/archives/44-Using-DbLink-to-access-other-PostgreSQL-Databases-and-Servers.html.



My query to get access to another database on the same server looks like
this:

    select dblink_connect('1512','host=127.0.0.1 port=5432
    dbname=Test user=postgres password=postgres');

    select * from dblink('1512','select "Vorname", "Name" from
    "PERSONEN"')
    AS (Vorname text, Name text);

The question is if it is possible to leave the 'user' and 'password'
empty, so that these are taken from the account I'm currently using, as
described below (taken from the above article).

    [...]
    Below is an example of querying a database on the same server
    and cluster using DbLink. Note if no username and password is
    specified, then DbLink connects with whatever account you are
    currently using.

It is not possible to connect to the other database when they are empty
and it seems that DBLink can't get them from the currently used account.
Can you please help me with that? Thank you.

Regards,
H. Muster

Does no one have any idea about that?

Regards.



Hermann Muster wrote:
> Hi,
>
> I have the following problem when trying to access other PostgreSQL
> databases with DBLink. I followed the instructions on
>
http://www.postgresonline.com/journal/index.php?/archives/44-Using-DbLink-to-access-other-PostgreSQL-Databases-and-Servers.html.

>
>
> My query to get access to another database on the same server looks like
> this:
>
>     select dblink_connect('1512','host=127.0.0.1 port=5432
>     dbname=Test user=postgres password=postgres');
>
>     select * from dblink('1512','select "Vorname", "Name" from
>     "PERSONEN"')
>     AS (Vorname text, Name text);
>
> The question is if it is possible to leave the 'user' and 'password'
> empty, so that these are taken from the account I'm currently using, as
> described below (taken from the above article).
>
>     [...]
>     Below is an example of querying a database on the same server
>     and cluster using DbLink. Note if no username and password is
>     specified, then DbLink connects with whatever account you are
>     currently using.
>
> It is not possible to connect to the other database when they are empty
> and it seems that DBLink can't get them from the currently used account.
> Can you please help me with that? Thank you.
>
> Regards,
> H. Muster

On Friday 06 June 2008 2:32 am, Hermann Muster wrote:
> Does no one have any idea about that?
>
> Regards.
>
> Hermann Muster wrote:
> > Hi,
> >
> > I have the following problem when trying to access other PostgreSQL
> > databases with DBLink. I followed the instructions on
> > http://www.postgresonline.com/journal/index.php?/archives/44-Using-DbLink
> >-to-access-other-PostgreSQL-Databases-and-Servers.html.
> >
> >
> > My query to get access to another database on the same server looks like
> > this:
> >
> >     select dblink_connect('1512','host=127.0.0.1 port=5432
> >     dbname=Test user=postgres password=postgres');

From the documentation:
http://www.postgresql.org/docs/current/static/dblink.html
Don't do the above.


> >
> >     select * from dblink('1512','select "Vorname", "Name" from
> >     "PERSONEN"')
> >     AS (Vorname text, Name text);

Here do:
select * from dblink('dbname=Test','select "Vorname", "Name" from
     "PERSONEN"')
     AS (Vorname text, Name text);

> >
> > The question is if it is possible to leave the 'user' and 'password'
> > empty, so that these are taken from the account I'm currently using, as
> > described below (taken from the above article).
> >
> >     [...]
> >     Below is an example of querying a database on the same server
> >     and cluster using DbLink. Note if no username and password is
> >     specified, then DbLink connects with whatever account you are
> >     currently using.
> >
> > It is not possible to connect to the other database when they are empty
> > and it seems that DBLink can't get them from the currently used account.
> > Can you please help me with that? Thank you.
> >
> > Regards,
> > H. Muster

--
Adrian Klaver
aklaver@comcast.net

Hi Adrian,

I tried what you suggested, but still get the following Error:
"Error connecting to the server: fe_sendauth: no password supplied"

What is it I'm doing wrong? Isn't it possible to leave the password
empty so that PostgreSQL can retrieve it from the current account?

Thank you.



Adrian Klaver wrote:
> On Friday 06 June 2008 2:32 am, Hermann Muster wrote:
>> Does no one have any idea about that?
>>
>> Regards.
>>
>> Hermann Muster wrote:
>>> Hi,
>>>
>>> I have the following problem when trying to access other PostgreSQL
>>> databases with DBLink. I followed the instructions on
>>> http://www.postgresonline.com/journal/index.php?/archives/44-Using-DbLink
>>> -to-access-other-PostgreSQL-Databases-and-Servers.html.
>>>
>>>
>>> My query to get access to another database on the same server looks like
>>> this:
>>>
>>>     select dblink_connect('1512','host=127.0.0.1 port=5432
>>>     dbname=Test user=postgres password=postgres');
>
> From the documentation:
> http://www.postgresql.org/docs/current/static/dblink.html
> Don't do the above.
>
>
>>>     select * from dblink('1512','select "Vorname", "Name" from
>>>     "PERSONEN"')
>>>     AS (Vorname text, Name text);
>
> Here do:
> select * from dblink('dbname=Test','select "Vorname", "Name" from
>      "PERSONEN"')
>      AS (Vorname text, Name text);
>
>>> The question is if it is possible to leave the 'user' and 'password'
>>> empty, so that these are taken from the account I'm currently using, as
>>> described below (taken from the above article).
>>>
>>>     [...]
>>>     Below is an example of querying a database on the same server
>>>     and cluster using DbLink. Note if no username and password is
>>>     specified, then DbLink connects with whatever account you are
>>>     currently using.
>>>
>>> It is not possible to connect to the other database when they are empty
>>> and it seems that DBLink can't get them from the currently used account.
>>> Can you please help me with that? Thank you.
>>>
>>> Regards,
>>> H. Muster
>

Hermann Muster wrote:
> Hi Adrian,
>
> I tried what you suggested, but still get the following Error:
> "Error connecting to the server: fe_sendauth: no password supplied"
>
> What is it I'm doing wrong? Isn't it possible to leave the password
> empty so that PostgreSQL can retrieve it from the current account?
>

Your login password isn't kept anywhere in the session, so it's not
possible for dblink to retrieve it. Furthermore, allowing passwordless
authentication via dblink is considered a security risk, as it's
potentially possible to escalate your access privileges to superuser.
See http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2007-3278 and
http://www.securityfocus.com/archive/1/archive/1/471541/100/0/threaded
for more info on this issue.


--
Tommy Gildseth
DBA, Gruppe for databasedrift
Universitetet i Oslo, USIT
m: +47 45 86 38 50
t: +47 22 85 29 39

Tommy Gildseth wrote:
> Hermann Muster wrote:
>> Hi Adrian,
>>
>> I tried what you suggested, but still get the following Error:
>> "Error connecting to the server: fe_sendauth: no password supplied"
>>
>> What is it I'm doing wrong? Isn't it possible to leave the password
>> empty so that PostgreSQL can retrieve it from the current account?
>>
>
> Your login password isn't kept anywhere in the session, so it's not
> possible for dblink to retrieve it. Furthermore, allowing passwordless
> authentication via dblink is considered a security risk, as it's
> potentially possible to escalate your access privileges to superuser.
> See http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2007-3278 and
> http://www.securityfocus.com/archive/1/archive/1/471541/100/0/threaded
> for more info on this issue.
>
>

Hi Tommy,

I read the pdf from the second link you posted. Very interesting. Didn't
know about that.

So, how is it possible to connect to a second database on my server? I
think it's also a security risk to hardcode the user name and password
into the SQL query and to use the postgres user for all PCs that make
use of this SQL query. If I understand it right, then everyone could
easily read the password from the database with pgAdmin, right? Doesn't
look that secure to me either.

The following text from my first post mentioning the use of the current
account is therefore faulty?

[...]
     Below is an example of querying a database on the same server
     and cluster using DbLink. Note if no username and password is
     specified, then DbLink connects with whatever account you are
     currently using.

I checked on connecting two databases in PostgreSQL, but the only thing
I found was dbLink. Isn't there any other possibility?

On Tuesday 10 June 2008 12:41 am, Hermann Muster wrote:

>
> Adrian Klaver wrote:
> > On Friday 06 June 2008 2:32 am, Hermann Muster wrote:
> >> Does no one have any idea about that?
> >>
> >> Regards.
> >>
> >> Hermann Muster wrote:
> >>> Hi,
> >>>
> >>> I have the following problem when trying to access other PostgreSQL
> >>> databases with DBLink. I followed the instructions on
> >>> http://www.postgresonline.com/journal/index.php?/archives/44-Using-DbLi
> >>>nk -to-access-other-PostgreSQL-Databases-and-Servers.html.
> >>>
> >>>
> >>> My query to get access to another database on the same server looks
> >>> like this:
> >>>
> >>>     select dblink_connect('1512','host=127.0.0.1 port=5432
> >>>     dbname=Test user=postgres password=postgres');
> >
> > From the documentation:
> > http://www.postgresql.org/docs/current/static/dblink.html
> > Don't do the above.
> >
> >>>     select * from dblink('1512','select "Vorname", "Name" from
> >>>     "PERSONEN"')
> >>>     AS (Vorname text, Name text);
> >
> > Here do:
> > select * from dblink('dbname=Test','select "Vorname", "Name" from
> >      "PERSONEN"')
> >      AS (Vorname text, Name text);
> Hi Adrian,
>
> I tried what you suggested, but still get the following Error:
> "Error connecting to the server: fe_sendauth: no password supplied"
>
> What is it I'm doing wrong? Isn't it possible to leave the password
> empty so that PostgreSQL can retrieve it from the current account?
>

Try setting up a .pgpass file. See documentation below:
http://www.postgresql.org/docs/8.3/interactive/libpq-pgpass.html

--
Adrian Klaver
aklaver@comcast.net