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

Aaron<br /><br /> Thanks for this one - I had actually wondered about doing that but the trouble is that they say that
theyneed up to the minute reports not "as of last night".  Indeed, I do have another app where I do just that because I
findthat reports indexes/requirements are very different to transactional type requirements.  However, you have made me
makeup my mind to see if I can persuade them to work on data that is a day old.<br /><br /> What we really need is a
goodgraphical (Windows based) query/report tool that allows us to configure the tables to be viewed etc etc and, most
importantly,is license free.  There's fame for someone there....  <br /><br /> Hilary<br /><br /><br /><br /><br /> At
10:0721/07/2006 -0500, Aaron Bono wrote:<br /><br /><blockquote cite="cite" class="cite" type="cite">On 7/21/06,
<b>HilaryForbes</b> <<a href="mailto:hforbes@dmr.co.uk">hforbes@dmr.co.uk</a>> wrote:<br /><blockquote
cite="cite"class="cite" type="cite">Dear All<br /><br /> Next question on privileges!  Can I safely remove all
privilegesfrom the system catalog tables for a user and still enable them to select from the public schema?  I guess
thereal question is what access rights does an ordinary user have to have to the system catalog tables in order for
postgresto work properly given I only ever want the user to be able to SELECT from views.<br /><br /> This is all
broughtabout by a user who wants to use MS Access Query for adhoc queries to a (small) database via ODBC.  (the
databaseitself drives a major web application.) I can't find an easy way of preventing them seeing that tables exist
butI 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!)</blockquote><br />  <br /> This doesn't address the permissions
issuebut is a suggestion regarding your approach on granting access to an untrusted user for reporting purposes... <br
/><br/> 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
reportingserver nightly.  Tech savy business users (the ones who typically need this kind of access) are notorious for
writingbad 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
likeyou would if they caused problems on the live database. <br /><br /> An argument that the users who run the reports
oftenmake is that they need the most current data.  Most of the time this is not the case.  My recommendation is to let
theusers create the queries they need to run for realtime data on the reporting database, then pass them by an expert
forreview before putting them into an IT controlled reporting application. <br /><br /> Bottom line, be careful about
givingnon-experts too much access to your live production data.<br /><br />
==================================================================<br/>    Aaron Bono<br />    Aranya Software
Technologies,Inc. <br />    <a href="http://www.aranya.com">http://www.aranya.com</a><br />
==================================================================</blockquote><p> Hilary Forbes<br /> DMR Limited (UK
registration01134804) <br /> A DMR Information and Technology Group company (<a eudora="autourl"
href="http://www.dmr.co.uk/"><fontcolor="#0000FF"><u>www.dmr.co.uk</u></font></a>) <br /> Direct tel 01689 889950 Fax
01689860330 <br /> DMR is a UK registered trade mark of DMR Limited<br />
**********************************************************