Hi,
On 17/01/2011 14:49, Craig Ringer wrote:
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.
That's something I know I can't do anything against. But that's the same
problem I face now with my binary data format and so far I have not seen
any user able to modify it. I know from the user forums that a lot of
people tried, but they failed. But I can achieve the same thing by
password protecting the SQL CE or SQLite database file. And that's good
enough for me, I know I cannot get a 100% secure solution, but the one I
can get is good enough for me. Since my application is used by a very
small number of users world wide ( < 10.000) I don't really need to
worry about it being cracked in the same way Adobe or Microsoft needs to
worry.
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.
I can hide the credentials in my application, that's what I currently
also need to do in order to access the binary data file.
The data that is logged can be uploaded, only if a user desires, to a
web service which process the data and compares it against other user.
What we saw from previous application versions which used an XML based
file format is that a lot of users manipulated their data to get better
results. That was "fixed" by introducing our binary format, but that has
proven to be hard to maintain and work with in general which is my I am
currently looking into database solutions. And I want to provide some
protection, I don't want the data to be wide open to everyone.
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?
The password protection I can get using SQLite or SQL CE is more than
enough. If I can do the same thing with PostgreSQL it's OK. Creating a
database user for my application is what I planned to do. I am also in
no way interested in the user's computer user or admin rights, I am just
talking about a database user.
But, from your initial reply I understood that a user can simply browse
to my database installation folder (e.g.: C:/ProgramData/MyApp/data),
read out and/or modify a configuration file and he can access the entire
database and modify the data. And that's what I want to prevent.
(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
- Jensen
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
