Question :
I am trying to tamper proof as best as possible a peer to peer application.
I am concerned that the prepared statements will be altered.
Can the Postgres admin alter a connection’s prepared statements with built-in functionality?
If so, how best can this be managed? Randomly looping to unprepare and reprepare?
Answer :
If the user has:
- Physical hardware access;
- Root / “Administrator” access to the system; or
- PostgreSQL superuser rights;
then they effectively have total control and can do whatever they feel like.
It’s a little trickier when starting with superuser access, but the PostgreSQL superuser is not isolated from the underlying OS user that PostgreSQL its self runs as. They can modify the data directory contents in-place, change views, install extensions, etc.
If you’re running a database on someone else’s hardware, they control the database – not you. You must design with that in mind, allowing for the idea that they can modify the data, queries, etc in any way they feel like.
It’s pretty simple to recompile PostgreSQL with whatever modifications you feel like, or add extensions to do so. Feel like matching query text and replacing it with a different query? Easy, that’s just a parser or executor hook.
Or you can just attach a debugger and do it live.
So, while the user can’t just go modifying prepared statements directly from SQL, if they have control of the system they can still do it with varying degrees of difficulty. Whether it’s “hard enough” for your purposes depends mainly on what the incentives for breaking the system are, the skill level of the attacker(s), whether attackers communicate with each other, etc.
There are some strategies you can use to make untrusted local storage better protected – for example, signing data with a key known only to the entity that generates it, so others can verify its authenticity but not modify it.