Store database users in custom table

This is a cryptographically signed message in MIME format.

--------------ms070003040605040403050409
Content-Type: multipart/mixed;
boundary="------------040100060804010000050606"

This is a multi-part message in MIME format.
--------------040100060804010000050606
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

Hi,

I am developping a Java application, and using a PostgreSQL backend=20
database. Theorically, there might be lots of users, each with=20
potentially different permissions over the different tables.

I could add those user to the database with a classic CREATE USER, but=20
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=20
possibility to tell Postgres to fetch user logins and passwords in a=20
custom table (perhaps respecting a certain schema) instead of the=20
default location?

Thank you.

Antoine POPINEAU.

--------------040100060804010000050606
Content-Type: text/x-vcard; charset=utf-8;
name="apognu.vcf"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="apognu.vcf"

begin:vcard
fn:Antoine POPINEAU
n:POPINEAU;Antoine
org:Antoine POPINEAU
adr:;;24 rue du Berry;Chevilly-Larue;;94550;France
email;internet:apognu@akegroup.org
tel;cell:+33 6 84 83 01 34
x-mozilla-html:FALSE
url:http://www.apognu.net
version:2.1
end:vcard


--------------040100060804010000050606--

--------------ms070003040605040403050409
Content-Type: application/pkcs7-signature; name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="smime.p7s"
Content-Description: S/MIME Cryptographic Signature

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIIHEDCC
A4QwggJsoAMCAQICAUgwDQYJKoZIhvcNAQEFBQAwVjESMBAGA1UEChMJQUtF IEdyb3VwMQsw
CQYDVQQGEwJGUjESMBAGA1UEAxMJQUtFIEdyb3VwMR8wHQYJKoZIhvcNAQkB FhBha2VAYWtl
Z3JvdXAub3JnMB4XDTEwMDEyOTE4MjUwNVoXDTExMDEyOTE4MjUwNVowgYUx EjAQBgNVBAoT
CUFLRSBHcm91cDELMAkGA1UEBhMCRlIxGTAXBgNVBAMTEEFudG9pbmUgUE9Q SU5FQVUxIjAg
BgkqhkiG9w0BCQEWE2Fwb2dudUBha2Vncm91cC5vcmcxETAPBgNVBAQTCFBP UElORUFVMRAw
DgYDVQQqEwdBbnRvaW5lMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKC AQEA1FXHu+4g
W/bSoZcLCB9fCiGzZYNdclsuk5YDAFBOULVbUYLMbYzgmWRa9yTt7mOioNK+ Hcp4aBBtyo2J
2dvWfp9ISR9aGrIdaqPjplBcQaL4TpdSXueXQi7D8gSsNaW+w0oYq0VESOm/ CnfDsNxYvcyF
6LQY6aPNLoHxbbVhGtaXuE1XKiXh5jrpPzxhEXsqq9Hc9vgysLw0keBeh+u0 fJWupFw/Vyci
lrqWqZbIT5tgdtTSqQ1Iz/z63iKiWTzD9oi9SOl3QkPekHgyvWf4FTfqdmP8 CAAxuGh8ptdo
dAe/mRJtifHDHSaadkefDyIJP7lyjgMeX9EY+UgIj95GkwIDAQABoy0wKzAJ BgNVHRMEAjAA
MBEGCWCGSAGG+EIBAQQEAwIEsDALBgNVHQ8EBAMCBPAwDQYJKoZIhvcNAQEF BQADggEBAFsT
d7zW9MhCPTMrNuEyR/acA7F6iSHxdyUqMlXjap/+1H7JNLxNkN9D6kaRgTMh AEu96b08d9Z/
3/8GyannBoraKbN44MWCKzeyxXHkGHFbIizUTYNcoGYufqlmam3Wvs8VFKUW rAlCq1BGuCEQ
rUVTwshV61QH0CXJVn+GFRiNeoh1qr3XpLUhNWzagxILG2vv9gXFnw7JNFMx 2zOyOTFRRBGw
hjTvgjYTMGR48Vyl557aTF4rtxntcvTjgUJjgEvwA6s/Kx3wlpzxX4tEOzPD gIKsrTOQaJp6
LHNKNRH0W3ssDiH/a3/GDwCLfqJJZImnlpt3ffklrQznYgbBK9kwggOEMIIC bKADAgECAgFI
MA0GCSqGSIb3DQEBBQUAMFYxEjAQBgNVBAoTCUFLRSBHcm91cDELMAkGA1UE BhMCRlIxEjAQ
BgNVBAMTCUFLRSBHcm91cDEfMB0GCSqGSIb3DQEJARYQYWtlQGFrZWdyb3Vw Lm9yZzAeFw0x
MDAxMjkxODI1MDVaFw0xMTAxMjkxODI1MDVaMIGFMRIwEAYDVQQKEwlBS0Ug R3JvdXAxCzAJ
BgNVBAYTAkZSMRkwFwYDVQQDExBBbnRvaW5lIFBPUElORUFVMSIwIAYJKoZI hvcNAQkBFhNh
cG9nbnVAYWtlZ3JvdXAub3JnMREwDwYDVQQEEwhQT1BJTkVBVTEQMA4GA1UE KhMHQW50b2lu
ZTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBANRVx7vuIFv20qGX CwgfXwohs2WD
XXJbLpOWAwBQTlC1W1GCzG2M4JlkWvck7e5joqDSvh3KeGgQbcqNidnb1n6f SEkfWhqyHWqj
46ZQXEGi+E6XUl7nl0Iuw/IErDWlvsNKGKtFREjpvwp3w7DcWL3Mhei0GOmj zS6B8W21YRrW
l7hNVyol4eY66T88YRF7KqvR3Pb4MrC8NJHgXofrtHyVrqRcP1cnIpa6lqmW yE+bYHbU0qkN
SM/8+t4iolk8w/aIvUjpd0JD3pB4Mr1n+BU36nZj/AgAMbhofKbXaHQHv5kS bYnxwx0mmnZH
nw8iCT+5co4DHl/RGPlICI/eRpMCAwEAAaMtMCswCQYDVR0TBAIwADARBglg hkgBhvhCAQEE
BAMCBLAwCwYDVR0PBAQDAgTwMA0GCSqGSIb3DQEBBQUAA4IBAQBbE3e81vTI Qj0zKzbhMkf2
nAOxeokh8XclKjJV42qf/tR+yTS8TZDfQ+pGkYEzIQBLvem9PHfWf9//Bsmp 5waK2imzeODF
gis3ssVx5BhxWyIs1E2DXKBmLn6pZmpt1r7PFRSlFqwJQqtQRrghEK1FU8LI VetUB9AlyVZ/
hhUYjXqIdaq916S1ITVs2oMSCxtr7/YFxZ8OyTRTMdszsjkxUUQRsIY074I2 EzBkePFcpeee
2kxeK7cZ7XL044FCY4BL8AOrPysd8Jac8V+LRDszw4CCrK0zkGiaeixzSjUR 9Ft7LA4h/2t/
xg8Ai36iSWSJp5abd335Ja0M52IGwSvZMYIDHjCCAxoCAQEwWzBWMRIwEAYD VQQKEwlBS0Ug
R3JvdXAxCzAJBgNVBAYTAkZSMRIwEAYDVQQDEwlBS0UgR3JvdXAxHzAdBgkq hkiG9w0BCQEW
EGFrZUBha2Vncm91cC5vcmcCAUgwCQYFKw4DAhoFAKCCAZgwGAYJKoZIhvcN AQkDMQsGCSqG
SIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMTAwMzE5MjMxOTEwWjAjBgkqhkiG 9w0BCQQxFgQU
QZDnI9KqcfqdZI+NYnc5kKNL9TMwXwYJKoZIhvcNAQkPMVIwUDALBglghkgB ZQMEAQIwCgYI
KoZIhvcNAwcwDgYIKoZIhvcNAwICAgCAMA0GCCqGSIb3DQMCAgFAMAcGBSsO AwIHMA0GCCqG
SIb3DQMCAgEoMGoGCSsGAQQBgjcQBDFdMFswVjESMBAGA1UEChMJQUtFIEdy b3VwMQswCQYD
VQQGEwJGUjESMBAGA1UEAxMJQUtFIEdyb3VwMR8wHQYJKoZIhvcNAQkBFhBh a2VAYWtlZ3Jv
dXAub3JnAgFIMGwGCyqGSIb3DQEJEAILMV2gWzBWMRIwEAYDVQQKEwlBS0Ug R3JvdXAxCzAJ
BgNVBAYTAkZSMRIwEAYDVQQDEwlBS0UgR3JvdXAxHzAdBgkqhkiG9w0BCQEW EGFrZUBha2Vn
cm91cC5vcmcCAUgwDQYJKoZIhvcNAQEBBQAEggEAi7TroyzetUKfc36So25t 52mRCTnqTNj5
DBeFEng61dPAdcpMI0SWl72Z0vnIyBv+mOTSlacckPhEdFXzwqCAmt5cHwAB Z5HzLzkJnsYh
c6RwE1TIV138Cng+1IrUVFbAClkPyv2mvuZdllMpuvSfWCDXVniks39x+1BU ratEz/4ihtuX
8o1cwU5Jd+25U5vVqkPhf+psuAb8BGmJ1YWPMoTalnPxVPW03F9o6bzBpr7d dAms8slxuk+E
Eci4PH42B+b370hfLB1Ps4ckKP++MFNJU4PdDTmJhpFUc6POVcl8QZnHt9di luRsmIFlg1a1
3rtRAX+TztO4oonHiXM0GgAAAAAAAA==
--------------ms070003040605040403050409--

Re: Store database users in custom table

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

2010/3/19 Antoine POPINEAU

> 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

lass=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;=
padding-left:1ex;">


Thank you.



Antoine POPINEAU.