Обсуждение: unable to connect to postgres db via psycopy
All,
Over the last year or so I have developed several scripts to connect to
postgresql. Between last week and today they are no longer connecting.
The system has been upgraded and there may have been a change to
postgresql-server is 'postgresql-server.x86_64 9.4.6-1.fc22'.
The database starts successfully and is accessible from user dan via
psql and all commands appear to work successfully.
The postgres log configuration is not correct so there is no helpful
information there. I am trying to fix that to help debug this issue.
Below is a test script that fails:
#!/usr/bin/python3
import psycopg2
#note that we have to import the Psycopg2 extras library!
import psycopg2.extras
import sys
import pprint
import string
import unittest
from optparse import OptionParser
def main():
conn_string = "host='localhost' dbname='opace0215' user='dan'
port=5432 password='xxx' "
print ("Connecting to database\n ->%s" % (conn_string))
conn = psycopg2.connect(conn_string)
cursori = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
work_mem = 2048
cursori.execute('SET work_mem TO %s', (work_mem,))
cursori.execute('select entity_name from opace')
# f = open("test.txt","w") #opens file with name of "test.txt"
i = 1
while i != 10:
records = cursori.fetchone()
record = records[0]
print(record)
i = i+1
f.close()
conn.commit()
if __name__ == "__main__":
main()
Connecting to database
->host='localhost' dbname='opace0215' user='dan' password='s42340'
port=5432
Traceback (most recent call last):
File "./db_open_test.py", line 35, in <module>
main()
File "./db_open_test.py", line 20, in main
conn = psycopg2.connect(conn_string)
File "/usr/lib64/python3.4/site-packages/psycopg2/__init__.py", line
164, in connect
conn = _connect(dsn, connection_factory=connection_factory,
async=async)
psycopg2.OperationalError: FATAL: Ident authentication failed for user
"dan"
Below are the tables in the db. They all work correctly.
psql --list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
-----------+----------+----------+-------------+-------------+-----------------------
ohlcv | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
opace | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
opace0215 | dan | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
opace0421 | dan | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
opace0714 | dan | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
opace0715 | dan | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
opace0815 | dan | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres +
| | | | |
postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres=CTc/postgres+
| | | | | =c/postgres
(10 rows)
On Mar 12, 2016, at 5:23 PM, Dan Sawyer <dansawyer@earthlink.net> wrote: > psycopg2.OperationalError: FATAL: Ident authentication failed for user "dan" The place to look is in your pg_hba.conf, and makes sure that user "dan" has MD5 password access turned on to the appropriatedatabase(s) for localhost. -- -- Christophe Pettus xof@thebuild.com
Thank you for the reply. In the past I have not used this file. Has there been a recent change? Should the new line be: local all all md5 Below is the configuration section of pg_hba.conf: # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 ident # IPv6 local connections: host all all ::1/128 ident # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgres peer #host replication postgres 127.0.0.1/32 ident #host replication postgres ::1/128 ident On 03/12/2016 05:26 PM, Christophe Pettus wrote: > On Mar 12, 2016, at 5:23 PM, Dan Sawyer <dansawyer@earthlink.net> wrote: > >> psycopg2.OperationalError: FATAL: Ident authentication failed for user "dan" > The place to look is in your pg_hba.conf, and makes sure that user "dan" has MD5 password access turned on to the appropriatedatabase(s) for localhost. > > -- > -- Christophe Pettus > xof@thebuild.com >
On 03/12/2016 07:12 PM, Dan Sawyer wrote: > Thank you for the reply. In the past I have not used this file. Has > there been a recent change? > > Should the new line be: > > local all all md5 Yes if you where not using the host= section in your connect string. local is for local Unix type socket connections. Exception being on Windows where there are no Unix sockets and local is mapped to localhost. Since you are using host= in the connection string then the line(s) you need to change are those starting with host. I would suggest taking a look at the Postgres docs here: http://www.postgresql.org/docs/9.5/interactive/auth-pg-hba-conf.html FYI, the content of pg_hba.conf is determined by how you installed Postgres and therefore should always be verified. > > > Below is the configuration section of pg_hba.conf: > > # TYPE DATABASE USER ADDRESS METHOD > > # "local" is for Unix domain socket connections only > local all all peer > # IPv4 local connections: > host all all 127.0.0.1/32 > ident > # IPv6 local connections: > host all all ::1/128 ident > # Allow replication connections from localhost, by a user with the > # replication privilege. > #local replication postgres peer > #host replication postgres 127.0.0.1/32 ident > #host replication postgres ::1/128 ident > > > On 03/12/2016 05:26 PM, Christophe Pettus wrote: >> On Mar 12, 2016, at 5:23 PM, Dan Sawyer <dansawyer@earthlink.net> wrote: >> >>> psycopg2.OperationalError: FATAL: Ident authentication failed for >>> user "dan" >> The place to look is in your pg_hba.conf, and makes sure that user >> "dan" has MD5 password access turned on to the appropriate database(s) >> for localhost. >> >> -- >> -- Christophe Pettus >> xof@thebuild.com >> > > > -- Adrian Klaver adrian.klaver@aklaver.com