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.

Thanks,
Michael Gill

mgill@pointdx.com
mydba@usa.com

Tue, 30 Dec 2003 14:53:27 +0500
Michael Gill <mgill@pointdx.com>
Restrict users from describing table
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



Sat, 3 Jan 2004 20:18:43 -0500 (EST)
"Andrew Dunstan" <andrew@dunslane.net>
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.








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.
Mon, 05 Jan 2004 11:32:42 +0500
Michael Gill <mgill@pointdx.com>
Re: Restrict users from describing table
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.

Thu, 8 Jan 2004 22:10:35 -0600
Bruno Wolff III <bruno@wolff.to>
Re: Restrict users from describing table
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.



Fri,  9 Jan 2004 09:05:04 -0500
mgill@pointdx.com
Re: Restrict users from describing table