Обсуждение: Bug Report - PGAdmin3 windows pgpass.conf passwords stored in plain text

I'm working on my first PostgreSQL project.  We are attempting a proof
of concept.  I'm using PGAdmin 3 v1.6.2 on a Windows XP Pro workstation.
I like the option to 'Save Password' because I'm generally lazy and
don't want to type in the password every time I log on.

While doing some poking around I discovered that the passwords in the
pgpass.conf file are stored in plain text.  I consider this a bug.

Being new to PGS and not fully understanding all the implications I set
up my database to use MD5 for password encryption.  So, I attempted to
replace the plain text password in pgpass.conf with the MD5 encrypted
one hoping that PGA3 would see the MD5 as the first few characters and
realize that it didn't have to encrypt the password before sending it to
the server.  No such luck.

I searched the Known Issues and didn't see this listed as a problem.  I
searched the TODO list and didn't see any mention of this problem.  I
realize that the work around is to simply not make use of the 'store
password' option but then I can't help but wonder why the 'store
password' option exists in the product.

Would the 'powers that be' list this as a bug and add it to the TODO
list?

Joe Moyle
Sr. DBA
Office (713) 895-2055
Fax (713) 895-2001
JMoyle@Paymetric.com

Dear Developers,

There a bug since 1.6.3.
This bug is not exists in 1.6.2 and early versions.

The last tested version in 1.7 snapshot 070510 under WinXp.

When you open the table browser. On a table in right click menu select 
the 'View data / view *'.
Resize any column. Push refresh button. The resized column is set back 
to the original size. This is very bad when you would like only refresh 
data, not the all view.

Best Regards,   Ferenc Lutischan

Joe Moyle wrote:
> I'm working on my first PostgreSQL project.  We are attempting a proof
> of concept.  I'm using PGAdmin 3 v1.6.2 on a Windows XP Pro workstation.
> I like the option to 'Save Password' because I'm generally lazy and
> don't want to type in the password every time I log on.  
> 
> While doing some poking around I discovered that the passwords in the
> pgpass.conf file are stored in plain text.  I consider this a bug.  
> 
> Being new to PGS and not fully understanding all the implications I set
> up my database to use MD5 for password encryption.  So, I attempted to
> replace the plain text password in pgpass.conf with the MD5 encrypted
> one hoping that PGA3 would see the MD5 as the first few characters and
> realize that it didn't have to encrypt the password before sending it to
> the server.  No such luck.
> 
> I searched the Known Issues and didn't see this listed as a problem.  I
> searched the TODO list and didn't see any mention of this problem.  I
> realize that the work around is to simply not make use of the 'store
> password' option but then I can't help but wonder why the 'store
> password' option exists in the product.
> 
> Would the 'powers that be' list this as a bug and add it to the TODO
> list?

This is how PostgreSQL's libpq requires the file to be formatted.

Regards, Dave.

Joe,

You took a good first step in setting up authenication to use MD5 by 
default.  Now, (as a new community member) I have a few questions to 
determine what encrypting the password would accomplish.

What would such an encryption protect against?  You can explicitly set the 
permissions on your Application Data directory to refuse "All Users" read 
permission; on my WinXP box, the Postgres directory under App Data only 
explicitly allows access from myself, the Administrator, or SYSTEM.  I'm 
not a Windows expert so I don't know if "read" permissions are implied by 
virtue of not having "read" restrictions...

So, if you've secured the file so that only you can read it, the next step 
is to secure the file so that if someone logs in as you and gets the file, 
they can only see encrypted bytes in the password file.  But let me ask - 
if someone can login as you, can't they login to your PG datbases anyway, 
by virtue of your having saved your passwords?

Even if it were trivial to encrypt the password, we'd have to figure out 
how to encrypt it securely, because trivial encryption methods are broken 
easily.  Since pgAdmin is designed to be more of a "maintenance" tool than 
an "end user" tool, it is considered to be more "protected" from 
intrusion; compare this with the passwords being sent over the wire, which 
can be encrypted with MD5 because they are not as protected as a 
limited-use maintenance tool.

Hope this helps,
-Josh

On 23/05/2007 15:52, Joe Moyle wrote:

> While doing some poking around I discovered that the passwords in the
> pgpass.conf file are stored in plain text.  I consider this a bug.  

It's not a bug - use file permissions to protect the file from prying eyes.

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

> Joe Moyle wrote:
...
> > While doing some poking around I discovered that the passwords in
the
> > pgpass.conf file are stored in plain text.  I consider this a bug.
...
> > Would the 'powers that be' list this as a bug and add it to the TODO
> > list?
>
> This is how PostgreSQL's libpq requires the file to be formatted.
>
> Regards, Dave.

First let me say that I'm not a programmer (wanna-be at best) so I'm
asking forgiveness in advance if I use the wrong nomenclature or fail to
communicate what I'm thinking in terms that interested parties can
easily understand.

I'm looking at the documentation for the libpq method called
PQconnectdb.  I see that it requires user and password in a scenario
like I've got my server set up.  I still think that PGA3 storing the
password in plain text is a bug.  Wouldn't it be better if it stored it
encrypted using an encryption algorithm that can be unencrypted so that
it could be unencrypted and then sent to libpq in plain text?

When trying to answer this question for myself I thought that it might
be pointless because some key would be required for unencrypting.  I
then thought that if I had to type in the key every time it would blow
my lazy desire to type less out of the water.  Upon further reflection I
thought that it would still be better since I would only have to
remember one key instead of the various username/password combinations.

I can't help but feel I'm missing something obvious here but am just too
ignorant to know it.  I'll continue reading the libpq documentation and
thinking about it.

Joe Moyle wrote:
>> Joe Moyle wrote:
> ...
>>> While doing some poking around I discovered that the passwords in
> the
>>> pgpass.conf file are stored in plain text.  I consider this a bug.
> ...
>>> Would the 'powers that be' list this as a bug and add it to the TODO
>>> list?
>> This is how PostgreSQL's libpq requires the file to be formatted.
>>
>> Regards, Dave.
> 
> First let me say that I'm not a programmer (wanna-be at best) so I'm
> asking forgiveness in advance if I use the wrong nomenclature or fail to
> communicate what I'm thinking in terms that interested parties can
> easily understand.
> 
> I'm looking at the documentation for the libpq method called
> PQconnectdb.  I see that it requires user and password in a scenario
> like I've got my server set up.  I still think that PGA3 storing the
> password in plain text is a bug.  Wouldn't it be better if it stored it
> encrypted using an encryption algorithm that can be unencrypted so that
> it could be unencrypted and then sent to libpq in plain text?
> 
> When trying to answer this question for myself I thought that it might
> be pointless because some key would be required for unencrypting.  I
> then thought that if I had to type in the key every time it would blow
> my lazy desire to type less out of the water.  Upon further reflection I
> thought that it would still be better since I would only have to
> remember one key instead of the various username/password combinations.
> 
> I can't help but feel I'm missing something obvious here but am just too
> ignorant to know it.  I'll continue reading the libpq documentation and
> thinking about it.
> 

pgAdmin only ever writes the file, libpq does the reading so we have to
write it in the format it dictates. See
http://www.postgresql.org/docs/8.2/interactive/libpq-pgpass.html for
more info.

pgAdmin 1.8 does also warn you about the possible consequences of having
an unsecured pgpass file.

Regards, Dave.

...much removed
>
> pgAdmin 1.8 does also warn you about the possible consequences of
having
> an unsecured pgpass file.
>
> Regards, Dave.

Thank you everyone who answered and gave me guidance.

For anyone interested I removed permissions on the file from everyone
but myself.  I then had one of the domain admins log in as domain admin
and attempt to access the file.  He got permission denied.  So, I'm
placated.

Joe Moyle