Thread: Problems using PostgreSQL command line tools
Hi to all in the mailing-list! I am new and I have problems with command
line tools of PostgreSQL.
I have 2 computers, one at home and one at office. Both computers are
equipped with Windows 2000 Professional SP4. On both computers I have
installed PostgreSQL 8.1.3-1 (the latest at this moment).
Both installations are "clean", I have NOT modified any PostgreSQL
configuration file!
At home (where I have no problems), I can do:
-----------from console-----------
C:\>psql -h localhost -U postgres -d postgres
Password for user postgres:
Welcome to psql 8.1.3, the PostgreSQL interactive terminal.
[....]
postgres=#
----------------------------------
At office, if I try to do the same thing, I get:
-----------from console-----------
C:\>psql -h localhost -U postgres -d postgres
psql: FATAL: password authentication failed for user "postgres"
----------------------------------
In practice, psql doesn't ask me the password and fails immediately!
But note this (the first strange thing), I have NO problems connecting
to PostgreSQL server using pgAdmin III tool and also from PHP4/PHP5
scripts (I develop on web with Apache and PHP4/5).
For example I have tried the following PHP script:
-----------code-----------
<h1>PostgreSQL connection test</h1>
<?php
$dbconn = pg_connect ("host=localhost dbname=postgres user=postgres
password=pgpw")
or die ('Connect error: ' . pg_last_error ());
pg_close ($dbconn);
?>
<h3>Connected!</h3>
--------------------------
And in the web browser I see "Connected!", so connection to PostgreSQL
is ok!
I am NOT able to connect to server using all command line tools like
psql, createuser, createdb, etc...
Can someone help me?? What could be wrong on my computer at office???
Thanks in advance and sorry if my english is not perfect!
Andrea
Hi Andrea,
You should try to connect to 127.0.0.1 (or your hostname) instead
of localhost.
why?
- localhost means that you will connect using a socket (psql -h localhost)
- ip/hostname will use tcp/ip (psql -h 127.0.0.1)
Both have separate auth lines in your pg_hba.conf, therefore, you are
prolly facing a problem with the localhost line, where pgadmin is
connecting using the tcp/ip connection for sure.
Check that, but I am almost sure.
Regards,
Guido Barosio
On 3/23/06, Andrea <andrea.b73@email.it> wrote:
> Hi to all in the mailing-list! I am new and I have problems with command
> line tools of PostgreSQL.
>
> I have 2 computers, one at home and one at office. Both computers are
> equipped with Windows 2000 Professional SP4. On both computers I have
> installed PostgreSQL 8.1.3-1 (the latest at this moment).
> Both installations are "clean", I have NOT modified any PostgreSQL
> configuration file!
>
> At home (where I have no problems), I can do:
> -----------from console-----------
> C:\>psql -h localhost -U postgres -d postgres
> Password for user postgres:
> Welcome to psql 8.1.3, the PostgreSQL interactive terminal.
>
> [....]
>
> postgres=#
> ----------------------------------
>
> At office, if I try to do the same thing, I get:
> -----------from console-----------
> C:\>psql -h localhost -U postgres -d postgres
> psql: FATAL: password authentication failed for user "postgres"
> ----------------------------------
>
> In practice, psql doesn't ask me the password and fails immediately!
> But note this (the first strange thing), I have NO problems connecting
> to PostgreSQL server using pgAdmin III tool and also from PHP4/PHP5
> scripts (I develop on web with Apache and PHP4/5).
>
> For example I have tried the following PHP script:
> -----------code-----------
> <h1>PostgreSQL connection test</h1>
> <?php
> $dbconn = pg_connect ("host=localhost dbname=postgres user=postgres
> password=pgpw")
> or die ('Connect error: ' . pg_last_error ());
>
> pg_close ($dbconn);
> ?>
> <h3>Connected!</h3>
> --------------------------
> And in the web browser I see "Connected!", so connection to PostgreSQL
> is ok!
>
> I am NOT able to connect to server using all command line tools like
> psql, createuser, createdb, etc...
>
> Can someone help me?? What could be wrong on my computer at office???
> Thanks in advance and sorry if my english is not perfect!
>
> Andrea
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Guido Barosio
-----------------------
Andrea <andrea.b73@email.it> writes:
> C:\>psql -h localhost -U postgres -d postgres
> psql: FATAL: password authentication failed for user "postgres"
> In practice, psql doesn't ask me the password and fails immediately!
I don't pay a lot of attention to Windows-specific problems, but I seem
to recall some recent patches involving the behavior of password
prompting on Windows. You might check the list archives for the past
month or so to see if your results match up with what was discussed.
regards, tom lane
Hi, thank you for the answer. However it's not very useful to me:
C:\>psql -h 127.0.0.1 -U postgres -d postgres
psql: FATAL: password authentication failed for user "postgres"
My main question is: given the command line
psql -h localhost -U postgres -d postgres
why at home is ok (and works without problems) and at office doesn't
work at all???
pg_hba.conf contains:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
#host all all ::1/128 md5
on both computers.
Regards.
Andrea
> Hi Andrea,
>
> You should try to connect to 127.0.0.1 (or your hostname) instead
> of localhost.
>
> why?
>
> - localhost means that you will connect using a socket (psql -h localhost)
> - ip/hostname will use tcp/ip (psql -h 127.0.0.1)
>
> Both have separate auth lines in your pg_hba.conf, therefore, you are
> prolly facing a problem with the localhost line, where pgadmin is
> connecting using the tcp/ip connection for sure.
>
> Check that, but I am almost sure.
>
> Regards,
> Guido Barosio
>
> On 3/23/06, Andrea <andrea.b73@email.it> wrote:
>
>>Hi to all in the mailing-list! I am new and I have problems with command
>>line tools of PostgreSQL.
>>
>>I have 2 computers, one at home and one at office. Both computers are
>>equipped with Windows 2000 Professional SP4. On both computers I have
>>installed PostgreSQL 8.1.3-1 (the latest at this moment).
>>Both installations are "clean", I have NOT modified any PostgreSQL
>>configuration file!
>>
>>At home (where I have no problems), I can do:
>>-----------from console-----------
>>C:\>psql -h localhost -U postgres -d postgres
>>Password for user postgres:
>>Welcome to psql 8.1.3, the PostgreSQL interactive terminal.
>>
>> [....]
>>
>>postgres=#
>>----------------------------------
>>
>>At office, if I try to do the same thing, I get:
>>-----------from console-----------
>>C:\>psql -h localhost -U postgres -d postgres
>>psql: FATAL: password authentication failed for user "postgres"
>>----------------------------------
>>
>>In practice, psql doesn't ask me the password and fails immediately!
>>But note this (the first strange thing), I have NO problems connecting
>>to PostgreSQL server using pgAdmin III tool and also from PHP4/PHP5
>>scripts (I develop on web with Apache and PHP4/5).
>>
>>For example I have tried the following PHP script:
>>-----------code-----------
>><h1>PostgreSQL connection test</h1>
>><?php
>>$dbconn = pg_connect ("host=localhost dbname=postgres user=postgres
>>password=pgpw")
>> or die ('Connect error: ' . pg_last_error ());
>>
>>pg_close ($dbconn);
>>?>
>><h3>Connected!</h3>
>>--------------------------
>>And in the web browser I see "Connected!", so connection to PostgreSQL
>>is ok!
>>
>>I am NOT able to connect to server using all command line tools like
>>psql, createuser, createdb, etc...
>>
>>Can someone help me?? What could be wrong on my computer at office???
>>Thanks in advance and sorry if my english is not perfect!
>>
>>Andrea
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 4: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>>
>
>
>
> --
> Guido Barosio
> -----------------------
Here at office: C:\>psql -U postgres -d postgres psql: FATAL: password authentication failed for user "postgres" And at home this command works ok. I have tried it already. Andrea > try: > > psql -U postgres -d postgres > > avoid the -h, to check
Ok!!! Setting 'trust' instead of 'md5' works well!! Now psql doesn't ask me the password while pgAdmin III works too and ask me the password (why this difference???). <confused on> I continue to wonder why I got 2 different behaviours on my 2 PCs. At home I have 'md5' and it works! ;) <confused off> Regards. Andrea > replace the md5 with trust, and try again. > > > # IPv4 local connections: > host all all 127.0.0.1/32 trust
Prolly just something down in the windows universe md5 related? :-) Not a winguru :) -Dunno about windows, but what if you set the trust to 'password' and reload. If it works, there is something wrong with the method, or that's what I'd rather tho. -any $ENV var affecting? g.- On 3/23/06, Andrea <andrea.b73@email.it> wrote: > Ok!!! Setting 'trust' instead of 'md5' works well!! > Now psql doesn't ask me the password while pgAdmin III works too and ask > me the password (why this difference???). > > <confused on> > I continue to wonder why I got 2 different behaviours on my 2 PCs. At > home I have 'md5' and it works! ;) > <confused off> > > Regards. > Andrea > > > replace the md5 with trust, and try again. > > > > > > # IPv4 local connections: > > host all all 127.0.0.1/32 trust > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- Guido Barosio -----------------------
Tom Lane wrote: > Andrea <andrea.b73@email.it> writes: > > C:\>psql -h localhost -U postgres -d postgres > > psql: FATAL: password authentication failed for user "postgres" > > > In practice, psql doesn't ask me the password and fails immediately! > > I don't pay a lot of attention to Windows-specific problems, but I seem > to recall some recent patches involving the behavior of password > prompting on Windows. You might check the list archives for the past > month or so to see if your results match up with what was discussed. The issue is if you have a \dev on your current drive, opening /dev/tty works when it shouldn't. This will be fixed in 8.1.4 when it is released. -- Bruce Momjian http://candle.pha.pa.us + If your life is a hard drive, Christ can be your backup. +