Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 8E64A63F309
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 22 Mar 2010 15:11:14 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 19735-05-6
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 22 Mar 2010 18:11:03 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from exprod7og109.obsmtp.com (exprod7og109.obsmtp.com [64.18.2.171])
	by mail.postgresql.org (Postfix) with SMTP id 985E7633E38
	for <pgsql-admin@postgresql.org>; Mon, 22 Mar 2010 15:09:23 -0300 (ADT)
Received: from source ([74.125.82.44]) by exprod7ob109.postini.com
	([64.18.6.12]) with SMTP
	ID DSNKS6eyUjREzQ7j/zTYxVkXs9U+QklbfHcF@postini.com;
	Mon, 22 Mar 2010 11:09:23 PDT
Received: by wwg30 with SMTP id 30so2306716wwg.3
	for <pgsql-admin@postgresql.org>; Mon, 22 Mar 2010 11:09:21 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.216.89.84 with SMTP id b62mr2612001wef.226.1269281360665;
 Mon,
	22 Mar 2010 11:09:20 -0700 (PDT)
In-Reply-To: <4BA4066E.4060205@akegroup.org>
References: <4BA4066E.4060205@akegroup.org>
Date: Mon, 22 Mar 2010 14:09:20 -0400
Message-ID: <d3ab2ec81003221109i10591e69x31db24de79ebcc39@mail.gmail.com>
Subject: Re: Store database users in custom table
From: Scott Mead <scott.lists@enterprisedb.com>
To: Antoine POPINEAU <apognu@akegroup.org>
Cc: pgsql-admin@postgresql.org
Content-Type: multipart/alternative; boundary=0016e6d971029f5e6404826798b0
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5
	tests=BAYES_00=-2.599, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201003/154
X-Sequence-Number: 33953

--0016e6d971029f5e6404826798b0
Content-Type: text/plain; charset=ISO-8859-1

2010/3/19 Antoine POPINEAU <apognu@akegroup.org>

> Hi,
>
> I am developping a Java application, and using a PostgreSQL backend
> database. Theorically, there might be lots of users, each with potentially
> different permissions over the different tables.
>
> I could add those user to the database with a classic CREATE USER, but that
> doesn't seem efficient to me, since I've already got a custom table with
> users data and, amongst others, password.
>
> So my question is simple (the answer might not be, though): is there a
> possibility to tell Postgres to fetch user logins and passwords in a custom
> table (perhaps respecting a certain schema) instead of the default location?
>

There isn't, but the question to ask yourself is:

 Do you want application level users to be able to login to the database
directly???

 If the answer is yes, then you may want to use a centralized authentication
mechanism like LDAP. (postgres can use LDAP authentication).


  If the answer is no, then build your own auth scheme in the application
and have the application login to the database as a specific user ... i.e.

user bob.foo logins to web interface
provides password: login123

  java app says:  Connect to database,
 user: app_read
 pass: %32sd$s2

   Java app says, select user_id, password from users where user_name =
'bob.foo'
    java app runs:
 if result.password = user_entered_password
    then
        login allowed
    else
         denied
    end if

  Obviously, you'd want to use a hash of the password to avoid sending it
over the wire, but I think you get the idea.


--Scott M



 --Scott M

>
> Thank you.
>
> Antoine POPINEAU.
>

--0016e6d971029f5e6404826798b0
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div class=3D"gmail_quote">2010/3/19 Antoine POPINEAU <span dir=3D"ltr">&lt=
;<a href=3D"mailto:apognu@akegroup.org">apognu@akegroup.org</a>&gt;</span><=
br><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left=
:1px #ccc solid;padding-left:1ex;">
Hi,<br>
<br>
I am developping a Java application, and using a PostgreSQL backend databas=
e. Theorically, there might be lots of users, each with potentially differe=
nt permissions over the different tables.<br>
<br>
I could add those user to the database with a classic CREATE USER, but that=
 doesn&#39;t seem efficient to me, since I&#39;ve already got a custom tabl=
e with users data and, amongst others, password.<br>
<br>
So my question is simple (the answer might not be, though): is there a poss=
ibility to tell Postgres to fetch user logins and passwords in a custom tab=
le (perhaps respecting a certain schema) instead of the default location?<b=
r>
</blockquote><div><br></div><div>There isn&#39;t, but the question to ask y=
ourself is:</div><div><br></div><div>=A0Do you want application level users=
 to be able to login to the database directly???</div><div><br></div><div>
=A0If the answer is yes, then you may want to use a centralized authenticat=
ion mechanism like LDAP. (postgres can use LDAP authentication).</div><div>=
<br></div><div><br></div><div>=A0=A0If the answer is no, then build your ow=
n auth scheme in the application and have the application login to the data=
base as a specific user ... i.e.<br>
</div><div><br></div><div>user bob.foo logins to web interface</div><div>pr=
ovides password: login123</div><div><br></div><div>=A0=A0java app says: =A0=
Connect to database,<br>=A0user: app_read</div><div>=A0pass: %32sd$s2</div>=
<div>=A0</div>
<div>=A0=A0 Java app says, select user_id, password from users where user_n=
ame =3D &#39;bob.foo&#39;</div><div>=A0=A0 =A0java app runs:</div><div>=A0i=
f result.password =3D user_entered_password</div><div>=A0=A0 =A0then</div><=
div>=A0=A0 =A0 =A0 =A0login allowed</div>
<div>=A0=A0 =A0else</div><div>=A0=A0 =A0 =A0 =A0 denied</div><div>=A0=A0 =
=A0end if</div><div><br></div><div>=A0=A0Obviously, you&#39;d want to use a=
 hash of the password to avoid sending it over the wire, but I think you ge=
t the idea.</div><div><br>
</div><div><br></div><div>--Scott M</div><div>=A0=A0=A0</div><div>=A0=A0 =
=A0 =A0 =A0 =A0=A0</div><div><br></div><div>=A0--Scott M</div><blockquote c=
lass=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;=
padding-left:1ex;">
<br>
Thank you.<br><font color=3D"#888888">
<br>
Antoine POPINEAU.<br>
</font></blockquote></div><br>

--0016e6d971029f5e6404826798b0--