Обсуждение: 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.