Обсуждение: revoked permissions on table still allows users to see table's structure

Hi All

I'm new to the list, but have a few years as postgres user. I want to
share what I consider a rare behavior of postgresql regarding database
object's premissions:

I have noticed that there is no way (at least no one I know) to prevent
a user from seeing the table's structures in a database.

I created a new user (user1) and do the following:

'revoke all on all tables in schema public from public;'

After that, user1 cant do select, inserts , etc from database's tables.
But still is able to see my table's structure:

voicemax=>select * from tasks;
ERROR:  permission denied for relation tasks
voicemax=> delete from tasks where task_id=6;
ERROR:  permission denied for relation tasks
voicemax=> \d tasks
                                         Table "public.tasks"
      Column      |          Type          |
Modifiers
-----------------+------------------------+---------------------------------------------------------
  task_id         | bigint                 | not null
  task_name       | character varying(32)  | not null
  description     | character varying(128) |
  enabled         | integer                | default 0
Indexes:
     "tasks_task_name_key" UNIQUE, btree (task_name)

The same behavior occurs when this user is logged from pgAdmin. User can
see all table's structure, even if have no privileges on database tables.

Is this a normal behavior of the product ?
Is there a way to prevent a user from seeing my table's, procedure's and
function's code ?

Thanks in advance.

--
Juan R. Cuervo Soto
Quality Telecom Ltd
www.quality-telecom.net
PBX : (575) 3693300
CEL : (57)  301-4174865

On Thu, Jul 21, 2011 at 6:08 PM, Juan Cuervo (Quality Telecom)
<juanrcuervo@quality-telecom.net> wrote:
> Hi All
>
> I'm new to the list, but have a few years as postgres user. I want to share
> what I consider a rare behavior of postgresql regarding database object's
> premissions:
>
> I have noticed that there is no way (at least no one I know) to prevent a
> user from seeing the table's structures in a database.
>
> Is this a normal behavior of the product ?

Yep.  Completely normal.

> Is there a way to prevent a user from seeing my table's, procedure's and
> function's code ?

Don't let them connect to the db?  That's all I can think of.

Hi Scott

Thanks for your answer.

It should be a way to prevent this from normal users who only need
access to a set of tables, a view or even a store procedure. (Maybe a
VIEW_SCHEMA privilege of roles?). View a table's structure should only
be allowed to users who has at least one privilege on the table.

It doesnt make much sense to me that every user with access to the
database , would be able to see the whole database design.

Do you know if this is common in other RDBMS ?

Regards,

Juan R. Cuervo Soto
Quality Telecom Ltd
www.quality-telecom.net
PBX : (575) 3693300
CEL : (57)  301-4174865


El 21/07/2011 08:48 p.m., Scott Marlowe escribió:
> On Thu, Jul 21, 2011 at 6:08 PM, Juan Cuervo (Quality Telecom)
> <juanrcuervo@quality-telecom.net>  wrote:
>> Hi All
>>
>> I'm new to the list, but have a few years as postgres user. I want to share
>> what I consider a rare behavior of postgresql regarding database object's
>> premissions:
>>
>> I have noticed that there is no way (at least no one I know) to prevent a
>> user from seeing the table's structures in a database.
>>
>> Is this a normal behavior of the product ?
> Yep.  Completely normal.
>
>> Is there a way to prevent a user from seeing my table's, procedure's and
>> function's code ?
> Don't let them connect to the db?  That's all I can think of.
>

Juan,

That is what schemas, permissions and search paths are for.  You create multiple schemas, put the tables in the
appropriateones, grant usage permissions to those users that need access to the schemas and set the search path to
searchthe schemas for objects.  Below is the test case.  It helps if you reset the psql prompt to display the current
user:

\set PROMPT1 '%m:%>:%n:%/:%R%x%# '

As the database owner:

create schema seethat;
create schema seewhat;
create user al_low;
create user dee_ny;
grant usage on schema seethat to al_low, dee_ny;
grant usage on schema seewhat to al_low;
set search_path to seethat, seewhat, public;

create table seethat.open(open_id int);
create table seewhat.closed(closed_id int);


set session authorization al_low;

\d


set session authorization dee_ny;

\d


Hope that helps!

Bob Lunney

----- Original Message -----
From: Juan Cuervo (Quality Telecom) <juanrcuervo@quality-telecom.net>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-admin@postgresql.org
Sent: Friday, July 22, 2011 8:24 AM
Subject: Re: [ADMIN] revoked permissions on table still allows users to see table's structure

Hi Scott

Thanks for your answer.

It should be a way to prevent this from normal users who only need
access to a set of tables, a view or even a store procedure. (Maybe a
VIEW_SCHEMA privilege of roles?). View a table's structure should only
be allowed to users who has at least one privilege on the table.

It doesnt make much sense to me that every user with access to the
database , would be able to see the whole database design.

Do you know if this is common in other RDBMS ?

Regards,

Juan R. Cuervo Soto
Quality Telecom Ltd
www.quality-telecom.net
PBX : (575) 3693300
CEL : (57)  301-4174865


El 21/07/2011 08:48 p.m., Scott Marlowe escribió:
> On Thu, Jul 21, 2011 at 6:08 PM, Juan Cuervo (Quality Telecom)
> <juanrcuervo@quality-telecom.net>  wrote:
>> Hi All
>>
>> I'm new to the list, but have a few years as postgres user. I want to share
>> what I consider a rare behavior of postgresql regarding database object's
>> premissions:
>>
>> I have noticed that there is no way (at least no one I know) to prevent a
>> user from seeing the table's structures in a database.
>>
>> Is this a normal behavior of the product ?
> Yep.  Completely normal.
>
>> Is there a way to prevent a user from seeing my table's, procedure's and
>> function's code ?
> Don't let them connect to the db?  That's all I can think of.
>

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Bob Lunney <bob_lunney@yahoo.com> wrote:

> That is what schemas, permissions and search paths are for.

I don't think those do as much as you're giving them credit for:

test=> set session authorization dee_ny;
SET
test=> \d
        List of relations
 Schema  | Name | Type  |  Owner
---------+------+-------+---------
 public  | a    | table | kgrittn
 public  | b    | table | kgrittn
 seethat | open | table | kgrittn
(3 rows)

test=> \dt seewhat.*
         List of relations
 Schema  |  Name  | Type  |  Owner
---------+--------+-------+---------
 seewhat | closed | table | kgrittn
(1 row)

test=> \d seewhat.closed
     Table "seewhat.closed"
  Column   |  Type   | Modifiers
-----------+---------+-----------
 closed_id | integer |

I don't think I've used any database where the *structure* of
database objects was hidden from someone connected to the database.
There are typically system tables of some sort to which all
authorized users in the database have read-only access.  The
parallel I would draw in the "real world" is that the format of the
forms which are required for adoption in the Wisconsin court system
are a matter of public record -- anybody can see the blank forms.
Getting a look at data which has been entered onto such forms is a
very different matter.

If you want to hide the structure of the tables from a person, you
need to deny that person authority to connect to the database.  You
can always allow such a person to connect to an application which
you are running in a trusted environment.

-Kevin

In my opinion, that is precicely what privileges where created for:  in
order to restrict what people with database's access can do.
As I see it, it would make a lot of sense to have something like a
'view_design' privilege on database objects.

Imagine you own a software development company, and decides to base the
company's product on Postgresql databases.
Such a company surely dont want to expose his database design to its
customers, but in some time might want to provide 'select' access to
some users, so they can pull data to external datamining or data
analisys tools, for example. If this is not possible in postgresql right
now, then all users with connect privilege will be able to see not only
the table's structure, but also the stored procedures code, wich in many
cases, stores a business logic or know-how.

I believe postgresql is the best open source RDBMS, but I see this
behavior of postgresql as a limitation, and the solution of forbiding
users the database's access is also radical and limiting.

I hace found several posts related to this issue, and seems like nothing
have been done, maybe because this is not considered necessary, or just
becasuse the product works fine this way. However, If there are others
who agree with me, I encourage them to help me propose or develop a
solution to this issue, and probably post it as a patch or optional
improvement to the postgresql product.


Regards,


Juan R. Cuervo Soto
Quality Telecom Ltd
www.quality-telecom.net
PBX : (575) 3693300
CEL : (57)  301-4174865


El 21/07/2011 08:48 p.m., Scott Marlowe escribió:
> On Thu, Jul 21, 2011 at 6:08 PM, Juan Cuervo (Quality Telecom)
> <juanrcuervo@quality-telecom.net>  wrote:
>> Hi All
>>
>> I'm new to the list, but have a few years as postgres user. I want to share
>> what I consider a rare behavior of postgresql regarding database object's
>> premissions:
>>
>> I have noticed that there is no way (at least no one I know) to prevent a
>> user from seeing the table's structures in a database.
>>
>> Is this a normal behavior of the product ?
> Yep.  Completely normal.
>
>> Is there a way to prevent a user from seeing my table's, procedure's and
>> function's code ?
> Don't let them connect to the db?  That's all I can think of.
>

"Juan Cuervo (Quality Telecom)" <juanrcuervo@quality-telecom.net>
wrote:

> Imagine you own a software development company,

Not too hard for me.  Been there, done that.

> and decides to base the company's product on Postgresql databases.
> Such a company surely dont want to expose his database design to
> its customers, but in some time might want to provide 'select'
> access to some users, so they can pull data to external datamining
> or data analisys tools, for example. If this is not possible in
> postgresql right now, then all users with connect privilege will
> be able to see not only the table's structure, but also the stored
> procedures code, wich in many cases, stores a business logic or
> know-how.

Imagine that the software is running on a machine under the client's
control, where they have root access to the OS.  They can then
disassemble or debug through code to see how the encrypted procedure
code is turned into something the database can compile, they can
connect to the database as the superuser to view all details.  The
only protection provided by what you suggest is from those too inept
to really pose a competitive threat.  If you think some other
product gives you protection beyond this, it is an illusion.

The only way to protect your schema and logic from view is to offer
"software as a service".  While someone might still infer a lot
about the structure of the data and the logic of the code from
observing its displays and the procedures available to the user, you
would have some insulation.

-Kevin

-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: Friday, July 22, 2011 10:33 AM
To: Juan Cuervo (Quality Telecom); Bob Lunney
Cc: pgsql-admin@postgresql.org
Subject: Re: revoked permissions on table still allows users to see
table's structure


I don't think I've used any database where the *structure* of
database objects was hidden from someone connected to the database.
There are typically system tables of some sort to which all
authorized users in the database have read-only access.  The
parallel I would draw in the "real world" is that the format of the
forms which are required for adoption in the Wisconsin court system
are a matter of public record -- anybody can see the blank forms.
Getting a look at data which has been entered onto such forms is a
very different matter.

If you want to hide the structure of the tables from a person, you
need to deny that person authority to connect to the database.  You
can always allow such a person to connect to an application which
you are running in a trusted environment.

-Kevin

Not exactly.
In Oracle user needs to be granted SELECT_CATALOG_ROLE role in order to
get SELECT privileges on data dictionary views.

Regards,
Igor Neyman

We had the same problem, and we still do not have an elegant solution,
we have a workaround which I really don't like.

I agree with Juan - it is a limitation. I understand that you can solve
this problem outside of a database, but it will be nice to have a
strictly read only user who can just see data of the assigned objects
and nothing else.

Dinesh

O-+n 7/22/2011 11:00 AM, Kevin Grittner wrote:
> "Juan Cuervo (Quality Telecom)"<juanrcuervo@quality-telecom.net>
> wrote:
>
>> Imagine you own a software development company,
>
> Not too hard for me.  Been there, done that.
>
>> and decides to base the company's product on Postgresql databases.
>> Such a company surely dont want to expose his database design to
>> its customers, but in some time might want to provide 'select'
>> access to some users, so they can pull data to external datamining
>> or data analisys tools, for example. If this is not possible in
>> postgresql right now, then all users with connect privilege will
>> be able to see not only the table's structure, but also the stored
>> procedures code, wich in many cases, stores a business logic or
>> know-how.
>
> Imagine that the software is running on a machine under the client's
> control, where they have root access to the OS.  They can then
> disassemble or debug through code to see how the encrypted procedure
> code is turned into something the database can compile, they can
> connect to the database as the superuser to view all details.  The
> only protection provided by what you suggest is from those too inept
> to really pose a competitive threat.  If you think some other
> product gives you protection beyond this, it is an illusion.
>
> The only way to protect your schema and logic from view is to offer
> "software as a service".  While someone might still infer a lot
> about the structure of the data and the logic of the code from
> observing its displays and the procedures available to the user, you
> would have some insulation.
>
> -Kevin
>

On Jul 22, 2011, at 12:09 PM, Dinesh Bhandary wrote:

> ...but it will be nice to have a strictly read only user who can just see data of the assigned objects and nothing
else.

Surely you mean data & structure of the assigned objects and no other objects?

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice