Re: Does psycopg2 support Kerberos for Postgres? - Mailing list psycopg

You are most likely correct about local log in.

This is the string that I'm using:
    sqlalchemy.create_engine("postgresql+psycopg2://fqdn_of_host/db_name")
I tried adding "? krbsrvname=postgres" at the end as well, but got the error aforementioned.

Thanks.

Yang


-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Monday, December 05, 2016 3:49 PM
To: Yang Gao; 'psycopg@postgresql.org'
Subject: Re: [psycopg] Does psycopg2 support Kerberos for Postgres?

On 12/05/2016 10:17 AM, Yang Gao wrote:
> Logging in a local host w/o password is fine for psycopg2. Not sure if it goes though GSSAPI though, but the remote
serverdefinitely has GSSAPI enabled. 

Best guess is that the local connections are being done via a socket and pg_hba.conf is letting those connections in
undertrust auth: 

https://www.postgresql.org/docs/9.5/static/client-authentication.html

Remember in pg_hba.conf first match wins. You might want to look in this file on both the local and remote servers

>
> I did try the krbsrvname option, but got a complaint from _connect:
>     psycopg2.OperationalError: invalid connection option "krbsrvname"

So what was your connection string in the code?

>
> Thanks.
>
> Yang
>
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Sent: Monday, December 05, 2016 11:57 AM
> To: Yang Gao; 'psycopg@postgresql.org'
> Subject: Re: [psycopg] Does psycopg2 support Kerberos for Postgres?
>
> On 12/05/2016 08:38 AM, Yang Gao wrote:
>> Hi, Adrian,
>>
>>   Thanks for the reply.
>>
>>   The Postgres server is Kerberos enabled, as I can log in using either psql CLI, or pyodbc lib, or pg_admin client
UIwithout password. 
>
> I assume both the server at localhost and the remote_host you mention below have Kerberos enabled.
>
>>
>>   With the bare bone code as below, when I run against the localhost, I am able to log in. However, it fails if it's
theremote host. The error says GSSAPI authentication not supported as below. 
>
> So does that apply to the other clients also, they can log in locally but not remotely?
>
> Or can they log in to the remote server using GSSAPI?
>
>>   Please advise.
>>
>>   Thank you.
>>
>> Yang
>> ----
>>
>> Traceback (most recent call last):
>>   File "/home/yangg/IdeaProjects/PyPlayground/psycopg2_test.py", line 15, in <module>
>>     conn = psycopg2.connect(conn_string)
>>   File "/home/yangg/.conda/envs/py2/lib/python2.7/site-packages/psycopg2/__init__.py", line 164, in connect
>>     conn = _connect(dsn, connection_factory=connection_factory,
>> async=async)
>> psycopg2.OperationalError: GSSAPI authentication not supported
>>
>> ----
>>
>> from pprint import pprint as p
>> import psycopg2
>> import os
>>
>> conn_string = "host='remote_host_XXX' dbname='table_YYY'"
>
> I don't use Kerberos but I gotta believe the below is important:
>
> https://www.postgresql.org/docs/9.5/static/libpq-connect.html
>
> krbsrvname
>
>      Kerberos service name to use when authenticating with GSSAPI.
> This must match the service name specified in the server configuration
> for Kerberos authentication to succeed. (See also Section 19.3.3.)
>
> https://www.postgresql.org/docs/9.5/static/auth-methods.html#GSSAPI-AU
> TH
>
> When GSSAPI uses Kerberos, it uses a standard principal in the format servicename/hostname@realm. The PostgreSQL
serverwill accept any principal that is included in the keytab used by the server, but care needs to be taken to
specifythe correct principal details when making the connection from the client using the krbsrvname connection
parameter.(See also Section 31.1.2.) The installation default can be changed from the default postgres at build time
using./configure --with-krb-srvnam=whatever. In most environments, this parameter never needs to be changed. Some
Kerberosimplementations might require a different service name, such as Microsoft Active Directory which requires the
servicename to be in upper case (POSTGRES). 
>
>
>
>> conn = psycopg2.connect(conn_string)
>> cursor = conn.cursor()
>> cursor.execute("SELECT version()")
>> records = cursor.fetchall()
>> p(records)
>>
>> ----
>> -----Original Message-----
>> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
>> Sent: Saturday, December 03, 2016 5:53 PM
>> To: Yang Gao; 'psycopg@postgresql.org'
>> Subject: Re: [psycopg] Does psycopg2 support Kerberos for Postgres?
>>
>> On 12/02/2016 10:46 AM, Yang Gao wrote:
>>> Dear Psycopg Dev,
>>>
>>>
>>>
>>>   Not sure if this is the right channel but thought to give it a try anyway.
>>>
>>>
>>>
>>>   This is Yang Gao from Two Sigma. We are trying to use your driver
>>> along with SqlAlchemy to access a Postgres DB with Kerberos support
>>> and failed to make it work. We have done quite some googling but
>>> found almost not relevant info. The driver does not seem to
>>> recognize the "krbsrvname" or  "gsslib" param suggested by Postgres doc.
>>>
>>>
>>>
>>>   We were wondering if Kerberos is supported at all by the driver?
>>> And if yes, is there any documentation or example?
>>
>> Second question. Was your Postgres database built with the proper support?:
>>
>> https://www.postgresql.org/docs/9.5/static/auth-methods.html
>>
>> GSSAPI is an industry-standard protocol for secure authentication defined in RFC 2743. PostgreSQL supports GSSAPI
withKerberos authentication according to RFC 1964. GSSAPI provides automatic authentication (single sign-on) for
systemsthat support it. The authentication itself is secure, but the data sent over the database connection will be
sentunencrypted unless SSL is used. 
>>
>> GSSAPI support has to be enabled when PostgreSQL is built; see
>> Chapter
>> 15 for more information.
>>
>>>
>>>
>>>
>>>   Thank you very much.
>>>
>>>
>>>
>>> Yang
>>>
>>>
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>


--
Adrian Klaver
adrian.klaver@aklaver.com