This is dangerous territory (and I strongly recommend you weigh up this requirement carefully due to the obvious dangers you will be exposing yourself to), however I will try to give you the safest way to proceed if you must.
The only assumption I am making here is that you are running a current version of PostgreSQL and that you require users to remotely connect to the server (using their own tools) to execute their custom queries. Even if they will be entering them into a webpage, most of the same techniques will still apply as long as they each have a separate user log in for the database server.
First, (as NoBugs pointed out) to prevent users executing obvious malicious statements (like UPDATES, DELETES, DROPS, etc) you need to ensure that the user account connecting to the server has only SELECT permissions on the db(s) and table(s) they should be able to read from. Have a look in manual to see how to define roles for users, and grant specific permissions to those roles.
http://www.postgresql.org/docs/9.0/static/user-manag.html
http://www.postgresql.org/docs/9.0/static/database-roles.html
Note that you can only limit a user down to a particular table. If
users each need to be given access to different parts of a table, then
PostgreSQL (and nearly all DBMS's) will not support this out of the
box. Your only option would be to try and create some kind of SQL/TCP
proxy that intercepts requests, and modifies them somehow to limit
query results, before passing on to the database server. This would be
extremely difficult even for a very experienced developer!
To prevent (or at least detect) DOS attacks, you will need an external script or process to keep an eye on the resource usage of the database (and/or the entire server) every few seconds, and possibly build in a mechanism to restart the PostgreSQL service if it is maxed-out.
You will need to experiment with how long before you should intervene
carefully, as it is quite possible for a legitimate query to max
things for a few seconds.
As you mentioned, you would need to keep a careful log of who was trying to execute what, & when so, if necessary you can work backwards from a failure, to find out the culprit. You can really only rely on the system logs for this, which can be configured to write out to files, CSV, or Syslog.
I would suggest you pre-create some tools to help you quickly search
these logs to find what you need before you need to try and find it
(pun intended).
Finally you should also try to follow the other standard best practices for administration and security (all of which can be found in the manuals) including:
- Only allow access for your users from specific ip's/hosts (dont give the general public any chance at connecting to your server. Your customers will need static IP's to access the system, but this is certainly worth considering to mitigate risks.
- Keep a close eye on all of your standard administrative tasks for the server (especially backups, disk space, log file maintenance, index usage, etc.)
