Обсуждение: Opinion wanted: Default select rights for users via public schema
Hi Again boring admin question: I found that all users have access to pg_class etc. by default. In my opinion this causes some security questions or at least can make users curious about things they should not. e.g. SELCT * FROM pg_tables where table_name like '%customer_accountings%'; Probably this user should NOT know, that there are some customer_accountings on this system??? How do you solve this problem? Would it not be usefull to have some views like all_tables, user_tables etc. (like a big db company does) for preventing acces to pg_tables (= dba_tables)? How is it recommended to revoke the rights to pg_xxx? REVOKE ALL PRIVS FROM PUBLIC... (like pgdump does) And then create own access rules? It seems to me, that e.g. php_mod for apache does not work properly after this becaus they do NOT find e.g. column names anymore (clear: he does not have access to pg_tables, etc. anymore!) Any hint is welcome Oli ------------------------------------------------------- Oli Sennhauser Database-Engineer (Oracle & PostgreSQL) Rebenweg 6 CH - 8610 Uster / Switzerland Phone (+41) 1 940 24 82 or Mobile (+41) 79 450 49 14 e-Mail oli.sennhauser@bluewin.ch Website http://mypage.bluewin.ch/shinguz/PostgreSQL/ Secure (signed/encrypted) e-Mail with a Free Personal SwissSign ID: http://www.swisssign.ch Import the SwissSign Root Certificate: http://swisssign.net/cgi-bin/trust/import
Вложения
Oli Sennhauser <oli.sennhauser@bluewin.ch> writes: > I found that all users have access to pg_class etc. by default. In my > opinion this causes some security questions or at least can make users > curious about things they should not. This isn't going to change, because it would break too many clients (as indeed you just found out). Give users their own databases if you feel you need that much separation. regards, tom lane
Hello Tom Thank you for answering. >>I found that all users have access to pg_class etc. by default. In my >>opinion this causes some security questions or at least can make users >>curious about things they should not. >> >> >This isn't going to change, because it would break too many clients >(as indeed you just found out). Give users their own databases if you >feel you need that much separation. > > I see. But sometimes your solution is not possible. E.g. if I have a critical application (banking?) and several kind of users on it. Some users should NOT know, what is also around them but they have to use the application. And if they know little about the rdbms they will find things, they should not know, and they will be interested in and they will search for knowing what is in, and so on.... In my opinion this is a (dd) design error done longe time ago. Now it depends where do we want to go with PostgreSQL. In my opinion with this "bug" (and some others) we will allways have the image of "little boys (and girls) playing little around with rdbms. Would it not be possible, e.g. to say we have a new interface now (e.g. pg_tables, user_tables, all_tables and so on) and application should now use the new interface and the old interface will outage in 2 or 3 years. Otherwise we will never get rid of this problems. Thanks for discussing Oli ------------------------------------------------------- Oli Sennhauser Database-Engineer (Oracle & PostgreSQL) Rebenweg 6 CH - 8610 Uster / Switzerland Phone (+41) 1 940 24 82 or Mobile (+41) 79 450 49 14 e-Mail oli.sennhauser@bluewin.ch Website http://mypage.bluewin.ch/shinguz/PostgreSQL/ Secure (signed/encrypted) e-Mail with a Free Personal SwissSign ID: http://www.swisssign.ch Import the SwissSign Root Certificate: http://swisssign.net/cgi-bin/trust/import
Вложения
On Fri, Dec 26, 2003 at 15:39:00 +0100, Oli Sennhauser <oli.sennhauser@bluewin.ch> wrote: > I see. But sometimes your solution is not possible. E.g. if I have a > critical application (banking?) and several kind of users on it. Some > users should NOT know, what is also around them but they have to use the > application. And if they know little about the rdbms they will find > things, they should not know, and they will be interested in and they > will search for knowing what is in, and so on.... It is unlikely that keeping the table structure secret is of much importance. They can get that by several means (like running the application under a debugger). In some cases you might not want people to know the approximate number of rows in some tables or other things you can find out from the catalogs. But in general this won't be true. And I wouldn't expect business applications to be one of the areas where this is likely to be a problem. > In my opinion this is a (dd) design error done longe time ago. Now it > depends where do we want to go with PostgreSQL. In my opinion with this > "bug" (and some others) we will allways have the image of "little boys > (and girls) playing little around with rdbms. I disagree. The current system allows for flexibility in the clients. You can get information from the catalogs without having to have a special function created for each type of information you want to get. (And even that wouldn't really solve your problem since information will leak via timing.) > Would it not be possible, e.g. to say we have a new interface now (e.g. > pg_tables, user_tables, all_tables and so on) and application should now > use the new interface and the old interface will outage in 2 or 3 years. > Otherwise we will never get rid of this problems. I think writing a proxy between the database and your applications may be a better solution if you only want to provide very limited access to the database.