Обсуждение: USAGE on schema allowed by default?

Hi all,

I am having a problem with USAGE. If I create a schema, users other than
the owner can enumerate tables in that schema. It is my understanding
from the documentation [1] that by default this should not be possible.
Personally I would not consider this a security risk, but some customers
might feel uncomfortable with this. Is there anything I can do to revoke
USAGE priviledges on the schema by default?

Below is the transcript of what I did to test this. (It was done with a
psql.exe for PostgreSQL 7.2, if somebody can point me to a download
location for a psql.exe + libpq.dll for PostgreSQL 7.3 I would be most
happy, Cygwin appears to be on 7.2.3)

Jochem



 From other connection:
test=# CREATE USER testuser1 UNENCRYPTED PASSWORD 'testuser1';
CREATE USER
test=# CREATE USER testuser2 UNENCRYPTED PASSWORD 'testuser2';
CREATE USER
test=# CREATE DATABASE testdb;
CREATE DATABASE

C:\PROGRA~1\psql>psql -U superuser testdb
Password: password
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
        \h for help with SQL commands
        \? for help on internal slash commands
        \g or terminate with semicolon to execute query
        \q to quit

testdb=# drop schema public;
DROP SCHEMA
testdb=# create schema testuser1 authorization testuser1;
CREATE SCHEMA
testdb=# create schema testuser2 authorization testuser2;
CREATE SCHEMA
testdb=# \q

C:\PROGRA~1\psql>psql -U testuser1 testdb
Password: testuser1
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
        \h for help with SQL commands
        \? for help on internal slash commands
        \g or terminate with semicolon to execute query
        \q to quit

testdb=> create table testuser1.testtable (ID INTEGER);
CREATE TABLE
testdb=> \q

C:\PROGRA~1\psql>psql -U testuser2 testdb
Password: testuser2
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
        \h for help with SQL commands
        \? for help on internal slash commands
        \g or terminate with semicolon to execute query
        \q to quit

testdb=> \d
        List of relations
    Name    | Type  |   Owner
-----------+-------+-----------
  testtable | table | testuser1
(1 row)


testdb=> select * from testtable;
ERROR:  Relation "testtable" does not exist
testdb=> select * from testuser1.testtable;
ERROR:  testuser1: permission denied
testdb=> \q

[1] http://developer.postgresql.org/docs/postgres/ddl-schemas.html

Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
> I am having a problem with USAGE. If I create a schema, users other than
> the owner can enumerate tables in that schema.

This has nothing to do with USAGE on the schema; it is just a matter of
being able to read the system catalogs.  The only way we could prevent
it would be to disallow unprivileged users from reading pg_class; which
would break enough things that it seems unattractive.

            regards, tom lane

Tom Lane wrote:
> Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
>
>>I am having a problem with USAGE. If I create a schema, users other than
>>the owner can enumerate tables in that schema.
>
> This has nothing to do with USAGE on the schema; it is just a matter of
> being able to read the system catalogs.

<quote>
2.8.4. Schemas and Privileges

By default, users cannot see the objects in schemas they do not own.
</quote>

I seem to have misunderstood the meaning of object (the current wording
suggests to me that tables, views, sequences etc. are not just
unreadable, but also invisible). Is there an explanation of "objects"
somewhere?

Jochem

Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
> Tom Lane wrote:
>> This has nothing to do with USAGE on the schema; it is just a matter of
>> being able to read the system catalogs.

> <quote>
> By default, users cannot see the objects in schemas they do not own.
> </quote>

Perhaps "access" would be a better verb than "see" here.  You cannot
actually *do* anything with objects that live in a schema you don't
have USAGE on.  (If you find any holes in that statement, let me know.)
You can, however, find out their names and other properties by examining
the system catalogs.

I agree that this isn't completely ideal, but I stand by my comment that
it's not worth breaking every client that looks at system catalogs in
order to prevent it.

            regards, tom lane

> Date: Sat, 30 Nov 2002 23:14:43 -0500
> From: Tom Lane <tgl@sss.pgh.pa.us>
>
> Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
> > Tom Lane wrote:
> >> This has nothing to do with USAGE on the schema; it is just a matter of
> >> being able to read the system catalogs.
>
> > <quote>
> > By default, users cannot see the objects in schemas they do not own.
> > </quote>
>
>
> I agree that this isn't completely ideal, but I stand by my comment that
> it's not worth breaking every client that looks at system catalogs in
> order to prevent it.
>
What about the following scenario:

 - move the information in pg_class etc. to new tables pg_dba_class etc.
   to which only DBAs have access

 - redefine pg_class etc. as views which contain only the information the specific
   user has right to see

This mimics the way Oracle's data dictionary works and yet would not break existing
clients because the objects pg_class etc. still exist (though containing less data
for less privileged users).

Christoph Dalitz

Christoph Dalitz <christoph.dalitz@hs-niederrhein.de> writes:
>> I agree that this isn't completely ideal, but I stand by my comment that
>> it's not worth breaking every client that looks at system catalogs in
>> order to prevent it.
>>
> What about [ hiding the real catalogs behind views ]

A good solution would need more than that.  For example, something I've
heard repeatedly is that people would like to hide the source code of
their SQL or PLxxx functions from users who are nonetheless allowed to
call those functions.  A row-wise selective view of pg_proc can't fix
that.  In many cases it's less than clear which rows of which catalogs
to hide anyway.

Ultimately, if you don't want other people to see any of your catalog
information, you shouldn't let 'em into your database.  There's still
the option of setting up distinct databases.

            regards, tom lane

Tom Lane wrote:
> For example, something I've heard repeatedly is that people would like to
> hide the source code of their SQL or PLxxx functions from users who are
> nonetheless allowed to call those functions.  A row-wise selective view of
> pg_proc can't fix that.  In many cases it's less than clear which rows of
> which catalogs to hide anyway.

It wouldn't be terribly difficult to encrypt prosrc with 3des (or maybe aes)
using the owner's passwd from pg_shadow. We would need a new bool column in
pg_proc (proisencrypted?) and some logic in fmgr.c.

Is there sufficient interest to justify the effort?

Joe