On 2/24/15 3:31 PM, Stephen Frost wrote: > * David Steele (david@xxxxxxxxxxxxx) wrote: >> On 2/24/15 3:07 PM, Stephen Frost wrote: >>> The problem with a temporary table is, well, it goes away. :) There are >>> further concerns that, because it's created in some fashion by the >>> single application user, it might be less secure. Really, though, I'd >>> want it to be real so that it could have constraints be on it which >>> reference other appropriate tables, so the web user doesn't have to have >>> rights in any fashion to create objects, and so that it can be >>> referenced from RLS policies. A table as transient as a temporary table >>> doesn't strike me as the right solution for that. >> >> Temp tables go away at the end of the session, sure. It seems like >> exactly the time when you'd want them to do so. >> >> If the temp table is created by a security definer function (as was >> suggested earlier) then no special user privs are required. >> >> Being referenced from RLS polices is a good argument, though. I guess >> that's not possible with a temp table? Are they pre-parsed? > > Actually, it is possible, but it creates a dependency on the temporary > table and when the temporary table goes away, so will the policy. This > isn't a huge issue for RLS, of course, as if there's no policy then the > default-deny policy will kick in, but you can't have a policy against a > temporary table continue past the end of that session. It's not good if policies go away, even with a default. > Following the earlier discussion, I suppose you could create both the > temporary table and the policies in the security definer function, but > it feels a lot cleaner to have a real table for all of that, in my view, > to keep that security definer function nice and simple. Then you'd have a policy for each user which sounds messy, or some crazy locking scheme which sounds worse. I agree that a real table sounds like a better solution. > Further, there's lots of other reasons to have a session table anyway, > from an application standpoint, and so this feels like an approach which > is more in-line with how the application likely wants to operate anyway. > It's also handy to be able to log into the database and see all the > current sessions, similar to how we have pg_stat_activity. Well, I am a fan of monitoring. So I guess my last question is if you are inserting rows into a table to track user connections, how do you clean them out when the client does not disconnect cleanly? Or is this table intended to be append-only? -- - David Steele david@xxxxxxxxxxxxx
Attachment:
signature.asc
Description: OpenPGP digital signature
