Re: Novice question about users and...rights?

Dale,

* Dale Schmitz (dschmitz4@cox.net) wrote:
> My big question for this post regards database login by end users.  In the
> Access database, users logged in as a way of verifying to the database super
> user that they were legitimate users of the database, but everything ran as
> the DB owner (the super user I mentioned).  In converting this application
> I'm tempted to go the same route, but only because I saw that it worked in
> Access.  The users will have flags in their records stating their role, and
> hence what they can and can't do, but I wonder if this is smart.

First off, be careful when you talk about 'superuser' as it means
different things in different places.  Definitely do *not* use a
PostgreSQL superuser account for your application.

> Would it be smarter/wiser/more secure/more efficient, etc., to have the
> users actually log in to Postgres and assign them appropriate permissions to
> tables and such, as opposed to having the database owner run everything with
> knowledge of what the user is authorized to do?

For a small number of users, such as what it sounds like you have here,
I'd suggest having a database-level user for each user of the
application and managing privileges using roles, where access on each of
the tables is GRANT'd to some appropriate role and then those roles are
GRANT'd to the users who should have a given role (or roles).

This also means you can essentially avoid having the application have to
care about authentication or authorization, since it's all done in the
database and the user actually authenticates to the database and not to
the application.  This also has the advantage that if you have other
applications in the future you don't have to reimplement the
authentication or authorization pieces, and you could even give advanced
users access to the database itself if you wish to (note, however, that
PostgreSQL doesn't currently have any way to 'throttle' users who have
SQL-level access, so one user could potentially use a lot of resources
with direct SQL access, though this may also be true when accessing the
system through your application, so it might not be different, really).

Thanks!

Stephen