postgresql DAL connection issue

[lucas]

hello one and all,

i am having difficultly connecting postgres 9.1 under web2py v1.98.2
on a centos 6.0 box which has python 2.6.5. i have psycopg2 2.4
installed and working in python when i run the test

#python2.6 web2py -S welcom -N -a 'password'
then
>import psycopg2
>

works just fine. however, when i put

db = DAL('postgres://postgres:passwd@localhost:5432/prac2')

and attempt to click on database administrator, i get for the
traceback

Traceback (most recent call last):
File "/opt/web-apps/web2py/gluon/restricted.py", line 192, in
restricted
exec ccode in environment
File "/opt/web-apps/web2py/applications/prac2_with_postgresql/models/
db.py", line 83, in <module>
db = DAL('postgres://root:@localhost:5432/prac2')
File "/opt/web-apps/web2py/gluon/dal.py", line 3981, in __init__
raise RuntimeError, "Failure to connect, tried %d times:\n%s" %
(attempts, error)
RuntimeError: Failure to connect, tried 5 times:
could not connect to server: Permission denied
Is the server running on host "localhost" (127.0.0.1) and
accepting
TCP/IP connections on port 5432?

any ideas on what i have not done properly??? thank you in advance
and have a great day. lucas

[Mariano Reingart]

Can you test:

#python2.6
>import psycopg2
>psycopg2.connect(database="prac2", user="postgres", password="password", host="localhost")

Maybe the server is down, you have a firewall issue or your
pg_hba.conf is not open for localhost connections:
host all all 127.0.0.1 md5
hostssl all all 127.0.0.1 md5

(use 127.0.0.1 as ipv6 localhost address may conflict)

Best regards,

Mariano Reingart
http://www.sistemasagiles.com.ar
http://reingart.blogspot.com

[lucas]

ok, this is what i did to follow Mariano's suggestions:

as root:
> service iptables stop // should allow or accept all interfaces and ports
> cp /usr/pgsql-9.1/share/pg_hba.conf.sample /usr/pgsql-9.1/share/pg_hba.conf

which the previous has the lines in it
# IPv4 local connections:
host all all 127.0.0.1/32 md5
hostssl all all 127.0.0.1/32 md5

which i restarted postgresql with
> service postgresql-9.1 restart
which worked with confirmation
> service postgresql-9.1 status
(pid 18674) is running...

then into as lucas:
> python
Python 2.6.5 (r265:79063, Jun 25 2011, 08:36:25)
[GCC 4.4.4 20100726 (Red Hat 4.4.4-13)] on linux2
>>>import psycopg2
>>> psycopg2.connect(database="prac2", user="lucas", password="passwdnotposted", host="localhost")

Traceback (most recent call last):

File "<stdin>", line 1, in <module>
psycopg2.OperationalError: FATAL: Ident authentication failed for
user "lucas"

i did the above python stuff after making sure that postgresql had the
user added and proper password under template1 using psql, the
database created, and that user had all granted privileges to the
database.

i can't think of anything else. suggestions, please? lucas

[Mariano Reingart]

Look what your listen address is in postgresql.conf (this is not the
problem but just to check for the record):
listen_addresses = '*'

Put * or 127.0.0.1 or the ip addresses you want to open.

Then, restart the server and test with psql command line tool:

psql prac2 -U lucas -h 127.0.0.1

It should ask you the password and let you in, if not, you still have
a problem with the pg_hba.conf.
This is a example that might work:

local all postgres ident
local all all ident

host all all 127.0.0.1/32 md5

host all all ::1/128 md5
host all all 0.0.0.0/0 md5
hostssl all all 0.0.0.0/0 md5

Remove any line with "ident" method and "host" access type.
Warning: the last two lines open postgresql for all ip addresses (with
password auth)

Best regards,

Mariano Reingart
http://www.sistemasagiles.com.ar
http://reingart.blogspot.com

[lucas]

hello Mariano,

i have made progress. i was editting the wrong pg_hba.conf and
postgresql.conf files. the proper location under centos6.0 was under /
var/lib/pgsql/9.1/data. once i editted those conf files there,
everything started working better, but not completely. let me
summarize:

#testing python and psycopg2 under lucas login worked well:
> python

>>>import psycopg2
>>>psycopg2.connect(database="prac2", user="lucas", password="passwdnotposted", host="localhost")

<connection object at 0x1780650; dsn: 'dbname=prac2 host=localhost
user=lucas password=xxxxxxxxx', closed: 0>
#which looks like it worked

#testing psql under lucas loging with authentication worked well:
> psql prac2 -h localhost
Password:
psql (9.1.1)
Type "help" for help.

prac2=#
#and if a wrong password is entered, psql gives a fatal error and
exits back to bash.

#testing web2py under root (under lucas did not work) login worked
well also:
> python2.6 web2py.py -S welcome -N -a 'nonane939'
web2py Web Framework
Created by Massimo Di Pierro, Copyright 2007-2011
Version 1.98.2 (2011-08-04 00:47:09)
Database drivers available: SQLite3, pymysql, PostgreSQL
WARNING:web2py:import IPython error; use default python shell

Python 2.6.5 (r265:79063, Jun 25 2011, 08:36:25)
[GCC 4.4.4 20100726 (Red Hat 4.4.4-13)] on linux2

Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>>import psycopg2
>>>DAL('postgres://lucas:passwdnotposted@localhost/prac2')
<DAL {'_migrate_enabled': True, '_lastsql': 'SET
standard_conforming_strings=on;', '_db_codec': 'UTF-8', '_timings':
[('BEGIN;', 0.00039315223693847656), ("SET CLIENT_ENCODING TO
'UNICODE';", 0.00017309188842773438), ('SET
standard_conforming_strings=on;', 0.00014185905456542969)],
'_fake_migrate': False, '_dbname': 'postgres', '_request_tenant':
'request_tenant', '_adapter': <gluon.dal.PostgreSQLAdapter object at
0x15aac50>, '_pending_references': {}, '_fake_migrate_all': False,
'check_reserved': None, '_uri': 'postgres://lucas:nonane939@localhost/
prac2', 'tables': [], '_migrate': True, '_pool_size': 0,
'_common_fields': [], '_uri_hash': '623031fe80a4c6f15d3e4616f2919fb6'}
>
#which as you can see the db instance was created fine.

however, when i try the last step, in my mind, which is use this in
the db.py model file:

db = DAL('postgres://lucas:passwdnotposted@localhost/prac2')

i still get the Permission denied problem when i try to access the
"database administration" button on the sites main edit page. does
this make sense?

thank you in advance. lucas

[lucas]

oh damn,

i just figured it out. Mariano, everything you told me was correct,
thank you.

i finally remembered that SELinux might be blocking it. so i disabled
it temporarily with,

echo 0 > /selinux/enforce

and then tried to access the "database administration" and it created
the tables and worked fine after that.

so i just just need to figure out whether to keep selinux on and
assign the proper permissions, or just disable it altogether. i am
more for the former which has more security but i am not sure how to
implement it with apache/postgres/etc with web2py. suggestions?
advice? howto, please?

lucas

[Abhishek Gupta]

Hi,

I am encountering the exact same problem as mentioned by Lucas in one the earlier threads :

msql db_name -U user_name -h localhost 

works

import psycopg2

psycopg2.connect

works

python web2py.py -S app_name
DAL(...) works too

But, still I get error 

RuntimeError: Failure to connect, tried 5 times:

 

Also, it might help that I was working on mysql and have now shifted to postresql. Please tell me how can I debug it or if any other information is needed as well.