Thread: PG secure for financial applications ...
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
Micah Yoder wrote: > Just curious, would PostgreSQL be considered secure for applications involving > financial matters where the clients have a direct database logon? I'd say that an application where clients have a database login and can perform arbitrary SQL statements is not very robust and secure, but all that depends on the definition of security. You can securely restrict which data a user can access and manipulate - with views and, as you envision, functions. But, as you suspect, a database user can always hog resources that other users would need, thus denying them service. This is normally seen as a security problem. > 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. You cannot manage transactions inside functions. A function always runs inside a single transaction. > 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. No. A user logged into the database can always do that. There is no way in PostgreSQL to limit the length of a transaction. Some aspects of security will probably have to be handled by the application. It will determine which statements a user can run against the database. Even a few ill-constructed SELECT statements can generate enough load on a database to affect other users. Yours, Laurenz Albe
Micah Yoder wrote: > 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 > My $0.02 - if you're trying to be pragmatic about it, your starting point should be whatever audit regulations govern your definition of "financial matters", and how well-worn the path is to compliance on Postgres. Some audit regulations range from dubious to absurd, but they are still going to be what you have to answer to in the financial world. There are areas where Postgres will have difficulties, at least against the regs I've worked with, but IMHO these areas have little to do with real security. Paul
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Fri, 14 Mar 2008 02:00:39 -0600 Micah Yoder <micah@yoderdev.com> wrote: > 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. :-) If you don't have enough control over the application to handle that type of situation, no database is going to serve your purposes. Beyond that, PostgreSQL is one of the most flexible database systems around when it comes to security and my company professionally supports several financial firms using PostgreSQL as their core database. Sincerely, Joshia D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL political pundit | Mocker of Dolphins -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH2n4JATb/zqfZUUQRApakAJ9oJFFVEji72W71G+50bLb7+O90kQCgimwM Rt5kAqM4EvAMHsQ8U1ShKzo= =VdVV -----END PGP SIGNATURE-----
Joshua D. Drake wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On Fri, 14 Mar 2008 02:00:39 -0600 > Micah Yoder <micah@yoderdev.com> wrote: > > >> 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. :-) >> > > If you don't have enough control over the application to handle that > type of situation, no database is going to serve your purposes. > > Beyond that, PostgreSQL is one of the most flexible database systems > around when it comes to security and my company professionally supports > several financial firms using PostgreSQL as their core database. > > Sincerely, > > Joshia D. Drake > Is it possible to share what audit regulations you have been able to meet with Postgres? Do you deal with SOX or PCI regs that require an audit trail for DBAs and SAs (e.g. PCI v1.1 10.1)? Short of building in some Oracle-like audit vault, I don't see how you can do this without falling back to mitigating controls loopholes. Paul
Thanks for the replies. That's kind of what I figured, though it would be interesting if it were possible. For example, if a financial institution could allow their clients direct connections to a database, the clients (or anyone) could build absolutely any interface to it they want. I think that would be awesome. I was also thinking a bit more broad than just finance. Could PG be used, for example, as a multiplayer strategy game server where clients can directly connect without another middleware daemon? Seems to me like it has everything necessary, except for this problem. Perhaps this could be worked around by building a slim proxy for PG connections (and maybe one exists, I haven't looked). All it would really need to do, I think, is filter BEGIN commands so clients could not start transactions to hold locks. All tables would be non-accessible to clients except through SECURITY DEFINER functions, so I don't think there's any other way they could grab a lock, or cause too much trouble (correct me if I'm wrong!). > You cannot manage transactions inside functions. A function always > runs inside a single transaction. Actually from the pl/pgsql manual it looks like you can raise an error and have it abort the surrounding transaction. If that's true it should be robust. Thanks, Micah
Micah Yoder wrote: > I was also thinking a bit more broad than just finance. Could PG be used, for > example, as a multiplayer strategy game server where clients can directly > connect without another middleware daemon? Seems to me like it has > everything necessary, except for this problem. Each software serves certain purposes. Databases provide fast, reliable, consistent, and concurrent storage and retrieval of data. That's all they try to accomplish. If you want something else, you'll have to use different software. You are dreaming of "the one program that does everything". This animal only exists in marketing brochures. Fortunately. >> You cannot manage transactions inside functions. A function always >> runs inside a single transaction. > > Actually from the pl/pgsql manual it looks like you can raise an error and > have it abort the surrounding transaction. If that's true it should be > robust. It is - once the transaction is aborted due to an error it will remain in that state until you terminate the transaction (implicitly or explicitly). Actually, exception *handling* is implemented using savepoints, so you could say that one can do limited transaction management inside a function. But you cannot start or end a transaction inside a function. Yours, Laurenz Albe