Обсуждение: Restrict users from describing table
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_tableTable "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. Thanks, Michael Gill mgill@pointdx.com mydba@usa.com
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
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 />
On Mon, Jan 05, 2004 at 11:32:42 +0500, Michael Gill <mgill@pointdx.com> wrote: > > 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. > This won't work as they can still get at the system catalog which will allow them to see the schema.
Quoting Bruno Wolff III <bruno@wolff.to>: > On Mon, Jan 05, 2004 at 11:32:42 +0500, > Michael Gill <mgill@pointdx.com> wrote: > > > > 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. > > > > This won't work as they can still get at the system catalog which will > allow them to see the schema. > You're correct. There doesn't seem to be a way to restrict a user from reading the system tables. To limit the client to only accessing the data through functions, I'm looking at creating a java-based api to wrap all accesses to the db. The client machine wouldn't need a db password, merely asking for DML through the api.