HTTP user authentication against PostgreSQL

Jeremy Palmer-2 wrote

I'm setting up an apache server and was wondering if it is possible to
setup HTTP user authentication against PostgreSQL authentication? I see
http://www.giuseppetanzilli.it/mod_auth_pgsql2/, but that requires a
custom username table. I want to be able to leverage the PostgreSQL
Authentication system.

I am sure that code can be written that will do what you describe here - an
external program that uses psql to try and connect to the database and react
to the response would be sufficient, if not particularly performant. I am
doubtful that it has already but there is a slim chance you might get a ping
here.

Personally, I would consider having both Apache and PostgreSQL talk to a
LDAP database if you really need to have a single point of identity
definition.

Doing what you describe here doesn't seem to me to be a good idea as
PostgreSQL has no provisions for making its internal catalogs usable in this
manner - or even at all outside of the libpq protocol - for security
reasons. The internal user database for a system is seldom made accessible
for other applications that do not intend to make use of the actual service
that system is providing.

I'm not apt to have any other useful suggestions but describing why you want
to do this thing may encourage others to suggest additional alternatives.

David J.

--
View this message in context: http://postgresql.nabble.com/HTTP-user-authentication-against-PostgreSQL-tp5836111p5836115.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

David G Johnston wrote

Personally, I would consider having both Apache and PostgreSQL talk to a
LDAP database if you really need to have a single point of identity
definition.

The PostgreSQL DB is currently setup with Kerberos for Windows SSO, as well as MD5 password authentication for another pool of other PostgreSQL users who are not part of our Active Directory. LDAP could be used, but then we would have to move the current external users into the AD, and I'm not sure that can happen due to policy reasons.

Doing what you describe here doesn't seem to me to be a good idea as
PostgreSQL has no provisions for making its internal catalogs usable in this
manner - or even at all outside of the libpq protocol - for security
reasons. The internal user database for a system is seldom made accessible
for other applications that do not intend to make use of the actual service
that system is providing.

I'm not apt to have any other useful suggestions but describing why you want
to do this thing may encourage others to suggest additional alternatives.

Looking at building a JSON RESTFul API that connects to the database to performs CRUD operations.

David J.

This message contains information, which may be in confidence and may be subject to legal privilege. If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify us immediately (Phone 0800 665 463 or info@linz.govt.nz) and destroy the original message. LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ. Thank You.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 1/30/2015 12:31 AM, Jeremy Palmer wrote:

The PostgreSQL DB is currently setup with Kerberos for Windows SSO, as well as MD5 password authentication for another pool of other PostgreSQL users who are not part of our Active Directory. LDAP could be used, but then we would have to move the current external users into the AD, and I'm not sure that can happen due to policy reasons.

active directory IS a sort of ldap. I'd be looking at connecting
apache to that AD too, and not trying to do it via postgres. associating
a postgres connection with each HTTP client session would be inefficent
and ugly, especially as http can have multiple threads for a single
client session, yet http is considered sessionless.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Ok thanks for the advice John - much appreciated.
________________________________________
From: pgsql-general-owner@postgresql.org [pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce [pierce@hogranch.com]
Sent: Friday, 30 January 2015 10:33 p.m.
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] HTTP user authentication against PostgreSQL

On 1/30/2015 12:31 AM, Jeremy Palmer wrote:

The PostgreSQL DB is currently setup with Kerberos for Windows SSO, as well as MD5 password authentication for another pool of other PostgreSQL users who are not part of our Active Directory. LDAP could be used, but then we would have to move the current external users into the AD, and I'm not sure that can happen due to policy reasons.

active directory IS a sort of ldap. I'd be looking at connecting
apache to that AD too, and not trying to do it via postgres. associating
a postgres connection with each HTTP client session would be inefficent
and ugly, especially as http can have multiple threads for a single
client session, yet http is considered sessionless.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

This message contains information, which may be in confidence and may be subject to legal privilege. If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify us immediately (Phone 0800 665 463 or info@linz.govt.nz) and destroy the original message. LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ. Thank You.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general