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

I'm converting a Microsoft Access database to PostgreSQL and writing all the HTML and necessary server routines to migrate the database to web technologies. It works fine on a stand-alone system, but we want to network it now. I have some experience with databases, having written some simple Access applications, but I'm by no means an expert. I've had classes on database design and am familiar with the basic concepts of organization, normalization, entity relationships, and the like, but my immediate needs are a bit more detailed. 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. 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? Thanks in advance, Dale

Dale Schmitz 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.

The best setup is probably like this:

Don't have a database user for each application user, but use
one database user for the application to connect to the database.

That database user ideally should not own the tables, but only have
the permissions necessary to run the application.
That reduces the damage that can be done if an attacker breaks into
your application with SQL injection or otherwise.

Manage your application users in a database table.

If you have different kinds of application users with different sets
of permissions (e.g. some that may only read, but not modify the data)
you could use more than one application user with the appropriate
permissions and reconnect as that user after you have authenticated the
application user.

Yours,
Laurenz Albe

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

Laurenz,

* Laurenz Albe (laurenz.albe@cybertec.at) wrote:
> Dale Schmitz 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.
>
> The best setup is probably like this:

I wouldn't suggest this as being the 'best' setup- that really depends
on the user, the applications, environment, et al.

> Don't have a database user for each application user, but use
> one database user for the application to connect to the database.

This makes the application have to handle all of the authentication and
authorization for the user, which certainly requires not only more code
in the application but may also be more complex.

Of course, if the authorization requirements aren't able to be expressed
through SQL GRANT and POLICY commands, or VIEWs, TRIGGERs, et al, then
one might have to implement the authorization in the application.  One
might wish to consider if such a complicated authorization setup is a
good approach though since that's one of those things that can be tricky
to get correct in all of the various corner cases.

> That database user ideally should not own the tables, but only have
> the permissions necessary to run the application.

I agree that, generally speaking, you don't want your application user,
or end users, to be able to create tables unless you've specifically
thought about that and considered if it's a good thing or not.  If you
decide to allow them to create tables (which can be quite handy in some
cases), I'd suggest you consider user schemas to keep the users
segregated from each other, and be sure to REVOKE the default CREATE
rights on the public schema from PUBLIC.

> That reduces the damage that can be done if an attacker breaks into
> your application with SQL injection or otherwise.

This is further reduced if the application doesn't handle the
authentication or authorization but instead the database does.

> Manage your application users in a database table.

The database can handle this for you, and users would end up in the
system catalog tables.

> If you have different kinds of application users with different sets
> of permissions (e.g. some that may only read, but not modify the data)
> you could use more than one application user with the appropriate
> permissions and reconnect as that user after you have authenticated the
> application user.

This is handled much more cleanly by having end user accounts in the
database itself and provides much more flexibility than having to create
an application-level role for every combination of privileges and then
making sure the application uses the right role at the right time.

Thanks!

Stephen

Stephen Frost wrote:
> > Don't have a database user for each application user, but use
> > one database user for the application to connect to the database.
> 
> This makes the application have to handle all of the authentication and
> authorization for the user, which certainly requires not only more code
> in the application but may also be more complex.

True, if you give administrative application users the CREATEROLE privilege,
you can map database users to application users and have the database handle
application user management.

It is something I do not see often in the wild, but that does not mean
it is a bad thing (unless you want the application to work with different DBMS).

Yours,
Laurenz Albe

Laurenz,

* Laurenz Albe (laurenz.albe@cybertec.at) wrote:
> Stephen Frost wrote:
> > > Don't have a database user for each application user, but use
> > > one database user for the application to connect to the database.
> >
> > This makes the application have to handle all of the authentication and
> > authorization for the user, which certainly requires not only more code
> > in the application but may also be more complex.
>
> True, if you give administrative application users the CREATEROLE privilege,
> you can map database users to application users and have the database handle
> application user management.

Yes, you could do that, but it really depends on the environment as to
if that makes sense.  Not all systems should have self-subscription
capability; in many environments a user gets access to various resources
as part of 'on-boarding' at a company or similar and that sounds like
what would be appropriate here, where you'd actually have an admin or
another system (puppet, chef, et al) that would create the account.

Also, to be clear, the CREATEROLE privilege is more like 'create and
modify' roles and isn't something to be given out lightly.

> It is something I do not see often in the wild, but that does not mean
> it is a bad thing (unless you want the application to work with different DBMS).

This approach is something used much more frequently for internal
applications than for things like public websites.

Thanks!

Stephen