Zhaomo, * Zhaomo Yang (zhy...@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
Description: Digital signature