X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 1C18D9FA6B0 for ; Fri, 21 Jul 2006 12:07:41 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 63816-10 for ; Fri, 21 Jul 2006 12:07:32 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.186]) by postgresql.org (Postfix) with ESMTP id D82079FA50D for ; Fri, 21 Jul 2006 12:07:31 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id p77so895517nfc for ; Fri, 21 Jul 2006 08:07:30 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=MHEM6j4/VE+2rlji8DO01Hx1HwVdf1PSkVtuceB2sFW6/Cp7nL49ZzdTHVfyhHcZuIj2koBYFPquyvrQLP7pEYwMzeaHF7bQFSa6SZx8/H4XL0QIqicuK2rr0tyePseEKTR8nFW4M4nUVwu39132DB1cfXnWu5KmZEfbcHtpvd8= Received: by 10.78.151.3 with SMTP id y3mr415539hud; Fri, 21 Jul 2006 08:07:30 -0700 (PDT) Received: by 10.78.32.10 with HTTP; Fri, 21 Jul 2006 08:07:30 -0700 (PDT) Message-ID: Date: Fri, 21 Jul 2006 10:07:30 -0500 From: "Aaron Bono" To: "Hilary Forbes" Subject: Re: System catalog table privileges Cc: pgsql-sql@postgresql.org In-Reply-To: <5.1.0.14.0.20060721133547.040c0e28@mailserver.dmr.co.uk> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_80434_24543302.1153494450514" References: <5.1.0.14.0.20060721133547.040c0e28@mailserver.dmr.co.uk> X-Google-Sender-Auth: 733900a4eadadf4a X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.551 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_10_20, HTML_MESSAGE, SPF_PASS X-Spam-Level: * X-Archive-Number: 200607/274 X-Sequence-Number: 25848 ------=_Part_80434_24543302.1153494450514 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/21/06, Hilary Forbes 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 ================================================================== ------=_Part_80434_24543302.1153494450514 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/21/06, Hilary Forbes <hforbes@dmr.co.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
================================================================== ------=_Part_80434_24543302.1153494450514--