Re: Using Postgresql as application server - Mailing list pgsql-general

On Wed, Aug 17, 2011 at 7:40 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

>
> GRANT/REVOKE only constrain read/write privileges to a database.
> Application level security is typically much finer grained than that.
> Also, I using SQL roles for actual user roles is not typically done
> for various reasons.  Generally, SQL roles are used to define 'what'
> is logging in, no necessarily 'who'.  If you allow and SQL through
> from the application then table level security becomes very
> important...otherwise not so much.

You can use roles to affect things on a fairly granular level, if you
combine a relation interface with a functional one.

And the fact most people use SQL roles this way is due to enforcing
security in the middleware.  The disadvantage is that the database has
to trust the middleware and the other clients connecting to it.  To
some extent this is unavoidable, but in general, reducing the level of
trust between the components reduces the security exposure.  Obviously
this has some issues in terms of how far it can scale.

One of the things we decided to do with LedgerSMB was to make every
application user a database user and then enforce security on the back
end.
>
>> What I am saying is that the further back you enforce the security the
>> more you can guarantee consistent enforcement across applications.
>> Connection pooling makes this much harder because you can't enforce it
>> within the db using the normal methods and end up having to implement
>> it all over.  Instead you have to implement security before the data
>> hits the database.  That's a big difference and it has HUGE
>> ramifications for security exposure vs utility of an application.
>
> That is totally incorrect.  pgbouncer maintains separate pools for
> each role and only intermingles queries for like roles.  Any
> intelligent connection pooler would do the same. You lose access to
> database session features but database level security features are
> still enforced.  Whether you connection pool or not really doesn't
> play into this from my point of view.

Right, but then you still can't enforce *user* permissions on the
database because there isn't a point in having a connection pool if
each user gets one as a db user, is there?

>
> Recall that in our hypothetical 'database as middleware' database, the
> main tables and there data are not actually in the database -- the
> only tables available to query would be session state, etc.  Most
> operations would funnel through back to the main database through
> procedures and application level security would be checked there.
> Now, if you want your system to be simple, tight, and fast, you could
> combine those two databases but would have to figure out how to manage
> security to a libpq speaking application.   Like I said, in my case I
> did this with a whitelist, but it's not the only way.

I guess I am approaching it differently as looking at logical tiers
getting incorporated into the RDBMS, which becomes the centerpiece of
and entrance point to the application server environment.  That's why
I am talking about the database taking over traditional middleware
functions rather than having a separate database......
>
>>> well, not exactly.  it is a concession to security.  allowing

> If you expose (as I did) your middleware api as a 100% sql function
> interface, then yes ad hoc sql is not allowed.   If you wrap your
> middleware with a http server than ad hoc sql would not be allowed.  I
> doubt the day will come where the browser will be sending ad hoc SQL
> queries directly to a database.

One of my LedgerSMB customers decided they wanted to be able to
distribute SQL scripts to bookkeepers and have them run them via
pgAdmin.  So from the browser?  No.  From other software clients?
Quite possibly.  What we were able to do was assign the specifically
needed functionality to the pgAdmin users and thus ensure that
security and data integrity were not compromised by this approach.
Now, the users in this case require a lot of read access, with a few
very specific write permissions.   The security footprint here is very
low.

We couldn't have accommodated that request safely, however, if our
permissions system wasn't geared around the db enforcing permissions
per user.
>
> The reason to use a database backend to handle middleware functions is
> based on the strength of the SQL language, supported by the various PL
> extensions you may want to include, to manage various everyday
> programming tasks.  The security discussion is a bit of a sideshow
> because it is generally a tiny fraction of the coding that typically
> happens at this level.  An individual's personal appraisal of this
> idea will largely depend on certain personal factors that will vary
> from developer to developer.  An unbiased analysis would probably
> conclude that it is an interesting, but unproven approach with a lot
> of potential.

It's a tiny piece of the code, but it's a critical one, and when
something goes wrong.....

Here's my approach generally.

1) client apps (middleware and web apps become client apps in this
approach) log in to the db with user-supplied credentials.  The
database enforces permissions on a table, view, and function level.
Some operations are controlled at the table level.  Some operations on
a function level.  As we start requiring newer versions of PostgreSQL,
it is more and more likely that function-level permissions will go
away.  Permissions are granted to roles which are granted to users.

2)  All new code in LedgerSMB moves through a discoverable stored
procedure interface, so the actual web app is a thin Perl glue between
a user interface template and stored procedures.  The interface
template can be designed around stored procedure inputs and outputs,
and the glue itself has minimal knowledge of these things.  This has
some costs in terms of documentation but the SQL API is
self-documenting to the extent possible.

3)  PostgreSQL in this structure mostly accepts function calls and
retrieves/stores data, always returning a meaningful tuple to the
extent possible.  So if we select * from post_transaction(.....) we
would get the header information (including the id from the
transaction) back to the application.

4)  In some cases data may be queued in queue tables for further
processing.  NOTIFY is raised on commit (via triggers) to listening
processes, which can then process the data and further store it
somewhere as needed.  Currently there are no uses of this in LedgerSMB
out of the box, typically because this addresses needs of specific
customers and not the general application.

There are of course limits to all of these approaches.  If I were to
set up a shopping cart against such a database, I would probably use
one database role for all customers and enforce authentication on the
web server.  Authorization would still be via the db though.

>
>>> also I think most people would not go the libpq route even though
>>> listen/notify allows you to create beautifully interactive systems --
>>> mainly due to weirdness of the whole thing and the amount of work you
>>> have to do to get it safe.
>>
>> Why?  Each listener has to have its own connection, right?  Otherwise
>> there is nowhere to send the notifications to.  That connection has to
>> be attached to a db role.  The DB role has to have permission to some
>> portions of the database but not others, right?  I don't see why that
>> is hard to make safe.
>
> It's hard to make safe because allowing applications to speak libpq
> means you have to be worried about various nasty things untrusted SQL
> can do to your database server.  In the end, I think the only
> reasonable way to do this is a whitelist.

Two points:

1)  yes, coming up with proper permissions for a reasonably complex
database is a lot of work.  No question about it.  But if you enforce
these by the back-end, then allowing ad-hoc SQL isn't the end of the
world.

For example, suppose we have two tables:  journal (the accounting
general journal header info per entry) and journal_line (line items).
We might want to require that all transactions that get stored are
balanced, and that is something a relational interface is not good at.

So we might allow select privileges to both these tables based on
role, but only allow insert to the table owner, with access to a
security definer function to store transactions.  One could further
allow update on columns indicating transaction status to some
roles.....  These roles could all be granted to relevant users.

2)  Even if the above isn't the direction one wants to go, your
objection doesn't address the use case I am addressing which are
automated processes which monitor data queues and process data when
transactions commit data to those queues.  In those cases, you are
talking about applications which need remarkably limited access to the
database in the vast majority of cases, so creating a role with
appropriate access for those applications doesn't unduly increase the
security profile of the application.  I don't see why these processes
at any rate would go through a connection pooler since, well, they
need a constantly open connection.

Best Wishes,
Chris Travers