Thread: Best way to "mask" password in DBLINK
I'm starting to use DBLink / DBI-Link and one of the "bad" things is that the password is out in the clear. What can I do to prevent it from being such? How do I protect it from 'innocent' users?
Ow Mun Heng wrote: > I'm starting to use DBLink / DBI-Link and one of the "bad" things is that > the password is out in the clear. > > What can I do to prevent it from being such? How do I protect it from > 'innocent' users? If I'm not mistaken, it's possible to put your password in the .pgpass file in the postgres-users home folder, on the server where the postgres cluster is running. -- Tommy Gildseth
-----Original Message-----
From: Tommy Gildseth [mailto:tommy.gildseth@usit.uio.no]
Ow Mun Heng wrote:
>> I'm starting to use DBLink / DBI-Link and one of the "bad" things is that
>> the password is out in the clear.
>> What can I do to prevent it from being such? How do I protect it from
>> 'innocent' users?
>If I'm not mistaken, it's possible to put your password in the .pgpass
>file in the postgres-users home folder, on the server where the postgres
>cluster is running.
Isn't that how one connects using the CLI? Eg: via psql?
My connection string looks like this.
SELECT aaa
FROM dblink('dbname=hmxmms host=xxx.xxx.xxx.xxx user=yyy
password=zzz'::text, 'SELECT * from tablename'::text) b4(aaa xxx );
I've placed the above as a view hence the user/pass is being hardcoded(?) of
sorts
Ow Mun Heng wrote:
>
> -----Original Message-----
> From: Tommy Gildseth [mailto:tommy.gildseth@usit.uio.no]
>
> Ow Mun Heng wrote:
>>> I'm starting to use DBLink / DBI-Link and one of the "bad" things is that
>>> the password is out in the clear.
>>> What can I do to prevent it from being such? How do I protect it from
>>> 'innocent' users?
>
>> If I'm not mistaken, it's possible to put your password in the .pgpass
>> file in the postgres-users home folder, on the server where the postgres
>> cluster is running.
>
> Isn't that how one connects using the CLI? Eg: via psql?
>
> My connection string looks like this.
>
> SELECT aaa
> FROM dblink('dbname=hmxmms host=xxx.xxx.xxx.xxx user=yyy
> password=zzz'::text, 'SELECT * from tablename'::text) b4(aaa xxx );
>
> I've placed the above as a view hence the user/pass is being hardcoded(?) of
> sorts
Just leave out the "password=zzz" part of the connection string.
--
Tommy Gildseth
On Wed, Aug 12, 2009 at 09:30, Ow Mun Heng<ow.mun.heng@wdc.com> wrote: > > > -----Original Message----- > From: Tommy Gildseth [mailto:tommy.gildseth@usit.uio.no] > > Ow Mun Heng wrote: >>> I'm starting to use DBLink / DBI-Link and one of the "bad" things is that >>> the password is out in the clear. >>> What can I do to prevent it from being such? How do I protect it from >>> 'innocent' users? > >>If I'm not mistaken, it's possible to put your password in the .pgpass >>file in the postgres-users home folder, on the server where the postgres >>cluster is running. > > Isn't that how one connects using the CLI? Eg: via psql? You need to put it in the .pgpass file of the postgres user - the one that runs the server. .pgpass is dealt with by libpq, and DBLink and DBI-Link both use libpq to connect to the remote server. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
-----Original Message----- From: Magnus Hagander [mailto:magnus@hagander.net] On Wed, Aug 12, 2009 at 09:30, Ow Mun Heng<ow.mun.heng@wdc.com> wrote: >> >> From: Tommy Gildseth [mailto:tommy.gildseth@usit.uio.no] >> >> Ow Mun Heng wrote: >>>> I'm starting to use DBLink / DBI-Link and one of the "bad" things is >>>that >>>> the password is out in the clear. >>>> What can I do to prevent it from being such? How do I protect it from >>>> 'innocent' users? >> >>>If I'm not mistaken, it's possible to put your password in the .pgpass >>>file in the postgres-users home folder, on the server where the postgres >>>cluster is running. >> >> Isn't that how one connects using the CLI? Eg: via psql? >You need to put it in the .pgpass file of the postgres user - the one >that runs the server. .pgpass is dealt with by libpq, and DBLink and >DBI-Link both use libpq to connect to the remote server. The View is owned by the user "operator" not postgres Does it make a difference? My understanding of your words are that it _does_ make a difference and If I put it into the .pgpass of the postgres user then all is fine. Thanks for confirmation
On Wed, Aug 12, 2009 at 10:01, Ow Mun Heng<ow.mun.heng@wdc.com> wrote: > > > -----Original Message----- > From: Magnus Hagander [mailto:magnus@hagander.net] > On Wed, Aug 12, 2009 at 09:30, Ow Mun Heng<ow.mun.heng@wdc.com> wrote: >>> >>> From: Tommy Gildseth [mailto:tommy.gildseth@usit.uio.no] >>> >>> Ow Mun Heng wrote: >>>>> I'm starting to use DBLink / DBI-Link and one of the "bad" things is >>>>that >>>>> the password is out in the clear. >>>>> What can I do to prevent it from being such? How do I protect it from >>>>> 'innocent' users? >>> >>>>If I'm not mistaken, it's possible to put your password in the .pgpass >>>>file in the postgres-users home folder, on the server where the postgres >>>>cluster is running. >>> >>> Isn't that how one connects using the CLI? Eg: via psql? > >>You need to put it in the .pgpass file of the postgres user - the one >>that runs the server. .pgpass is dealt with by libpq, and DBLink and >>DBI-Link both use libpq to connect to the remote server. > > The View is owned by the user "operator" not postgres > Does it make a difference? No, we're talking about operating system user here, not postgres user. So the owner of the database object is irrelevant - only the user that the backend process is executing as. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
-----Original Message----- >From: Magnus Hagander [mailto:magnus@hagander.net] >No, we're talking about operating system user here, not postgres user. >So the owner of the database object is irrelevant - only the user that >the backend process is executing as. Got it.. Thanks for the tip.
Ow Mun Heng wrote: > > -----Original Message----- > From: Magnus Hagander [mailto:magnus@hagander.net] > On Wed, Aug 12, 2009 at 09:30, Ow Mun Heng<ow.mun.heng@wdc.com> wrote: >>> From: Tommy Gildseth [mailto:tommy.gildseth@usit.uio.no] >>> >>> Ow Mun Heng wrote: >>>>> I'm starting to use DBLink / DBI-Link and one of the "bad" things is >>>> that >>>>> the password is out in the clear. >>>>> What can I do to prevent it from being such? How do I protect it from >>>>> 'innocent' users? >>>> If I'm not mistaken, it's possible to put your password in the .pgpass >>>> file in the postgres-users home folder, on the server where the postgres >>>> cluster is running. >>> Isn't that how one connects using the CLI? Eg: via psql? > >> You need to put it in the .pgpass file of the postgres user - the one >> that runs the server. .pgpass is dealt with by libpq, and DBLink and >> DBI-Link both use libpq to connect to the remote server. > > The View is owned by the user "operator" not postgres > Does it make a difference? > > My understanding of your words are that it _does_ make a difference and If I > put it into the .pgpass of the postgres user then all is fine. No, it doesn't matter which role owns the database object. The system user trying to connect to the remote cluster via dblink, is the user which owns the postgres process, ie. normally the postgres system user. libpq will therefor look for the .pgpass file in the postgres system users home folder, irrespective of which role owns the database, or which role is used to connect to the database etc. -- Tommy Gildseth
Magnus Hagander <magnus@hagander.net> writes:
>>> If I'm not mistaken, it's possible to put your password in the .pgpass
>>> file in the postgres-users home folder, on the server where the postgres
>>> cluster is running.
> You need to put it in the .pgpass file of the postgres user - the one
> that runs the server. .pgpass is dealt with by libpq, and DBLink and
> DBI-Link both use libpq to connect to the remote server.
Didn't we recently add a security fix to prevent non-superusers from
relying on the server's .pgpass file?
I think 8.4 provides a reasonable solution to this via the SQL/MED
additions. In previous releases it's hard to find a nice place to
keep the password for a dblink connection.
regards, tom lane