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