Thread: Advice needed on application/database authentication/authorization/auditing model

I have a web application with a Postgres backend.  In my initial
prototype, I decided not to have a Postgres database user created for
each application user, opting instead to use my own users table.
Authentication of application users is done via PAM, so no password is
necessary in my users table -- I just let PAM do its thing, and if the
user is authenticated, I check for a record in my application's users
table to see if they're authorized to use the app, along with what
privileges they have, e.g.:

CREATE TYPE USER_ROLE AS ENUM ('User', 'Auditor', 'Administrator');

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT UNIQUE NOT NULL,
    displayname TEXT NOT NULL,
    role USER_ROLE NOT NULL DEFAULT 'User'
);

Now that this is moving beyond a prototype stage, I need to tighten up
the authentication/authorization/access control model.  In particular, I
need to add some basic audit trail functionality.  I found a couple of
projects that help make auditing easy (tablelog and EMaj being the most
promising) but they both rely on the database users mapping 1:1 to
application users, which is currently not the case -- right now I've
only got one database user that's used for all of the pooled
connections, so the audit logs are showing that user instead of my
application user.

So, I'm wondering what others have done in similar situations.  It seems
to me like the database connection pooling means I probably need the
connections to be made with a privileged "database superuser" account
that has permission to "SET ROLE" to each and every application user,
then "RESET ROLE" when it's done.  That's a bit of a pain, but doable

Then there's the issue of application roles vs. database roles.  I
wanted to have three roles in this application:  regular users, auditors
(who can do everything regular users can, plus access audit tables to
view audit log tables and potentially restore data from them) and
administrators (who can do everything in the application, but shouldn't
be Postgres superusers.)  Unfortunately, I can't figure out a clever way
to do this mapping, especially because Postgres doesn't allow users to
refer to system tables like pg_authid to do an explicit mapping of app
roles to database roles.

So, does anyone have any suggestions here?  I feel like there's got to
be a way to do this, but I can't find anything relevant in the list
archives.

Thanks.
-Tony

Hi Dmitriy,

On 10/21/10 4:21 PM, Dmitriy Igrishin wrote:
> IMO, you are trying to reinvent the wheel. Although, you may do it just for
> fun. :-)

Surely I am, but I think it made sense at the time.  It doesn't make as
much sense now that I need to audit every insert/update/delete in the
database.

> Why not just create "groups" via CREATE ROLE User ... and grants this
> roles to the "users" (created via CREATE USER or CREATE ROLE ... LOGIN)Â  ?

The reason I shied away from this initially was the overhead of having
to maintain user info in two places (the pg_catalog schema for postgres
users, and in my application schema, with the user's real name,
application preferences, etc.)  It also seemed like the role information
wasn't very accessible in the system catalogs -- I had noticed that the
pg_group view was deprecated, and the query to get group information out
of the pg_auth_members and pg_roles tables started to look very ugly,
when I could just do a quick "is the user an administrator" check via a
boolean flag in my app user's table.

With my new requirements for auditing, using the database's roles makes
more sense, but I still see some problems with it, even if I can solve
the connection pooling problem by using persistent connections as you
suggest.

For one thing, in this app, all higher permissions include the lower
permissions -- all administrators are auditors and regular users, and
all auditors are regular users.  So, my normal instinct would be to set
it up like this:

GRANT g_user TO g_auditor WITH ADMIN OPTION;
GRANT g_auditor TO g_admin WITH ADMIN OPTION;

Then, in theory, I could grant administrators the g_admin group,
auditors the g_auditor group, etc. and they could do all the things the
lower groups can.  BUT, in my app, to check for access to audit
functions, I can't do a simple query to see if the user is in the
"g_auditor" group, because administrators aren't explicitly granted this
group -- they get those permissions implicitly, but how do I know this
from my application?  Is there some kind of query I can do to get back
all the groups a role is a member of?

On Fri, Oct 22, 2010 at 12:21:17AM +0400, Dmitriy Igrishin wrote:
> Hey Tony,
>
> 2010/10/21 Tony Cebzanov <tonyceb@andrew.cmu.edu>
>
> > I have a web application with a Postgres backend.  In my initial
> > prototype, I decided not to have a Postgres database user created for
> > each application user, opting instead to use my own users table.
> >
> IMO, you are trying to reinvent the wheel. Although, you may do it just for
> fun. :-)

This is an interesting statement and I've wondered about this a couple
of times before.  It seems very common practice (at least with web
applications) to access a database from one user and do all
authentication and authorization checking in the application.

Is this a bad idea?  At first glance, it would indeed seem very
logical to make an application user match a database user, but how do
you do row-level permission checking?

For instance, very often I've had the situation where a web app stores
users, companies and <whatever>s.  The whatevers can be accessed only
to either the user who created them, or when they are either company-wide
assets or the user has company-wide privileges, can be accessed only
when the whatever was made by a user from the same company.

As far as I can see, this would imply either creating views on the
<whatever> for every user (or company?), or manually crafting queries
to do the same. The latter is of course what most webapps do, and it is
a frequent cause of errors and, hence, vulnerabilities.

I'd love to hear some good advice on what has worked well for others.

Cheers,
Peter
--
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
                            -- Donald Knuth

On Fri, Oct 22, 2010 at 08:20:11PM +0400, Dmitriy Igrishin wrote:
> Hey Peter,

Hello Dmitriy,

> > As far as I can see, this would imply either creating views on the
> > <whatever> for every user (or company?), or manually crafting queries
> > to do the same. The latter is of course what most webapps do, and it is
> > a frequent cause of errors and, hence, vulnerabilities.
> >
> Yes, liberal use of views and rules are the best solutions in this case IMO.

Do you know of an open source application that does that so I can see
it in practice?

I'd like to learn how it's done in practice because right now it seems
to me that this would be rather complicated to manage.

Cheers,
Peter
--
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
                            -- Donald Knuth

On 10/23/2010 12:02 AM, Dmitriy Igrishin wrote:

> Please see
> http://www.postgresql.org/docs/9.0/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE

Yep. As for not explicitly mentioning "lower" roles when granting a
higher role (ie "admin" isn't also a "user") - role inheritance.

http://www.postgresql.org/docs/current/interactive/role-membership.html

--
Craig Ringer

In article <20101022161331.GD9770@frohike.homeunix.org>,
Peter Bex <Peter.Bex@xs4all.nl> writes:

> As far as I can see, this would imply either creating views on the
> <whatever> for every user (or company?), or manually crafting queries
> to do the same.

Not necessarily. Consider this:

  CREATE TABLE t1 (
    id serial NOT NULL,
    val int NOT NULL,
    usr text NOT NULL,
    PRIMARY KEY (id)
  );

  COPY t1 (val, usr) FROM stdin;
  1    u1
  2    u1
  3    u2
  4    u3
  \.

  CREATE VIEW t1v (id, val) AS
  SELECT id, val
  FROM t1
  WHERE usr = current_user;

Now user "u1" can only see rows 1 and 2.

On 10/23/10 11:01 AM, Craig Ringer wrote:
> Yep. As for not explicitly mentioning "lower" roles when granting a
> higher role (ie "admin" isn't also a "user") - role inheritance.

I knew about role inheritance, I just didn't know about the
pg_has_role() function for determining if a user has a role.  That's
helpful, but I really don't want to be hitting the database with a
pg_has_role() call for every time I want to check if a user should have
access to a certain page or function in my application.

Normally, when the user logs in, I'd cache their user info, and any
roles they have, either directly or indirectly.  But how can I do this
if I'm not directly making administrators members of the other groups
they inherit the rights of?  In other words, is there a convenience
function or view I can use to get a list of all roles the user has
access to, both directly or indirectly?

On 27/10/10 04:49, Dmitriy Igrishin wrote:
> Hey Tony,
>
> 2010/10/27 Tony Cebzanov <tonyceb@andrew.cmu.edu
> <mailto:tonyceb@andrew.cmu.edu>>
>
>     On 10/23/10 11:01 AM, Craig Ringer wrote:
>     > Yep. As for not explicitly mentioning "lower" roles when granting a
>     > higher role (ie "admin" isn't also a "user") - role inheritance.
>
>     I knew about role inheritance, I just didn't know about the
>     pg_has_role() function for determining if a user has a role.  That's
>     helpful, but I really don't want to be hitting the database with a
>     pg_has_role() call for every time I want to check if a user should have
>     access to a certain page or function in my application.
>
> Why not? Performance? It's just one function call.

It's potentially a fair bit more than that. It requires a new connection
(tcp connection, backend startup, auth, etc) or borrowing one from a
pool. If the  pool is server side there's still a tcp connection with
the associated latency. Then there's a round trip for the query and
result. Processing the result. etc. It's not trivial, especially if your
client and server aren't co-located.

Like you, I'd suggest using information_schema for the job.

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/