Re: Restrict users from describing table

Andrew Dunstan wrote:

>Michael Gill said:
>
>
>>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.
>>
>>
>>
>
>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
>
>
>
>
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
relevant to us in this scenario.

I think I have found the simple solution by separating the user from the
owner of the tables, however!

I have simply created tables and functions in the owner's schema(A),
then granted execution to the other user(B). My brief testing indicates
that B cannot access or describe A's objects, yet can execute the
function that retrieves data and returns a ref cursor.