Re: Partial authentication (was Re: sefety of passwords for web-service applications)

On Sat, Nov 24, 2012 at 3:37 AM, Chris Angelico <rosuav(at)gmail(dot)com> wrote:

> On Sat, Nov 24, 2012 at 8:41 PM, Chris Travers <chris(dot)travers(at)gmail(dot)com>
> wrote:
> > 2) PostgreSQL allows you to move this authentication to a secondary
> service
> > like Kerberos, LDAP, or anything PAM supported. This means that if you
> want
> > to you can use a dedicated password store for the passwords which is not
> > accessible inside your database at all.
>
> Drawing a side point from this comment.
>
> We have some pieces of information that are global and public (basic
> configs and stuff), some that are private to one particular client's
> login, and some that are admin-only. Ideally, I'd like to have them
> all stored in one PG database, because some of them interact (eg
> there'll be joins involving the current client's info in table X and
> the public info in table Y). Currently, we're doing the same as the
> OP, with application-defined security based on a table of hashed
> passwords. Is there a way to arrange security such that this can be
> done efficiently? There are quite a few cases when public information
> is needed and a client's login isn't yet available, and I'd rather not
> have to connect using a public-only login, then disconnect and
> reconnect when we have the user's credentials.
>

What we do in LedgerSMB is to have a table that links users to records that
store personal data, and another table that stores user preferences.
Everything is there except for the password hashes, which we don't manage.
These can be pushed out to a dedicated password store at the cost or
duplicating the usernames but our app doesn't have to know this. I can't
imagine under what circumstance you;d have to report on passwords, so I
think that approach should work just fine.

In the future, we will probably move some of the material relating to user
preferences into a view with a security barrier, something like:

CREATE VIEW my_preferences AS select * FROM user_preference WHERE id =
(select id from users where username = SESSION_USER);

Then permissions, and update triggers, can be assigned on this view and a
security barrier could be attached to it while those who need to see the
whole set could do so.

Best Wishes,
Chris Travers

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