On Sat, Nov 24, 2012 at 3:37 AM, Chris Angelico <rosuav@xxxxxxxxx> wrote:
On Sat, Nov 24, 2012 at 8:41 PM, Chris Travers <chris.travers@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
