Re: PG secure for financial applications ...

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