Application user login/management

[Michael Glaesemann]

Hello all,

Recently I've been thinking about different methods of managing users
that log into a PostgreSQL-backed application. The users I'm thinking
of are not necessarily DBAs: they're application users that really
shouldn't even be aware that they are being served by the world's most
advanced open source database server. I appreciate any thoughts or
feedback people may have, as I'm trying to decide which is the most
appropriate way to move forward.

Method 1: Use PostgreSQL users and groups.
All application users will (unknowingly) be PostgreSQL users as well.
Restrict access for these users to prevent them from logging into the
PostgreSQL server directly, and limit their access to the DB using the
built-in PostgreSQL access privilege mechanism. Updates occur through
functions; selects are against views or using set returning functions.
This method leverages built-in functionality. Drawbacks I see are that
PostgreSQL users are unique to a cluster, rather that the db. This
means that once a user exists in one db, they exist in all of the dbs.
There might be users with the same name in other dbs, so that name is
no longer available (though of course this can also occur in a single
db as well). Also, it may be desirable to let usernames be retired for
one person, but the user is not deleted, for example if their data is
still required even though they are no longer active. One might want to
allow a new user to be able to use this username, i.e., active
usernames would be unique, rather than usernames in general.

Method 2: Store username/password information as data in tables, using
pgcrypto for authentication
In this scenario, middleware passes username/password combinations to
PostgreSQL and functions within the database use contrib/pgcrypto to
handle authentication. This allows a username to be 'retired' for one
person and assigned to another. Another advantage is that using
PostgreSQL functions for authentication mean that this doesn't need to
be duplicated in middleware. A possible disadvantage is that it
requires pgcrypto, though I don't know how much of a disadvantage this
is, as it is a contrib library that ships with the standard PostgreSQL
package.

Method 3: Store username/password information as data in tables, and
use middleware for authentication
This seems to be the most popular method from what I've seen of open
source packages. One reason for this may be that the middleware is
designed to work with a number of different dbms backends, and
different dbms' have different capabilities with respect to user
management: it's just easier to take care of it in the middleware.

I lean towards the first and second methods, as I like to keep as much
in the server as possible, and portability wrt the database server
isn't as important to me as being able to develop different middleware
against the same data.

Another thing on my mind is security. Any thoughts on the relative
security of the three methods I've outlined above?

Thank you for any and all thoughts on this. I appreciate hearing
other's views.

Regards,

Michael
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Jason Sheets]

Michael Glaesemann wrote:

Hello all,

Recently I've been thinking about different methods of managing users
that log into a PostgreSQL-backed application. The users I'm thinking
of are not necessarily DBAs: they're application users that really
shouldn't even be aware that they are being served by the world's most
advanced open source database server. I appreciate any thoughts or
feedback people may have, as I'm trying to decide which is the most
appropriate way to move forward.

Method 1: Use PostgreSQL users and groups.
All application users will (unknowingly) be PostgreSQL users as well.
Restrict access for these users to prevent them from logging into the
PostgreSQL server directly, and limit their access to the DB using the
built-in PostgreSQL access privilege mechanism. Updates occur through
functions; selects are against views or using set returning functions.
This method leverages built-in functionality. Drawbacks I see are that
PostgreSQL users are unique to a cluster, rather that the db. This
means that once a user exists in one db, they exist in all of the dbs.
There might be users with the same name in other dbs, so that name is
no longer available (though of course this can also occur in a single
db as well). Also, it may be desirable to let usernames be retired for
one person, but the user is not deleted, for example if their data is
still required even though they are no longer active. One might want
to allow a new user to be able to use this username, i.e., active
usernames would be unique, rather than usernames in general.
I've seen this method used successfully in some applications, I prefer
to avoid using it as you must also create a PostgreSQL user for each
application user. Instead I use either method 2 or 3 for user
authentication and then use method 1 to restrict the middleware's access
to the database itself (don't give the application more access than it
requires).

Method 2: Store username/password information as data in tables, using
pgcrypto for authentication
In this scenario, middleware passes username/password combinations to
PostgreSQL and functions within the database use contrib/pgcrypto to
handle authentication. This allows a username to be 'retired' for one
person and assigned to another. Another advantage is that using
PostgreSQL functions for authentication mean that this doesn't need to
be duplicated in middleware. A possible disadvantage is that it
requires pgcrypto, though I don't know how much of a disadvantage this
is, as it is a contrib library that ships with the standard PostgreSQL
package.
If you are confident that (a.) you will either run the database server
or (b.) have the authority to require that pgcrypto be installed on the
database for all installations this may be a good solution. Keep in
mind you are limited to the encryption types supported by pgcrypto and
moving to another database solution may be difficult. I also can't
comment on the availability of pgcrypto on Win32 but with PostgreSQL 8
just around the corner the desire might be there to run the DB on
Windows at some point. libmcrypt is currently available in win32 but
I've occasionally seen behavior differences with it on win32 v.s. Unix.

Also keep in mind that if you are not using encrypted database
connections (using PostgreSQL's built in SSL support or SSH tunneling or
another technique) you may be sending user's passwords across the
network in plain text for the database to use. I would either insure
that all connections will be encrypted or preferably at hash the
password with at least SHA-1 on the application side and pass that as
the password to the back-end, SHA-1 is available in almost all languages
these days; this technique may also remove the requirement of using
pgcrypto on the back-end.

If you are going to use multiple interfaces to the application this may
be the best choice as you don't have to re-implement the security system
for each client application.
Method 3: Store username/password information as data in tables, and
use middleware for authentication
This seems to be the most popular method from what I've seen of open
source packages. One reason for this may be that the middleware is
designed to work with a number of different dbms backends, and
different dbms' have different capabilities with respect to user
management: it's just easier to take care of it in the middleware.

I lean towards the first and second methods, as I like to keep as much
in the server as possible, and portability wrt the database server
isn't as important to me as being able to develop different middleware
against the same data.
This is the technique I've used pretty often, it gives me very powerful
application integration and allows me to more easily support different
back-ends if the customer so chooses (I currently go with PostgreSQL and
SQLite). The biggest drawback you've already touched is the system is
implemented in the middleware so other interfaces to the application
must also implement the security system.
Another thing on my mind is security. Any thoughts on the relative
security of the three methods I've outlined above?
Don't store passwords in plain text, don't pass anything sensitive over
the wire unencrypted (preferably nothing at all but sometimes that isn't
feasible). Like I mentioned above, I don't see method one as an
application security method but rather as a way to augment the security
of methods two or three. YMMV and some good planning now might help you
avoid re-implementing your system later.
Thank you for any and all thoughts on this. I appreciate hearing
other's views.

Regards,

Michael
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Scott Marlowe]

On Sun, 2004-10-03 at 22:23, Michael Glaesemann wrote:

Hello all,

Recently I've been thinking about different methods of managing users
that log into a PostgreSQL-backed application. The users I'm thinking
of are not necessarily DBAs: they're application users that really
shouldn't even be aware that they are being served by the world's most
advanced open source database server. I appreciate any thoughts or
feedback people may have, as I'm trying to decide which is the most
appropriate way to move forward.

The method I worked with was similar to your method 3, of maintaining
the info in tables, but more complex, and easier to handle for large
numbers of users.

We built an OpenLDAP server and wrote some scripts to maintain that and
allow for group editing. This structure existed completely outside of
the either the database or application. Then, apache handled all the
authentication through ldap authentication. The application was give
standard libs / includes that allowed for pushing a username up against
any group, etc... So that all the yes / no of being allowed somewhere or
allowed to do something was kept in the LDAP database.

This allowed us to allow owners of given groups to edit them by
themselves, i.e. the Director of Marketing could both add other junior
admins to the marketing groups, and could edit members of all the
marketing groups. Note that these groups / authentication are then
accessible to all other applications in the company that are LDAP
aware. And there's a lot of stuff that can work with LDAP and / or
apache/http auth against LDAP authentication.

This allows you to scale your authentication and group management
independently of any scaling issues with your application servers.
Since single master / multi slave OpenLDAP is a pretty easy thing to
implement, the only applications that need to access the master can be
set to the ldap editing applications (group editor, update scripts,
etc...) while standard old authentication can be pointed at one or more
slaves.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Michael Glaesemann]

Thank you, both Scott and Jason, for your responses. You both brought
up things I hadn't thought about. I've included snippets of their posts
below.

On Sun, 2004-10-03 at 22:23, Michael Glaesemann wrote:

Recently I've been thinking about different methods of managing users
that log into a PostgreSQL-backed application. The users I'm thinking
of are not necessarily DBAs: they're application users that really
shouldn't even be aware that they are being served by the world's most
advanced open source database server.

On Oct 4, 2004, at 1:48 PM, Scott Marlowe wrote:

We built an OpenLDAP server and wrote some scripts to maintain that and
allow for group editing. This structure existed completely outside of
the either the database or application. Then, apache handled all the
authentication through ldap authentication. <snip /> This allows you to scale your authentication and group management
independently of any scaling issues with your application servers.
Since single master / multi slave OpenLDAP is a pretty easy thing to
implement, the only applications that need to access the master can be
set to the ldap editing applications (group editor, update scripts,
etc...) while standard old authentication can be pointed at one or more
slaves.
Method 2: Store username/password information as data in tables,
using pgcrypto for authentication

On Oct 4, 2004, at 1:53 PM, Jason Sheets wrote:
If you are confident that (a.) you will either run the database server
or (b.) have the authority to require that pgcrypto be installed on
the database for all installations this may be a good solution. Keep
in mind you are limited to the encryption types supported by pgcrypto
and moving to another database solution may be difficult. I also
can't comment on the availability of pgcrypto on Win32 but with
PostgreSQL 8 just around the corner the desire might be there to run
the DB on Windows at some point. libmcrypt is currently available in
win32 but I've occasionally seen behavior differences with it on win32
v.s. Unix.

Also keep in mind that if you are not using encrypted database
connections (using PostgreSQL's built in SSL support or SSH tunneling
or another technique) you may be sending user's passwords across the
network in plain text for the database to use. I would either insure
that all connections will be encrypted or preferably at hash the
password with at least SHA-1 on the application side and pass that as
the password to the back-end, SHA-1 is available in almost all
languages these days; this technique may also remove the requirement
of using pgcrypto on the back-end.

As with many things, there are tradeoffs. As I'm going to be running
the database server, I think I'm going to push the pgcrypto solution as
far as it will go. Thanks again, to both of you, for your comments.
Much appreciated!

Michael
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings