Re: Store database users in custom table

2010/3/19 Antoine POPINEAU <apognu(at)akegroup(dot)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.
>