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

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.

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.