Thread: password is no required, authentication is overridden

password is no required, authentication is overridden

From
Thomas Bley
Date:
Hi,

I already tried to mail security@postgresql.org but my message was stalled.

Version: 8.1.4 (binary for win32)
OS: Windows XP SP2

I type: pg_dump -h localhost -U postgres database_name and there is no
question for the password.

I haven't made changes to pg_hba.conf. I'm logged in as user "admin",
not "postgres", the password is not empty.

my pg_hba.conf:

# ...
# TYPE DATABASE USER CIDR-ADDRESS METHOD

# IPv4 local connections:
host all all 127.0.0.1/32 md5

without the -h localhost, I get the same (complete) dump

SHOW hba_file;
gives me:
d:/program files/postgreSQL/8.1/data/pg_hba.conf
(I only have one file named pg_hba.conf on my computer.

I tried again with "pg_dump -U postgres -W database_name" when I enter
the right password it passes, with the wrong one it denies me, without
the "-W" it lets me pass.

thanks and bye,
Thomas


Re: password is no required, authentication is overridden

From
Andrew Dunstan
Date:
Are you sure you don't have a pgpass file, or the environment variable 
PGPASSWORD set?

cheers

andrew

Thomas Bley wrote:

> Hi,
>
> I already tried to mail security@postgresql.org but my message was 
> stalled.
>
> Version: 8.1.4 (binary for win32)
> OS: Windows XP SP2
>
> I type: pg_dump -h localhost -U postgres database_name and there is no
> question for the password.
>
> I haven't made changes to pg_hba.conf. I'm logged in as user "admin",
> not "postgres", the password is not empty.
>
> my pg_hba.conf:
>
> # ...
> # TYPE DATABASE USER CIDR-ADDRESS METHOD
>
> # IPv4 local connections:
> host all all 127.0.0.1/32 md5
>
> without the -h localhost, I get the same (complete) dump 
> http://www.postgresql.org/docs/current/static/sql-createtable.html
>
> SHOW hba_file;
> gives me:
> d:/program files/postgreSQL/8.1/data/pg_hba.conf
> (I only have one file named pg_hba.conf on my computer.
>
> I tried again with "pg_dump -U postgres -W database_name" when I enter
> the right password it passes, with the wrong one it denies me, without
> the "-W" it lets me pass.
>
> thanks and bye,
> Thomas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>



Re: password is no required, authentication is overridden

From
"Andrew Hammond"
Date:
Thomas Bley wrote:

> I type: pg_dump -h localhost -U postgres database_name and there is no
> question for the password.

Have you created a .pgpass (or whatever the equivilant in the Windows
world is)? That could be supplying the password.

> I haven't made changes to pg_hba.conf. I'm logged in as user "admin",
> not "postgres", the password is not empty.
>
> my pg_hba.conf:
>
> # ...
> # TYPE DATABASE USER CIDR-ADDRESS METHOD
>
> # IPv4 local connections:
> host all all 127.0.0.1/32 md5
>
> without the -h localhost, I get the same (complete) dump

I'm not sure what the default behaviour for local socket connections
is. Perhaps you should add a line to your pg_hba.conf to define a rule
for local socket connections.  For example

local all all md5



Re: password is no required, authentication is overridden

From
Andrew Dunstan
Date:
Andrew Hammond wrote:

>I'm not sure what the default behaviour for local socket connections
>is. Perhaps you should add a line to your pg_hba.conf to define a rule
>for local socket connections.  For example
>
>local all all md5
>
>
>  
>


He's on Windows - no local lines there.

cheers

andrew


Re: password is no required, authentication is overridden

From
Thomas Bley
Date:
Hello Andrew,

Thanks a lot!
There is a pgpass file and it contians the password:
D:\Documents and Settings\admin\Application Data\postgresql\pgpass.conf

My installation is on: D:\Program Files\PostgreSQL\8.1\

Maybe it got automatically created by pgadmin3 ?

Looking into the documentation delivered with pg ("PostgreSQL and
pgAdmin Help") there is a page about the pgpass file,
but there is not hint to environment variable pgpassword or the
pgpass-file on the page of pg_dump, maybe you can add it ?
In the documentation page of psql there is a hint to the pgpass file.

bye
Thomas


Andrew Dunstan wrote:
>
> Are you sure you don't have a pgpass file, or the environment variable 
> PGPASSWORD set?
>
> cheers
>
> andrew
>
> Thomas Bley wrote:
>
>> Hi,
>>
>> I already tried to mail security@postgresql.org but my message was 
>> stalled.
>>
>> Version: 8.1.4 (binary for win32)
>> OS: Windows XP SP2
>>
>> I type: pg_dump -h localhost -U postgres database_name and there is no
>> question for the password.
>>
>> I haven't made changes to pg_hba.conf. I'm logged in as user "admin",
>> not "postgres", the password is not empty.
>>
>> my pg_hba.conf:
>>
>> # ...
>> # TYPE DATABASE USER CIDR-ADDRESS METHOD
>>
>> # IPv4 local connections:
>> host all all 127.0.0.1/32 md5
>>
>> without the -h localhost, I get the same (complete) dump 
>> http://www.postgresql.org/docs/current/static/sql-createtable.html
>>
>> SHOW hba_file;
>> gives me:
>> d:/program files/postgreSQL/8.1/data/pg_hba.conf
>> (I only have one file named pg_hba.conf on my computer.
>>
>> I tried again with "pg_dump -U postgres -W database_name" when I enter
>> the right password it passes, with the wrong one it denies me, without
>> the "-W" it lets me pass.
>>
>> thanks and bye,
>> Thomas
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>>
>
>




Re: password is no required, authentication is overridden

From
"Andrew Hammond"
Date:
> There is a pgpass file and it contians the password:
> D:\Documents and Settings\admin\Application Data\postgresql\pgpass.conf
>
> My installation is on: D:\Program Files\PostgreSQL\8.1\
>
> Maybe it got automatically created by pgadmin3 ?

I'll bet you're right.

> Looking into the documentation delivered with pg ("PostgreSQL and
> pgAdmin Help") there is a page about the pgpass file,
> but there is not hint to environment variable pgpassword or the
> pgpass-file on the page of pg_dump, maybe you can add it ?
> In the documentation page of psql there is a hint to the pgpass file.

That's a good idea. What do you think would be a good way to say it?

Drew



Re: password is no required, authentication is overridden

From
Thomas Bley
Date:
Andrew Hammond wrote:
>> Looking into the documentation delivered with pg ("PostgreSQL and
>> pgAdmin Help") there is a page about the pgpass file,
>> but there is not hint to environment variable pgpassword or the
>> pgpass-file on the page of pg_dump, maybe you can add it ?
>> In the documentation page of psql there is a hint to the pgpass file.
>>     
>
> That's a good idea. What do you think would be a good way to say it?
>
>   

see "+" for my text:


=> pg_dump

...
-U username
Connect as the given user

-W
Force a password prompt. This should happen automatically if the server 
requires password authentication.
+ You can also place your password in a .pgpass file in order to avoid 
any inputs (see Section 28.12, “The Password File”).

Environment

PGDATABASE

PGHOST

PGPORT

PGUSER
Default connection parameters.

Diagnostics
pg_dump internally executes SELECT statements. If you have problems 
running pg_dump, make sure you are able to select information from the 
database using, for example, psql.

Notes

+ In order to create nightly backups it might be necessary to run the 
authentication automatically.
+ Adding the password as a parameter to pg_dump is not supported because 
this appears in the process list, available to all local users.
+ Instead you can place your credentials in a .pgpass file (see Section 
28.12, “The Password File”).

If your database cluster has any local additions to the template1 
database, be careful to restore the ...



=> 28.12. The Password File

...
chmod 0600 ~/.pgpass. If the permissions are less strict than this, the 
file will be ignored. (The file permissions are not currently checked on 
Microsoft Windows, however.)

+ The .pgpass file will be automatically created if you're using pgAdmin 
III with "store password" being enabled in the connection settings.


bye
Thomas


Re: password is no required, authentication is overridden

From
Andrew Dunstan
Date:
Thomas Bley wrote:

>
>
> + The .pgpass file will be automatically created if you're using 
> pgAdmin III with "store password" being enabled in the connection 
> settings.
>

It strikes me that this is actually a bad thing for pgadmin3 to be 
doing. It should use its own file, not the deafult location, at least if 
the libpq version is >= 8.1. We provided the PGPASSFILE environment 
setting just so programs like this could use alternative locations for 
the pgpass file. Otherwise, it seems to me we are violating the POLS, as 
in the case of this user who not unnaturally thought he had found a 
major security hole.

cheers

andrew


Re: password is no required, authentication is overridden

From
Thomas Bley
Date:
or maybe split the file up into sections like this example:

[pgadmin3]
localhost:5432:*:postgres:post

[pg_dump]

[psql]


bye
Thomas


Andrew Dunstan wrote:
> Thomas Bley wrote:
>
>>
>>
>> + The .pgpass file will be automatically created if you're using 
>> pgAdmin III with "store password" being enabled in the connection 
>> settings.
>>
>
> It strikes me that this is actually a bad thing for pgadmin3 to be 
> doing. It should use its own file, not the deafult location, at least 
> if the libpq version is >= 8.1. We provided the PGPASSFILE environment 
> setting just so programs like this could use alternative locations for 
> the pgpass file. Otherwise, it seems to me we are violating the POLS, 
> as in the case of this user who not unnaturally thought he had found a 
> major security hole.
>
> cheers
>
> andrew
>



Re: password is no required, authentication is overridden

From
"Hiroshi Saito"
Date:
From: "Andrew Dunstan"

> Thomas Bley wrote:
> 
>>
>>
>> + The .pgpass file will be automatically created if you're using 
>> pgAdmin III with "store password" being enabled in the connection 
>> settings.
>>
> 
> It strikes me that this is actually a bad thing for pgadmin3 to be 
> doing. It should use its own file, not the deafult location, at least if 
> the libpq version is >= 8.1. We provided the PGPASSFILE environment 
> setting just so programs like this could use alternative locations for 
> the pgpass file. Otherwise, it seems to me we are violating the POLS, as 
> in the case of this user who not unnaturally thought he had found a 
> major security hole.

Ummm, The function which pgAdmin offers is the optimal in present. I do not 
think that PGPASSFILE avoids the danger clearly. Probably, It is easy for the 
user who is malicious in the change to find it. I consider it to be a problem that 
the password is finally PlainText. Then, I made the proposal before. However,
It was indicated that deliberation is required again..... I want to consider a good 
method again. Is there any proposal with good someone?

Regards,
Hiroshi Saito






Re: password is no required, authentication is overridden

From
Andreas Pflug
Date:
Andrew Dunstan wrote:
>>
>
> It strikes me that this is actually a bad thing for pgadmin3 to be 
> doing. It should use its own file, not the deafult location, at least 
> if the libpq version is >= 8.1. We provided the PGPASSFILE environment 
> setting just so programs like this could use alternative locations for 
> the pgpass file. Otherwise, it seems to me we are violating the POLS, 
> as in the case of this user who not unnaturally thought he had found a 
> major security hole.
.pgpass is THE mechanism for storing libpq passwords, so what is wrong?
If the account is assumed insecure, the user shouldn't check "store 
password" in pgadmin3.
That's a libpq issue, not a pgadmin3 issue.

Regards,
Andreas



Re: password is no required, authentication is overridden

From
Andrew Dunstan
Date:

Hiroshi Saito wrote:

> From: "Andrew Dunstan"
>
>> Thomas Bley wrote:
>>
>>>
>>>
>>> + The .pgpass file will be automatically created if you're using 
>>> pgAdmin III with "store password" being enabled in the connection 
>>> settings.
>>>
>>
>> It strikes me that this is actually a bad thing for pgadmin3 to be 
>> doing. It should use its own file, not the deafult location, at least 
>> if the libpq version is >= 8.1. We provided the PGPASSFILE 
>> environment setting just so programs like this could use alternative 
>> locations for the pgpass file. Otherwise, it seems to me we are 
>> violating the POLS, as in the case of this user who not unnaturally 
>> thought he had found a major security hole.
>
>
> Ummm, The function which pgAdmin offers is the optimal in present. I 
> do not think that PGPASSFILE avoids the danger clearly. Probably, It 
> is easy for the user who is malicious in the change to find it. 



I don't understand what you are saying here. The problem is that it is 
not clear (at least to the original user, and maybe to others) that when 
pgadmin3 saves a password it saves it where it will be found by all 
libpq clients, not just by pgadmin3. How is that optimal? If pgadmin3 
were to save it in a non-standard location and then set PGPASSFILE to 
point to that location that would solve the problem. Or maybe it should 
offer a choice. Either way, how would a malicious user affect that? 
PGPASSFILE only contains a location, not the contents of the file, so 
exposing it is not any great security issue, as long as the location is 
itself protected.

> I consider it to be a problem that the password is finally PlainText. 
> Then, I made the proposal before. However,
> It was indicated that deliberation is required again..... I want to 
> consider a good method again. Is there any proposal with good someone?
>

Use of plaintext in pgpass files is a different problem.

If you really want high security you need to get out of the game of 
shared passwords altogether, and use client certificates, IMNSHO.

cheers

andrew



Re: password is no required, authentication is overridden

From
"Dave Page"
Date:

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of
> Andrew Dunstan
> Sent: 19 July 2006 13:55
> To: Hiroshi Saito
> Cc: Thomas Bley; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] password is no required,
> authentication is overridden
>
>
> I don't understand what you are saying here. The problem is
> that it is
> not clear (at least to the original user, and maybe to
> others) that when
> pgadmin3 saves a password it saves it where it will be found by all
> libpq clients, not just by pgadmin3.

From: http://www.pgadmin.org/docs/1.4/connect.html

If you select "store password", pgAdmin stores passwords you enter in
the ~/.pgpass file under *nix or %APPDATA%\postgresql\pgpass.conf under
Win32 for later reuse. For details, see pgpass documentation. It will be
used for all libpq based tools. If you want the password removed, you
can select the server's properties and uncheck the selection any time.


> How is that optimal? If pgadmin3
> were to save it in a non-standard location and then set PGPASSFILE to
> point to that location that would solve the problem. Or maybe
> it should
> offer a choice. Either way, how would a malicious user affect that?
> PGPASSFILE only contains a location, not the contents of the file, so
> exposing it is not any great security issue, as long as the
> location is
> itself protected.

We have no sensible way of determining whether or not the libpq we are
running with supports PGPASSFILE.

Regards, Dave.


Re: password is no required, authentication is overridden

From
Andrew Dunstan
Date:
Dave Page wrote:

> 
>
>  
>
>>-----Original Message-----
>>From: pgsql-hackers-owner@postgresql.org 
>>[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of 
>>Andrew Dunstan
>>Sent: 19 July 2006 13:55
>>To: Hiroshi Saito
>>Cc: Thomas Bley; pgsql-hackers@postgresql.org
>>Subject: Re: [HACKERS] password is no required, 
>>authentication is overridden
>>
>>
>>I don't understand what you are saying here. The problem is 
>>that it is 
>>not clear (at least to the original user, and maybe to 
>>others) that when 
>>pgadmin3 saves a password it saves it where it will be found by all 
>>libpq clients, not just by pgadmin3. 
>>    
>>
>
>From: http://www.pgadmin.org/docs/1.4/connect.html
>
>If you select "store password", pgAdmin stores passwords you enter in
>the ~/.pgpass file under *nix or %APPDATA%\postgresql\pgpass.conf under
>Win32 for later reuse. For details, see pgpass documentation. It will be
>used for all libpq based tools. If you want the password removed, you
>can select the server's properties and uncheck the selection any time.
>
>  
>

OK, although I am not sure I think that is sensible - it is at least 
documented. Does the dialog box also carry similar info?

>  
>
>>How is that optimal? If pgadmin3 
>>were to save it in a non-standard location and then set PGPASSFILE to 
>>point to that location that would solve the problem. Or maybe 
>>it should 
>>offer a choice. Either way, how would a malicious user affect that? 
>>PGPASSFILE only contains a location, not the contents of the file, so 
>>exposing it is not any great security issue, as long as the 
>>location is 
>>itself protected.
>>    
>>
>
>We have no sensible way of determining whether or not the libpq we are
>running with supports PGPASSFILE.
>
>
>  
>

Well, this answer is better. The lack of an API to tell you the library 
version is possibly worrying, though.

cheers

andrew



Re: password is no required, authentication is overridden

From
"Dave Page"
Date:

> -----Original Message-----
> From: Andrew Dunstan [mailto:andrew@dunslane.net]
> Sent: 19 July 2006 15:20
> To: Dave Page
> Cc: Hiroshi Saito; Thomas Bley; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] password is no required,
> authentication is overridden
>
> >From: http://www.pgadmin.org/docs/1.4/connect.html
> >
> >If you select "store password", pgAdmin stores passwords you enter in
> >the ~/.pgpass file under *nix or
> %APPDATA%\postgresql\pgpass.conf under
> >Win32 for later reuse. For details, see pgpass
> documentation. It will be
> >used for all libpq based tools. If you want the password removed, you
> >can select the server's properties and uncheck the selection
> any time.
> >
> >
> >
>
> OK, although I am not sure I think that is sensible - it is at least
> documented. Does the dialog box also carry similar info?

It has a Help button that takes you straight to that doc page.

> >We have no sensible way of determining whether or not the
> libpq we are
> >running with supports PGPASSFILE.
> >
> >
> >
> >
>
> Well, this answer is better. The lack of an API to tell you
> the library
> version is possibly worrying, though.

Indeed. The issue has been raised a few times in the past but for
reasons I forget, such an API has never been added :-(

I'd be happy with:

int PQmajorVer()
int PQminorVer()

Or something similar. It would also be handy to have something like
PQhasKerberos()...

Regards, Dave.