Re: Install PostgreSQL as part of a desktop application, but how to coop with existing installations?

On 01/17/2011 06:46 PM, Jensen Somers wrote:

> Once a recording session is completed the data should be locked and not
> modified anymore. And everything should be local on the user's PC, I
> can't use an external database server.

Those two requirements are mutually exclusive, unless you're willing to
qualify and limit one or both of them with something like "the user must
not have local admin rights". As stated, you've set yourself
requirements that are impossible to satisfy with ANY database system.

If the user has local admin, then everything is modifiable with varying
degrees of effort. You can potentially both your requirements if the
local user can be guanteed NOT have admin rights, by configuring privs
in the database to permit INSERT but not UPDATE/DELETE on the tables of
interest, or by using SECURITY DEFINER functions to perform limited
priveleged tasks (like "archive this batch into the
read-only-to-normal-users history tables).

No matter what data storage system you use, if your data or the
credentials that grant unlimited read/write access to that data on a
remote server are on the local machine and the user has local admin
rights, your data isn't really read-only. It might be "trickier to
modify", but that's about it. That might be good enough, depending on
how strong your requirement for read-only archival is. If it's a
legal/compliance requirement, you're stuffed, you need non-admin users
or a remote server + limited database access credentials. If it's just
to make playing with the history by accident unlikely and make casual
tampering hard, then maybe it'll be OK to keep the data local even with
a local admin user. They'll at least have to jump through a few hoops to
bypass the app and start modifying the data directly.

This applies to Pg, MS SQL Express, MySQL, SQLite, etc. If the user has
local admin rights, you have no security against them.

> My first idea was to use SQL
> Compact Edition. It can easily create a file which I can password
> protect so a user cannot open the file and modify the data.

... except by brute-force cracking the password, examining your program
binary to extract and read the password, capturing the password using a
debugger as your program passes it to the MS SQL Compact Edition library
calls, using their local admin rights to load a malicious DLL that
intercepts the calls and logs the password, ... etc etc etc.

How secure does it have to be? Against whom? What must it prevent, and
what things may it let past? Define your requirements in detail, THEN
start thinking about how to satisfy them.

> I have no problem with a user knowing I use PostgreSQL, but if there's
> no way I can block him out of the database and prevent him from
> manipulating the data in any way PostgreSQL is, unfortunately, no longer
> an option.

There is no way you can lock the user out of ****ANY**** database, data
file, etc if they have local administrator rights on the computer, the
data is on that computer, and the credentials used to access that data
(embedded in your program or otherwise) are also on the computer. None.
Zero. Nil. All you can do is make it harder for them to varying degrees.

Even if they don't have local admin, if they have physical access to the
computer they can get local admin if they particularly want it. Boot an
ubuntu CD / USB key, reset the NT password for the local admin, and
you're done. Easy. Or do the same thing over PXE (network boot) by
plugging in a laptop and spoofing the DHCP server. Numerous other tools
can be used instead of a linux boot CD, and there are may other ways to
backdoor a system than changing the admin password. The only way you can
stop them is by denying physical access and denying local admin rights.

So: How secure does it have to be?

(You **might** be able to keep it secure against a non-admin user with
physical access if you had a system with a "trusted boot" chain using
the TPM, etc. I doubt you'll be able to require that, though!)

--
Craig Ringer