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