psycopg authentication

[jslowery]

Hello, I'm new to both PostgreSQL and psycopg and I'm trying to connect
to my database running on localhost. I have postgres setup to do md5
authentication and this works when using a db admin tool on my local
network. For some reason, psycopg fails with IDENT authentication.

import psycopg
psycopg.connect ("dbname=jlower y user=jlowery host=localhost

password=XXX")
Traceback (most recent call last):
File "<stdin>", line 1, in ?
psycopg.Operati onalError: FATAL 1: IDENT authentication failed for
user "jlowery"

Is there a way to make this module connect with MD5 auth?

[Lee Harr]

On 2005-03-12, js******@gmail. com <js******@gmail .com> wrote:

Hello, I'm new to both PostgreSQL and psycopg and I'm trying to connect
to my database running on localhost. I have postgres setup to do md5
authentication and this works when using a db admin tool on my local
network. For some reason, psycopg fails with IDENT authentication.

import psycopg
psycopg.connect ("dbname=jlower y user=jlowery host=localhost

password=XXX")
Traceback (most recent call last):
File "<stdin>", line 1, in ?
psycopg.Operati onalError: FATAL 1: IDENT authentication failed for
user "jlowery"

Is there a way to make this module connect with MD5 auth?

Are you sure you do not have local connections set up
for md5 and host authentications still set to ident?

Did you restart the server after updating pg_hba.conf?

[jslowery]

I did, the pg_hba conf is a big tricky to me. I've tried the following
things.. the commented out lines I also tried.

#local all ident
sameuser
local all md5
host all 127.0.0.1 255.0.0.0 ident
sameuser
#host all 127.0.0.1 255.255.255.255 md5
host all 0.0.0.0 0.0.0.0 md5

I have an admin program "EMS PostgreSQL Manager" on my lan and it
connects fine with my user name and password.

[jslowery]

Thanks for the reply. I figured it out, the problem was with my
postgresql configuration. the following seemed to do the trick.

local all
md5
host all 0.0.0.0 0.0.0.0 md5

[Glauco]

I'm rebuilding my old library i've done some year ago using python and
postgres.

My problem is to do a middle layer over pycopg for eliminate type
casting problem in postgres in all direction.

i've resolved this doing a C extension in python and manipulating only
string and int in my application.

this is my example:
import sqlvar

sql = """insert into mytable (myint, mytext, maydate)
values
(%d,%s,%s);""" % (sqlvar.number( myvalue1),
sqlvar.text(myv alue2), sqlvar.date(myv alue3) )

all problem concerning quoting, " ' -> ''", null, None, 0, empty string
is solved by the sqlvar lib.
I want to eliminate this problem forever, i want to manipulate only
correct type as DateTime obj in my application so i want to arrive at
this solution:
sql = """insert into mytable (myint, mytext, maydate)
values
(%s,%s,%s);""" % (myvalue1, myvalue2, myvalue3)

without all time check for none, empty string and so on...

I've seen API of psycopg and some procedure for the solution but all are
too spaghetti for me.

I'm only with this problem ?
Glauco
--

\\\|///
\\ - - //
( @ @ )
+---------------------oOOo-( )-oOOo--------------------------+
| |
| I have a dream that one day this nation will rise up and |
| live out the true meaning of its creed: "We hold these |
| truths to be self-evident:that all men are created equal.|
| I have a dream that one day on the red hills of Georgia |
| the sons of former slaves and the sons of former |
| slaveowners will be able to sit down together at a table |
| of brotherhood. |
| I have a dream that one day even the state of Mississippi, |
| a desert state, sweltering with the heat of injustice |
| and oppression, will be transformed into an oasis of |
| freedom and justice. |
| I have a dream that my four children will one day live in |
| a nation where they will not be judged by the color of |
| their skin but by the content of their character. |
| I have a dream today. |
| |
| Martin Luther King, Jr 28 Ago 1963 |
+------------------------------------------------------------+
| glauco(at)urila nd.it |
| www.uriland.it .oooO ICQ: 115323690 |
+--------------------- ( )------ Oooo.---------------------+
\ ( ( )
\_) ) /
(_/

[Gerhard Haering]

On Fri, Jul 08, 2005 at 04:23:50PM +0200, Glauco wrote:

[...]
My problem is to do a middle layer over pycopg for eliminate type
casting problem in postgres in all direction.

i've resolved this doing a C extension in python and manipulating only
string and int in my application.

this is my example:
import sqlvar

sql = """insert into mytable (myint, mytext, maydate)
values
(%d,%s,%s);""" % (sqlvar.number( myvalue1),
sqlvar.text(myv alue2), sqlvar.date(myv alue3) )

all problem concerning quoting, " ' -> ''", null, None, 0, empty string
is solved by the sqlvar lib. [...]

Instead of quoting Python values yourself appropriately for each type,
just let the DB-API module do its work. Use parametrized queries and
then supply the arguments to the query:

cur = con.cursor()
intVal = 42
textVal = "Jason's house"
dateVal = datetime.date(2 005, 7, 8)
cur.execute("""
insert into mytable(myint, mytext, mydate)
values (%s, %s, %s)
""", (intval, textVal, dateVal))

The execute(many) method has an optional second parameter, which is a
tuple of values for your parametrized query.

There are different styles to parametrize queries among the various
DB-API modules. psycopg uses the pyformat one, where you just use %s as
placeholders.

It will happily quote all Python values of types int, str, date, float,
etc. for you. It's also possible to teach it how to quote other custom
data types, but you'll normally not need this.

HTH,

-- Gerhard
--
Gerhard Häring - [e-mail] - Python, web & database development

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFCzo7KdIO 4ozGCH14RAtnmAJ 9F1UB3AWyiUwjw8 D52kRbYOmIRCQCf d8IL
lbqe8geaR6RfDP3 pCrq3Bxg=
=2iQo
-----END PGP SIGNATURE-----

[Glauco]

Gerhard Haering wrote:

On Fri, Jul 08, 2005 at 04:23:50PM +0200, Glauco wrote:

[...]
My problem is to do a middle layer over pycopg for eliminate type
casting problem in postgres in all direction.

i've resolved this doing a C extension in python and manipulating only
string and int in my application.

this is my example:
import sqlvar

sql = """insert into mytable (myint, mytext, maydate)
values
(%d,%s,%s);""" % (sqlvar.number( myvalue1),
sqlvar.text(m yvalue2), sqlvar.date(myv alue3) )

all problem concerning quoting, " ' -> ''", null, None, 0, empty string
is solved by the sqlvar lib. [...]

Instead of quoting Python values yourself appropriately for each type,
just let the DB-API module do its work. Use parametrized queries and
then supply the arguments to the query:

cur = con.cursor()
intVal = 42
textVal = "Jason's house"
dateVal = datetime.date(2 005, 7, 8)
cur.execute("""
insert into mytable(myint, mytext, mydate)
values (%s, %s, %s)
""", (intval, textVal, dateVal))

The execute(many) method has an optional second parameter, which is a
tuple of values for your parametrized query.

There are different styles to parametrize queries among the various
DB-API modules. psycopg uses the pyformat one, where you just use %s as
placeholders.

It will happily quote all Python values of types int, str, date, float,
etc. for you. It's also possible to teach it how to quote other custom
data types, but you'll normally not need this.

HTH,

-- Gerhard

Gerhard thank you very much, this example explain me some idea, but
anyway don't resolve the core question.
In you example when dateVal is a None or textVal is none.
argument x must be DateTime, not None.
so i must manipulate for the empty string or None cases

Glauco
--

[Gerhard Häring]

Glauco wrote:

[...]
Gerhard thank you very much, this example explain me some idea, but
anyway don't resolve the core question.
In you example when dateVal is a None or textVal is none.
argument x must be DateTime, not None.
so i must manipulate for the empty string or None cases

No, you don't. If you leverage the two-parameter form of .execute(many),
your Python DB-API module will handle the None => NULL case for you
transparently.

-- Gerhard