Re: System catalog table privileges - Mailing list pgsql-sql

Dear All

Next question on privileges!  Can I safely remove all privileges from the system catalog tables for a user and still enable them to select from the public schema?  I guess the real question is what access rights does an ordinary user have to have to the system catalog tables in order for postgres to work properly given I only ever want the user to be able to SELECT from views.

This is all brought about by a user who wants to use MS Access Query for adhoc queries to a (small) database via ODBC.  (the database itself drives a major web application.) I can't find an easy way of preventing them seeing that tables exist but I don't want them trying to manually update any tables of mine or postgres's thank you very much!  (Don't shoot the messenger - there's no accounting for user's tastes!)

This doesn't address the permissions issue but is a suggestion regarding your approach on granting access to an untrusted user for reporting purposes...

Whenever I have a user that needs to do reporting from any production database, I set up a separate reporting database.  If possible, this is placed on a completely different machine and the data is fed from production to the reporting server nightly.  Tech savy business users (the ones who typically need this kind of access) are notorious for writing bad queries and causing performance problems.  If you isolate their activity, you will eliminate lots of headache.  If they cause a problem on the reporting server, you don't have to drop everything to get the problem fixed like you would if they caused problems on the live database.

An argument that the users who run the reports often make is that they need the most current data.  Most of the time this is not the case.  My recommendation is to let the users create the queries they need to run for realtime data on the reporting database, then pass them by an expert for review before putting them into an IT controlled reporting application.

Bottom line, be careful about giving non-experts too much access to your live production data.