I "might" have found a bug on 8.2.1 win32

Question, I hope stupid, postgres user HAS a password right?

Yes, it has a password, I set the password from the installer, and I
even reset it after the install was complete.
I just tried this from the command line and it let me right in:

psql template1 -U postgres

Didn't ask for a password or anything, and the localhost entry in
pg_hba.conf is also set for md5.

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration

In response to Tony Caduto <tony_caduto@amsoftwaredesign.com>:

Question, I hope stupid, postgres user HAS a password right?

Yes, it has a password, I set the password from the installer, and I
even reset it after the install was complete.
I just tried this from the command line and it let me right in:

psql template1 -U postgres

Didn't ask for a password or anything, and the localhost entry in
pg_hba.conf is also set for md5.

Does Windows have the equivalent of Unix domain sockets?

On a Unix machine, psql uses unix domain sockets by default, which are
controlled by an entry like:
# "local" is for Unix domain socket connections only
local all all trust

The following entry:
# IPv4 local connections:
host all all 127.0.0.1/32 trust

Will only apply if you connect via loopback networking (which is not
psql's default). Try your connect command like this:
psql template1 -U postgres -h 127.0.0.1
and see if the results change.

--
Bill Moran
Collaborative Fusion Inc.

Bill Moran wrote:

Will only apply if you connect via loopback networking (which is not
psql's default). Try your connect command like this:
psql template1 -U postgres -h 127.0.0.1
and see if the results change.

Hi Bill,

Using the -h 127.0.0.1 does cause the password prompt to fire, however
the weird part is in my pg_hba.conf I also have the local entry set to md5

local all all md5

I did some more testing and it seems the password I used during the
win32 install is being cached somewhere.
If I don't change the password from the one I used during the install it
lets me right in, if I change the password to something else I get a
password
authentication error with a blank password, if I then change the
postgres password back to the one I used during install a blank password
again lets me right in. I have not done a reboot yet, I will do reboot
and see if that clears it up.

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration

Tony Caduto wrote:

I had installed the win32 version awhile ago, but I had the pg_hba.conf
set to trust. Then I started to test SSL on win32 so I changed it to this:
host all all 127.0.0.1/32 md5
host all all 192.168.15.131/32 md5 #my pcs adddress

And I ensured the service had been restarted after making the change to
md5 instead of trust for my PC address.

Ok, here is the problem, If I pass in a blank password '' the md5
authentication is not done and I simply go right in with full access.
If I pass in a space ' ' the I get the password authentication error.
Normally with a blank password I would expect to see the no password
supplied error, but that is not happening on win32 it just gives full
blown access.

Here is the connect string being passed to libpq.dll when I use the
blank password, this string is captured from the debugger:

hostaddr='10.201.170.131' port='5432' dbname='template1' user='postgres'
password='' connect_timeout='15' sslmode=disable

I tried the same thing on a Linux server and it does not behave this
way, only on win32.

I then uninstalled 8.2.1 on the win32 box and completely deleted the
data directory and reinstalled and the same behavior prevailed.

I know a new connect GRANT was enabled in 8.2, but I though that was in
addition to the first checks done in pg_hba.conf.

Maybe I am doing something wrong, but it sure doesn't seem that way.
Like I said it "might" be a bug.

Question, I hope stupid, postgres user HAS a password right?

Well, there is something weird going on here:

If I change the postgres users password to the one I used during install
I get this when using psql:

C:\Program Files\PostgreSQL\8.2\bin>psql template1 -U postgres
DEBUG: InitPostgres
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid
1348/1/0, nestlvl: 1, children: <>
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid
1348/1/0, nestlvl: 1, children: <>
Welcome to psql 8.2.1, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

Warning: Console code page (437) differs from Windows code page (1252)
8-bit characters may not work correctly. See psql reference
page "Notes for Windows users" for details.

template1=#

If I change the password to anything else I get this:

C:\Program Files\PostgreSQL\8.2\bin>psql template1 -U postgres
psql: FATAL: password authentication failed for user "postgres"

I did a search for a password file but it came back empty.

Anyone have any ideas on why this is happening?

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration

Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:

I just tried this from the command line and it let me right in:
psql template1 -U postgres
Didn't ask for a password or anything, and the localhost entry in
pg_hba.conf is also set for md5.

You sure you restarted the postmaster (or did pg_ctl reload) after
changing the pg_hba.conf file? Is it possible there's another
postmaster on the machine that's the one you're actually connecting to?

Also, since you evidently did not show us all of pg_hba.conf the first
time, let's see it all.

regards, tom lane

Never mind,
somehow a application setup a pgpass.conf file for me
without my knowledge.

Sorry it's not a bug. However I wonder if it might be a good idea if
psql would raise a warning after logon that a pgpass.conf file was used
for authentication or have it written to the log.
I am sure some 3rd party application I was testing created the file when
it took my server login information. I would have found out about it
right away if it's use would have been written to the log.

Thanks everyone for you help.

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration

Tony Caduto wrote:

Never mind,
somehow a application setup a pgpass.conf file for me
without my knowledge.

Have you been leaving the dark side lately?
(pgAdmin does this..)

Sorry it's not a bug. However I wonder if it might be a good idea if
psql would raise a warning after logon that a pgpass.conf file was used
for authentication or have it written to the log. I am sure some 3rd
party application I was testing created the file when it took my server
login information. I would have found out about it right away if it's
use would have been written to the log.

psql can't do this, really, because that part happens entirely in libpq.
We'd need extra API for this. IIRC, I suggested this before, but I can't
remember what the result with. Running out of batteries ATM, so no time
to look it up in the archives ;-)

//Magnus

Magnus Hagander wrote:

Have you been leaving the dark side lately?
(pgAdmin does this..)

I still have pgAdmin installed :-) it's a good reference tool.
I imagine you guys are using it for the pg_restore/pg_dump? There
really should be a pg_dump.dll and a pg_restore.dll so a pgpass
file would not be needed. The microolap guys have created the dlls and
it's what I use now for my dump/restore operations.

Is there a way to tell libpq in the connection string or something not
to use a pgpass.conf file? I looked in the libpq docs but didn't really
see anything.

I kind of don't like the way the pgpass file affected all the apps that
used libpq, it could be considered a security risk.

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration

Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:

Is there a way to tell libpq in the connection string or something not
to use a pgpass.conf file? I looked in the libpq docs but didn't really
see anything.

Making pgpass files harder or more annoying to use does not strike me as
a good idea. I realize you're a bit ticked off right now, but for the
vast majority of users they're a very nice feature.

regards, tom lane

------- Original Message -------
From: Tony Caduto <tony_caduto@amsoftwaredesign.com>
To: Bill Moran <wmoran@collaborativefusion.com>
Sent: 01/02/07, 17:13:25
Subject: Re: [GENERAL] I "might" have found a bug on 8.2.1 win32

Bill Moran wrote:

Will only apply if you connect via loopback networking (which is not
psql's default). Try your connect command like this:
psql template1 -U postgres -h 127.0.0.1
and see if the results change.

Hi Bill,

Using the -h 127.0.0.1 does cause the password prompt to fire, however
the weird part is in my pg_hba.conf I also have the local entry set to md5

local all all md5

I did some more testing and it seems the password I used during the
win32 install is being cached somewhere.
If I don't change the password from the one I used during the install it
lets me right in, if I change the password to something else I get a
password
authentication error with a blank password, if I then change the
postgres password back to the one I used during install a blank password
again lets me right in. I have not done a reboot yet, I will do reboot
and see if that clears it up.

Could this be proof of you using pgAdmin ( :-) ) and checking the Store Password option? That'll save it in pgpass.conf in your profile.

/D

------- Original Message -------
From: Tony Caduto <tony_caduto@amsoftwaredesign.com>
To: Magnus Hagander <magnus@hagander.net>, pgsql-general@postgresql.org
Sent: 01/02/07, 18:38:22
Subject: Re: [GENERAL] I "might" have found a bug on 8.2.1 win32

I imagine you guys are using it for the pg_restore/pg_dump?

No, we use %PGPASSWORD% for that. pgAdmin stores the password there if you use the Store Password connection option.

/D

Dave Page wrote:

Could this be proof of you using pgAdmin ( :-) ) and checking the Store Password option? That'll save it in pgpass.conf in your profile.

/D

I won't deny I have it installed :-) I don't remember using the stored
password option though.
Do you also use that for the pg_restore/pg_dump so you don't have to
pipe the password?

Why don't you just store the password in a file in the same directory as
the pgAdmin executable? That way it would not affect other
applications.

Later,

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration

------- Original Message -------
From: Tony Caduto <tony_caduto@amsoftwaredesign.com>
To: Dave Page <dpage@postgresql.org>
Sent: 01/02/07, 20:17:00
Subject: Re: [GENERAL] I "might" have found a bug on 8.2.1 win32

I won't deny I have it installed :-) I don't remember using the stored
password option though.
Do you also use that for the pg_restore/pg_dump so you don't have to
pipe the password?

Why don't you just store the password in a file in the same directory as
the pgAdmin executable? That way it would not affect other
applications.

A non-admin user wouldn't necessarily be able to edit it, and it would be shared amongst all users of the machine.

/D

------- Original Message -------
From: Tony Caduto <tony_caduto@amsoftwaredesign.com>
To: Dave Page <dpage@postgresql.org>
Sent: 01/02/07, 20:37:18
Subject: Re: [GENERAL] I "might" have found a bug on 8.2.1 win32

Dave Page wrote:

A non-admin user wouldn't necessarily be able to edit it, and it would be shared amongst all users of the machine.

/D

Well, there has to be a way so it won't affect other applications.
I don't want to start deleting it when LA starts up. (which I could do
with one line of code)

That's a good way to cheese users off.

Is there a way to tell the libpq connect function not to use a pgpass
file?

No, as Tom already said.

It really should be up to the application if it wants to use it
or not.

No, it should be up to the *user* to decide, which is why it's a non-default (iirc) option in pgAdmin.

/D

<korryd@enterprisedb.com> writes:

I think you could effectively disable the pgpass file (for a given
application) if the application always requires an explicit, non-blank
password from the user.

If he does that, his users who do not use password-based authentication
will be after him with the proverbial villagers' implements.

regards, tom lane

Is there a way to tell the libpq connect function not to use a pgpass
file?

No, as Tom already said.

I think you could effectively disable the pgpass file (for a given
application) if the application always requires an explicit, non-blank
password from the user.

libpq won't use .pgpass if the connection string passed to PQconnectdb()
(or whatever connection function you happen to be using) includes a
"password=foo" clause, right?

-- Korry

------- Original Message -------
From: Tony Caduto <tony_caduto@amsoftwaredesign.com>
To: Dave Page <dpage@postgresql.org>
Sent: 01/02/07, 21:18:49
Subject: Re: [GENERAL] I "might" have found a bug on 8.2.1 win32

I may be forced to start deleting the pgpass file unless we can work
something out.

I must Be missing the point - why should I work something out with your app for using a documented feature of libpq in pgAdmin?

I saw in the docs you can specify a alternate location for the pgpass
file, why don't you guys use some other location so you are not
impacting other
apps? If a user goes into %APPDATA%\postgresql\pgpass.conf with the
specific purpose of setting up a pgpass file so they don't have to enter
a password that's a differnt story.

Because PGPASSFILE only appeared in 8.1 and we don't know if our libpq supports it at runtime.

Not to mention that the whole pgpass thing is a huge security hole, it
would be different if the passwords where encrypted or hashed, but they
are just sitting there in plain text.

In an 0600 file on *nix, or in your profile on Windows, which if you were concerned with security would be secured as well.

If you have a way to prevent my app from automatically using settings
set by pgAdmin III, I am willing to listen.

To what? I'm not the one wanting to change anything! :-)

/D

I think you could effectively disable the pgpass file (for a given
application) if the application always requires an explicit, non-blank
password from the user.

If he does that, his users who do not use password-based authentication
will be after him with the proverbial villagers' implements.

True... perhaps I missed that part of the thread. Of course my
suggestion would only affect the one application (which I think is what
Tony was looking for).

-- Korry