Just curious, would PostgreSQL be considered secure for applications involving
financial matters where the clients have a direct database logon?
First, to clarify, I'm not in a serious position to write such an application.
I'm just wondering. :-) If it is possible, I may make a proof of concept
application and document it on a public website. Kind of for fun, but also
as a learning experience.
It seems like it would be possible to be secure by writing functions defined
as SECURITY DEFINER, which would be owned by the postgres user. A client
could call this function, and it would try to do what the client asks. It
would then check that things are still OK and rollback the transaction if
not.
What I haven't figured out yet is, could anything prevent the client from
running BEGIN, then calling the function, then waiting around a long time
before running COMMIT? The concern is that they could wait until conditions
are different and then decide to commit or rollback, and/or hold locks that
would prevent other clients from doing what they want.
In other words, is it possible to do one of:
* A stored procedure detects that it has an enclosing transaction and
immediately abort if so, or
* Put some kind of transaction time limit for clients?
Maybe it's nuts to consider such a setup (and if you're talking a major bank
it probably is) ... and maybe not. At this point it's kind of a mental
exercise. :-)
Thanks,
Micah
