Re: System catalog table privileges

On 7/21/06, Hilary Forbes <hforbes(at)dmr(dot)co(dot)uk> wrote:
>
> 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.

==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================