Re: Application & Authentication

When I started writing Hermes (http://hermesweb.sourceforge.net) I was
faced with this problem as well. I wanted to support both MySQL (because it
is widely supported) but provide a flexible way of supporting some of the
more advanced features of PostgreSQL. You might find my project refreshing
if you are looking for something using native database accounts, but it is
not a small program. I settled on doing the following:

1: A pluggable authenticaiton module system so that I could authenticate
using database native accounts without worrying that maybe someday I would
be limited to one account and have to "make do."

2: A full database abstraction model which reduces most of the operations
to a (mostly) ANSI 92 complient database.

3: Permissions administration abstraction layer which I could use to wrap
the fact that MySQL does not support roles or groups (what a horrid hack I
used for MySQL ;)).

Here are the issues I think most people are facing with these applications:
1: Most hosted solutions do not offer a dedicated instance of the database
manager so that user accounts are global and the subscriber cannot create
them.
2: No easy way for users to change their passwords without being able to
change anyone else's (if the application can be bypassed). A stored
procedure in PostgreSQL should solve this issue.
3: Supporting multiple database backends with different user permissions
systems can result in both QA problems and much extra work.

But here is what database native accounts give you:
1: Enforcing the permissions as far "back" as possible so that they cannot
be bypassed by a simple application exploit.
2: More extensible user account management.
3: If properly managed, credential separation is possible so that even a
web server compromise is not sufficient alone to access the database.

Anyway, my $0.02,
Chris Travers

----- Original Message -----
From: "Sean Mullen" <smullen(at)optusnet(dot)com(dot)au>
To: "'pg_general'" <pgsql-general(at)postgresql(dot)org>
Sent: Thursday, July 17, 2003 11:58 PM
Subject: [GENERAL] Application & Authentication

> Hi,
>
> I'm a bit concerned about a 'conceptual' issue.
>
> I'm working on a web based app and am using postgresql
> users/groups for authentication, and using functions
> /rules/views to ensure users only access/modify their own data.
>
> i.e. many references to 'current_user'
>
> However, in my travels around sf.net, freshmeat etc I haven't come
> across any projects built like mine which is concerning to me.
>
> Other projects I've seen use their app for authentication/security
> and bypass/ignore the extremely 'useful' security system built into
> postgresql and build their own security/authentication system.
>
> I'm wondering if the reason for this is:
>
> A) Necessity.
> i.e. Their project frontends run on a mysql backend - and has
> to do 'everything'
>
> OR
>
> B) There is some horrible limitation that is going to ruin my day down
> the track
>
> Thanks for any comments,
>
> Sean
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>