Re: Restrict users from describing table

Andrew Dunstan wrote:<br /><blockquote cite="mid4433.24.211.141.25.1073179123.squirrel@www.dunslane.net"
type="cite"><prewrap="">Michael Gill said: </pre><blockquote type="cite"><pre wrap="">Hello,
 

I've tried asking this question elsewhere and have not received a
satisfactory response.

I want to restrict users of my packaged database from directly
accessing  the data or reading the schema. I would provide access to
the read-only  data through functions (which works well in PG). I find
that \d will  expose the structure even though the user can't select:

movies=> \d codeset.first_table
Table "codeset.first_table"
Column |  Type   | Modifiers
--------+---------+-----------
col1   | integer |
col2   | integer |

movies=> select * from codeset.first_table;
ERROR:  permission denied for schema codeset

Is there any way to hide the structure from a particular user. I can't
use Postgresql if I can't encapsulate our intellectual property.
   </pre></blockquote><pre wrap="">
How will purchasers of your product run pg_dump if the superuser can't get
at the database schema?

The only way I can see to do this in general is some sort of filter layer
between the database and the user.

I'm mildly dubious of the IP value of a database schema, I must confess. I
guess you could also play funny games with the column and table names to
obscure the semantics, at the obvious cost of a maintenance nightmare.

cheers

andrew

 </pre></blockquote> The reason it would work in my situation is that the database provided to the customer is
read-only.It will only be upgraded by us, but the customer needs to access the data (indirectly). So, pg_dump is not
relevantto us in this scenario.<br /><br /> I think I have found the simple solution by separating the user from the
ownerof the tables, however!<br /><br /> I have simply created tables and functions in the owner's schema(A), then
grantedexecution to the other user(B). My brief testing indicates that B cannot access or describe A's objects, yet can
executethe function that retrieves data and returns a ref cursor.<br /><br />