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 />