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) juanrcue...@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
Re: [ADMIN] revoked permissions on table still allows users to see table's structure
Juan, That is what schemas, permissions and search paths are for. You create multiple schemas, put the tables in the appropriate ones, grant usage permissions to those users that need access to the schemas and set the search path to search the 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) juanrcue...@quality-telecom.net To: Scott Marlowe scott.marl...@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) juanrcue...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] revoked permissions on table still allows users to see table's structure
Bob Lunney bob_lun...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] revoked permissions on table still allows users to see table's structure
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) juanrcue...@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
Re: [ADMIN] revoked permissions on table still allows users to see table's structure
Juan Cuervo (Quality Telecom) juanrcue...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] revoked permissions on table still allows users to see table's structure
-Original Message- From: Kevin Grittner [mailto:kevin.gritt...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] revoked permissions on table still allows users to see table's structure
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)juanrcue...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] revoked permissions on table still allows users to see table's structure
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_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] 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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] revoked permissions on table still allows users to see table's structure
On Thu, Jul 21, 2011 at 6:08 PM, Juan Cuervo (Quality Telecom) juanrcue...@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
