Thread: deny access to system tables
I've created a user "dblackey" and given them select permissions (no insert, update, etc...) on a couple of tables in my database. This prevents them from selecting from arbitrary tables, but is there any way to deny them select access to the system tables? REVOKE ALL ON pg_proc FROM dblackey doesn't seem to work... Robert Treat
On Wed, Oct 09, 2002 at 05:59:41PM -0400, Robert Treat wrote: > I've created a user "dblackey" and given them select permissions (no > insert, update, etc...) on a couple of tables in my database. This > prevents them from selecting from arbitrary tables, but is there any way > to deny them select access to the system tables? REVOKE ALL ON pg_proc > FROM dblackey doesn't seem to work... If you prevented SELECT access to the system tables, that would mean they couldn't execute queries, since the parser needs to access system tables to work out what fields are in tables, what indexes are available and such things. What are you trying to achieve? -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
On Wednesday 09 October 2002 08:29 pm, Martijn van Oosterhout wrote: > On Wed, Oct 09, 2002 at 05:59:41PM -0400, Robert Treat wrote: > > I've created a user "dblackey" and given them select permissions (no > > insert, update, etc...) on a couple of tables in my database. This > > prevents them from selecting from arbitrary tables, but is there any way > > to deny them select access to the system tables? REVOKE ALL ON pg_proc > > FROM dblackey doesn't seem to work... > > If you prevented SELECT access to the system tables, that would mean they > couldn't execute queries, since the parser needs to access system tables to > work out what fields are in tables, what indexes are available and such > things. > > What are you trying to achieve? If I could prevent access to pg_proc the user would (theoretically) not be able to run any functions, and more importantly would not be able to see the source of my functions. I believe the same hold true for pg_views. Robert Treat
On Thu, Oct 10, 2002 at 08:42:32 -0400, > > If I could prevent access to pg_proc the user would (theoretically) not be > able to run any functions, and more importantly would not be able to see the > source of my functions. I believe the same hold true for pg_views. You might want to consider upgrading to 7.3 (currently in beta). In 7.3 you can control the ability to execute specific functions using grant and revoke.
On Thu, Oct 10, 2002 at 08:42:32AM -0400, Robert Treat wrote: > On Wednesday 09 October 2002 08:29 pm, Martijn van Oosterhout wrote: > > If you prevented SELECT access to the system tables, that would mean they > > couldn't execute queries, since the parser needs to access system tables to > > work out what fields are in tables, what indexes are available and such > > things. > > > > What are you trying to achieve? > > If I could prevent access to pg_proc the user would (theoretically) not be > able to run any functions, and more importantly would not be able to see the > source of my functions. I believe the same hold true for pg_views. Well, that won't work. pg_proc also contains the functions that parse and output data to the client. You do realise that pg_views, pg_tables and pg_indexes are themselves views. The typecasts used are also looked up in pg_proc. Even tab-completion from psql uses a function. If you don't want users to see the source to your functions, write them in a way that doesn't require the source within postgres, like loading in an external library. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.