Обсуждение: Server starts, but I can't connect
Hello All, I am running PostgreSQL 8.4 on Kubuntu 11.04. The server starts without any issues, but I can not connect. I keep getting an invalid user/password error. I am connecting to the localhost, default port, user = postgres, database = postgres I have tried setting the authentication methods to trust in the pg_hba.conf file, but it does not make any difference. What am I doing wrong? Thanks, Craigbert
Craigbert <postgresql@mysoftforge.com> writes: > Hello All, > I am running PostgreSQL 8.4 on Kubuntu 11.04. > The server starts without any issues, but I can not connect. I keep > getting an invalid user/password error. > I am connecting to the localhost, default port, user = postgres, > database = postgres > I have tried setting the authentication methods to trust in the > pg_hba.conf file, but it does not make any difference. > What am I doing wrong? You could not get that type of error when running "trust" authentication. So, you aren't. Likely theories are that you forgot to reload or restart the server after changing pg_hba.conf, or that you did reload but it didn't "take" because of some error in the modified hba file (if so, there'll be some complaint about it in the server log file), or that the file you're changing isn't the one being used by the server you're actually connecting to. regards, tom lane
Tom, Thanks for replying. Here is the log: 2011-07-05 22:58:22 CDT LOG: database system is ready to accept connections 2011-07-05 22:58:22 CDT LOG: autovacuum launcher started 2011-07-05 22:59:33 CDT LOG: could not receive data from client: Connection reset by peer 2011-07-05 22:59:41 CDT FATAL: password authentication failed for user "postgres" 2011-07-05 22:59:41 CDT FATAL: password authentication failed for user "postgres" 2011-07-05 23:02:00 CDT LOG: could not receive data from client: Connection reset by peer 2011-07-05 23:02:01 CDT LOG: could not receive data from client: Connection reset by peer 2011-07-05 23:04:32 CDT LOG: could not receive data from client: Connection reset by peer Here is the main part of my pg_hba.conf file: # Database administrative login by UNIX sockets local all postgres trust #local all postgres ident --wcb original line # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # method above WAS ident --wcb # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5 I did a "find / -name pg_hba.conf" and this was the only file that was found. Based on what you are saying and the log file I have set up my pg_hba.conf file incorrectly. Any idea where I have gone awry? Thanks, Craigbert On 07/05/2011 10:11 PM, Tom Lane wrote: > Craigbert<postgresql@mysoftforge.com> writes: >> Hello All, >> I am running PostgreSQL 8.4 on Kubuntu 11.04. >> The server starts without any issues, but I can not connect. I keep >> getting an invalid user/password error. >> I am connecting to the localhost, default port, user = postgres, >> database = postgres >> I have tried setting the authentication methods to trust in the >> pg_hba.conf file, but it does not make any difference. >> What am I doing wrong? > You could not get that type of error when running "trust" > authentication. So, you aren't. Likely theories are that you forgot to > reload or restart the server after changing pg_hba.conf, or that you did > reload but it didn't "take" because of some error in the modified hba > file (if so, there'll be some complaint about it in the server log > file), or that the file you're changing isn't the one being used by the > server you're actually connecting to. > > regards, tom lane >
Hi On 6 July 2011 06:14, Craigbert <postgresql@mysoftforge.com> wrote: > Tom, > > Thanks for replying. > > Here is the log: [...] > > Here is the main part of my pg_hba.conf file: > # Database administrative login by UNIX sockets > local all postgres trust > #local all postgres ident --wcb original > line "local" is for UNIX domain sockets, not TCP/IP networking. > # TYPE DATABASE USER CIDR-ADDRESS METHOD > > # "local" is for Unix domain socket connections only > local all all trust > # method above WAS ident --wcb > # IPv4 local connections: > host all all 127.0.0.1/32 md5 Here you're specifying that when someone connects over TCP/IP to localhost, they must use a username and password using MD5 for the password hashing algorithm. > # IPv6 local connections: > host all all ::1/128 md5 > > I did a "find / -name pg_hba.conf" and this was the only file that was > found. > > Based on what you are saying and the log file I have set up my pg_hba.conf > file incorrectly. > > Any idea where I have gone awry? Unless you can't (e.g. you're using JDBC or something), try connecting to the UNIX domain socket instead of over TCP/IP. If this is not an option, try changing "md5" to "trust" for the "host ... 127.0.0.1/32" line. But I don't recommend this. It would be better to set up a username and password and use that. If you're just trying to connect using psql as user postgres, try this: craigbert@kubuntu$ sudo su - postgres postgres@kubuntu$ psql If you don't specify a host it should use the UNIX domain socket instead of TCP/IP. > On 07/05/2011 10:11 PM, Tom Lane wrote: >> >> Craigbert<postgresql@mysoftforge.com> writes: >>> >>> Hello All, >>> I am running PostgreSQL 8.4 on Kubuntu 11.04. >>> The server starts without any issues, but I can not connect. I keep >>> getting an invalid user/password error. >>> I am connecting to the localhost, default port, user = postgres, >>> database = postgres >>> I have tried setting the authentication methods to trust in the >>> pg_hba.conf file, but it does not make any difference. >>> What am I doing wrong? >> >> You could not get that type of error when running "trust" >> authentication. So, you aren't. Likely theories are that you forgot to >> reload or restart the server after changing pg_hba.conf, or that you did >> reload but it didn't "take" because of some error in the modified hba >> file (if so, there'll be some complaint about it in the server log >> file), or that the file you're changing isn't the one being used by the >> server you're actually connecting to. >> >> regards, tom lane -- Michael Wood <esiotrot@gmail.com>
Thanks Michael and everyone else. You guys nailed it. I was setting the UNIX sockets to trust instead of IP. Craigbert On 07/06/2011 06:26 AM, Michael Wood wrote: > Hi > > On 6 July 2011 06:14, Craigbert<postgresql@mysoftforge.com> wrote: >> Tom, >> >> Thanks for replying. >> >> Here is the log: > [...] >> Here is the main part of my pg_hba.conf file: >> # Database administrative login by UNIX sockets >> local all postgres trust >> #local all postgres ident --wcb original >> line > "local" is for UNIX domain sockets, not TCP/IP networking. > >> # TYPE DATABASE USER CIDR-ADDRESS METHOD >> >> # "local" is for Unix domain socket connections only >> local all all trust >> # method above WAS ident --wcb >> # IPv4 local connections: >> host all all 127.0.0.1/32 md5 > Here you're specifying that when someone connects over TCP/IP to > localhost, they must use a username and password using MD5 for the > password hashing algorithm. > >> # IPv6 local connections: >> host all all ::1/128 md5 >> >> I did a "find / -name pg_hba.conf" and this was the only file that was >> found. >> >> Based on what you are saying and the log file I have set up my pg_hba.conf >> file incorrectly. >> >> Any idea where I have gone awry? > Unless you can't (e.g. you're using JDBC or something), try connecting > to the UNIX domain socket instead of over TCP/IP. If this is not an > option, try changing "md5" to "trust" for the "host ... 127.0.0.1/32" > line. But I don't recommend this. It would be better to set up a > username and password and use that. > > If you're just trying to connect using psql as user postgres, try this: > > craigbert@kubuntu$ sudo su - postgres > postgres@kubuntu$ psql > > If you don't specify a host it should use the UNIX domain socket > instead of TCP/IP. > >> On 07/05/2011 10:11 PM, Tom Lane wrote: >>> Craigbert<postgresql@mysoftforge.com> writes: >>>> Hello All, >>>> I am running PostgreSQL 8.4 on Kubuntu 11.04. >>>> The server starts without any issues, but I can not connect. I keep >>>> getting an invalid user/password error. >>>> I am connecting to the localhost, default port, user = postgres, >>>> database = postgres >>>> I have tried setting the authentication methods to trust in the >>>> pg_hba.conf file, but it does not make any difference. >>>> What am I doing wrong? >>> You could not get that type of error when running "trust" >>> authentication. So, you aren't. Likely theories are that you forgot to >>> reload or restart the server after changing pg_hba.conf, or that you did >>> reload but it didn't "take" because of some error in the modified hba >>> file (if so, there'll be some complaint about it in the server log >>> file), or that the file you're changing isn't the one being used by the >>> server you're actually connecting to. >>> >>> regards, tom lane