Re: A mechanism securing web applications in DBMS - Mailing list pgsql-hackers

Stephen,

> Yes- but that's pretty trivially done, given that you've stipulated that
> a single connection DB connection must be used from authentication until
> de-authentication.  All that is needed is an additional column in the
> auth table which is populated with a pseudo-random value which is
> guaranteed to be unique and constant for the duration of the
> authenticated time- and the database backend PID is perfect for that.
> The auth function can call the pg_backend_pid() function directly and
> then the policies can include a 'pid = pg_backend_pid()' as part of the
> join to the auth table.  The auth function can also complain loudly if
> an entry in the pid table is found with the current PID during auth (and
> similar- the de-auth function can complain if an entry with the current
> PID is *not* found).  This would eliminate the need for the on-connect
> triggers, I believe (though those are interesting for other reasons..).


You are right. Using unlogged table is a good idea. I'll try it out.
Thanks for your advice!

>  It'd be very interesting to see this done with the unlogged table,
> security definer functions, and the row-level policies patch which we're
> working on.  I'd further suggest that the application also use multiple
> roles which are set noinherit and 'set role' based on the operation
> which it's currently being used for- this would add another level of
> protection.  Using stored procedures (for more than just the auth and
> de-auth functions as suggested here) can also be a good idea.


Currently auth functions are security definer functions. I'm gonna try
to create a patch using unlogged table + RLS and put it online (e.g.
this mail list) so that people can try it.

Thanks,
Zhaomo

On Sat, Sep 13, 2014 at 4:00 PM, Stephen Frost <sfrost@snowman.net> wrote:
> Zhaomo,
>
> * Zhaomo Yang (zhy001@cs.ucsd.edu) wrote:
>> > Have you considered just using a regular, but unlogged, table?  That
>> > would also avoid any risk that the application manages to drop or shadow
>> > the temp table somehow with a "fake" table that changes who is currently
>> > authenticated, and avoids having to figure out how to deal with the temp
>> > table vanishing due to the connections going away.
>>
>> So then all the currently logged in users will be stored in the same
>> table, which means we also need to make sure that the correct row in
>> that table is used when the row-level security policy refers to the
>> current application-level user.
>
> Yes- but that's pretty trivially done, given that you've stipulated that
> a single connection DB connection must be used from authentication until
> de-authentication.  All that is needed is an additional column in the
> auth table which is populated with a pseudo-random value which is
> guaranteed to be unique and constant for the duration of the
> authenticated time- and the database backend PID is perfect for that.
> The auth function can call the pg_backend_pid() function directly and
> then the policies can include a 'pid = pg_backend_pid()' as part of the
> join to the auth table.  The auth function can also complain loudly if
> an entry in the pid table is found with the current PID during auth (and
> similar- the de-auth function can complain if an entry with the current
> PID is *not* found).  This would eliminate the need for the on-connect
> triggers, I believe (though those are interesting for other reasons..).
>
>> Let me send you a copy of our paper in a separate email which is a
>> thorough description of the mechanism (including background, threat
>> model, how it works, etc), which should give you an better idea on
>> every aspect of the mechanism. Please do not distribute it because it
>> has been accepted for publication. Note that the implementation we
>> show in the paper is just a prototype (we made the changes so that we
>> could implement it quickly). Our goal always is to integrate our
>> mechanism into open source DBMS's like PG and MySQL cleanly.
>
> It'd be very interesting to see this done with the unlogged table,
> security definer functions, and the row-level policies patch which we're
> working on.  I'd further suggest that the application also use multiple
> roles which are set noinherit and 'set role' based on the operation
> which it's currently being used for- this would add another level of
> protection.  Using stored procedures (for more than just the auth and
> de-auth functions as suggested here) can also be a good idea.
>
>         Thanks,
>
>                 Stephen