Thread: System catalog table privileges
Dear All<br /><br /> Next question on privileges! Can I safely remove all privileges from the system catalog tables fora user and still enable them to select from the public schema? I guess the real question is what access rights does anordinary user have to have to the system catalog tables in order for postgres to work properly given I only ever want theuser to be able to SELECT from views.<br /><br /> This is all brought about by a user who wants to use MS Access Queryfor adhoc queries to a (small) database via ODBC. (the database itself drives a major web application.) I can't findan easy way of preventing them seeing that tables exist but I don't want them trying to manually update any tables ofmine or postgres's thank you very much! (Don't shoot the messenger - there's no accounting for user's tastes!)<br /><br/> TAI<br /> Hilary<br /><p> Hilary Forbes<br /> DMR Limited (UK registration 01134804) <br /> A DMR Information andTechnology Group company (<a eudora="autourl" href="http://www.dmr.co.uk/"><font color="#0000FF"><u>www.dmr.co.uk</u></font></a>)<br /> Direct tel 01689 889950 Fax 01689 860330 <br /> DMR is a UK registeredtrade mark of DMR Limited<br /> **********************************************************
On 7/21/06, Hilary Forbes <hforbes@dmr.co.uk> wrote:
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
==================================================================
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!)
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
==================================================================
Hilary Forbes <hforbes@dmr.co.uk> writes:
> Next question on privileges! Can I safely remove all privileges
> from the system catalog tables for a user
Various people have experimented with doing that, but it tends to break
a lot of stuff. I suggest you just revoke privileges on the tables you
don't want the users messing with, and not get too worried about whether
they know the tables exist or not.
regards, tom lane
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 /> **********************************************************
On Fri, 2006-07-21 at 11:19, Hilary Forbes wrote: > Aaron > > Thanks for this one - I had actually wondered about doing that but the > trouble is that they say that they need up to the minute reports not > "as of last night". Indeed, I do have another app where I do just > that because I find that reports indexes/requirements are very > different to transactional type requirements. However, you have made > me make up my mind to see if I can persuade them to work on data that > is a day old. > > What we really need is a good graphical (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.... A couple of points. 1: You can still use a slave server, just look into slony. It's what we use, and it's quite reassuring to know that I can let Joe Sixpack loose with a query editor and not worry about him killing my main production database. 2: If you HAVE to let an untrusted user have access to your real database, then make them use views, and place some kind of limit in the view that keeps them from blowing things up with really ugly queries. That won't stop a truly determined person from DOSing your server, but will help. 3: Look at pentaho. It's got a standalone java version that will do a lot in the graphical (windowsish) realm.
On 7/21/06, Hilary Forbes <hforbes@dmr.co.uk> wrote:
I have heard "I need up to the minute data" a lot but have NEVER seen it to be true. I guess if you are trading stocks on the stock market and need to buy and sell immediately as the prices change then you would have a reason but almost always business users think they need things now when they don't.
You could also look at the cost/benefit: if they bring the database down, how much would it cost the business? If they are working on day old data, how much would it cost? Get the user to write down and justify their numbers. This will show to you and the user whether it is really necessary to report off of the live data.
Good luck!
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
Aaron
Thanks for this one - I had actually wondered about doing that but the trouble is that they say that they need up to the minute reports not "as of last night". Indeed, I do have another app where I do just that because I find that reports indexes/requirements are very different to transactional type requirements. However, you have made me make up my mind to see if I can persuade them to work on data that is a day old.
You could also look at the cost/benefit: if they bring the database down, how much would it cost the business? If they are working on day old data, how much would it cost? Get the user to write down and justify their numbers. This will show to you and the user whether it is really necessary to report off of the live data.
Good luck!
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================