Обсуждение: password is no required, authentication is overridden
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
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 >
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
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
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
>>
>
>
> 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
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
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
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 >
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
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
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
> -----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.
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
> -----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.