Обсуждение: pg_hba.conf and ssl problem has me stumped
Hi folks,
I've been searching archives and helpfiles, and I am still very
confused.
My problem: newly created users cannot connect to a database. Older
users can connect.
The scenario: run pqsl from the webserver to connect to the database
server.
Example:
================================================
cath@webserver:~$ psql -h dbserver -U testuser -d casemgr
psql: FATAL: no pg_hba.conf entry for host "150.203.157.27", user
"testuser", database "casemgr", SSL off
cath@webserver:~$ psql -h dbserver -U cath -d casemgr
Password:
Welcome to psql 7.4.8, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
================================================
Now this is odd. It's from the SAME account on the SAME machine. Old
accounts work, automatically connect with ssl, new accounts don't.
Did I do something weird when making my account way back when, that I
forgot for the new user?
I just used createuser as normal... And the test user can connect
successfully from the database server.
Anyone have any ideas?
Thanks
Cath
Cath Lawrence, Cath.Lawrence@anu.edu.au
Senior Scientific Programmer, Centre for Bioinformation Science,
John Curtin School of Medical Research (room 4087)
& Mathematical Sciences Institute, John Dedman bldg (room 1189)
Australian National University, Canberra ACT 0200
ph: JCSMR 02-61257963 MSI: 02-61254628 mobile: 0421-902694
On Wed, 2005-10-12 at 18:11 +1000, Cath Lawrence wrote: > Hi folks, > I've been searching archives and helpfiles, and I am still very > confused. > > My problem: newly created users cannot connect to a database. Older > users can connect. > The scenario: run pqsl from the webserver to connect to the database > server. > > Example: > ================================================ > cath@webserver:~$ psql -h dbserver -U testuser -d casemgr > psql: FATAL: no pg_hba.conf entry for host "150.203.157.27", user > "testuser", database "casemgr", SSL off > > cath@webserver:~$ psql -h dbserver -U cath -d casemgr > Password: > Welcome to psql 7.4.8, the PostgreSQL interactive terminal. Please show us what you have in pg_hba.conf. -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== Do you want to know God? http://www.lfix.co.uk/knowing_god.html
On 12/10/2005, at 6:16 PM, Oliver Elphick wrote: > On Wed, 2005-10-12 at 18:11 +1000, Cath Lawrence wrote: >> My problem: newly created users cannot connect to a database. Older >> users can connect. >> The scenario: run psql from the webserver to connect to the database >> server. >> Example: >> ================================================ >> cath@webserver:~$ psql -h dbserver -U testuser -d casemgr >> psql: FATAL: no pg_hba.conf entry for host "150.203.157.27", user >> "testuser", database "casemgr", SSL off >> >> cath@webserver:~$ psql -h dbserver -U cath -d casemgr >> Password: >> Welcome to psql 7.4.8, the PostgreSQL interactive terminal. > Please show us what you have in pg_hba.conf. Sure, though as I said it's the same machine connection... Not an awful lot, as you see: # TYPE DATABASE USER CIDR-ADDRESS METHOD @remove-line-for-nolocal@# "local" is for Unix domain socket connections only @remove-line-for-nolocal@local all all @authmethod@ # IPv4 local connections: host all all 127.0.0.1/32 @authmethod@ # webserver password access host all all 150.203.157.27/32 md5 # IPv6 local connections: #host all all ::1/128 @authmethod@ thanks Cath Cath Lawrence, Cath.Lawrence@anu.edu.au Senior Scientific Programmer, Centre for Bioinformation Science, John Curtin School of Medical Research (room 4087) & Mathematical Sciences Institute, John Dedman bldg (room 1189) Australian National University, Canberra ACT 0200 ph: JCSMR 02-61257963 MSI: 02-61254628 mobile: 0421-902694
Cath Lawrence <Cath.Lawrence@anu.edu.au> writes:
> On 12/10/2005, at 6:16 PM, Oliver Elphick wrote:
>> Please show us what you have in pg_hba.conf.
> Sure, though as I said it's the same machine connection...
> Not an awful lot, as you see:
> # TYPE DATABASE USER CIDR-ADDRESS METHOD
> @remove-line-for-nolocal@# "local" is for Unix domain socket
> connections only
> @remove-line-for-nolocal@local all
> all @authmethod@
> # IPv4 local connections:
> host all all 127.0.0.1/32 @authmethod@
> # webserver password access
> host all all 150.203.157.27/32 md5
> # IPv6 local connections:
> #host all all ::1/128 @authmethod@
That is not your pg_hba.conf file. It looks like an unmodified
pg_hba.conf.sample file ... which the postmaster would reject if
presented in that form, because the @-thingies aren't valid syntax.
regards, tom lane
On 13/10/2005, at 10:16 AM, Tom Lane wrote: > That is not your pg_hba.conf file. It looks like an unmodified > pg_hba.conf.sample file > I am *SOOO* embarrassed. As soon as I looked at the real one I found my problem. Not sure why I had two - one in /usr/local/pgsql/data and the other in /usr/local/pgsql/share... Anyway, the data one was right. Thanks guys *blushes* cheers Cath Cath Lawrence, Cath.Lawrence@anu.edu.au Senior Scientific Programmer, Centre for Bioinformation Science, John Curtin School of Medical Research (room 4087) & Mathematical Sciences Institute, John Dedman bldg (room 1189) Australian National University, Canberra ACT 0200 ph: JCSMR 02-61257963 MSI: 02-61254628 mobile: 0421-902694