USAGE on schema allowed by default?

Started by Jochem van Dietenover 23 years ago4 messagesgeneral
Jump to latest
#1Jochem van Dieten
jochemd@oli.tudelft.nl

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]http://developer.postgresql.org/docs/postgres/ddl-schemas.html 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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Jochem van Dieten (#1)
Re: USAGE on schema allowed by default?

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

#3Jochem van Dieten
jochemd@oli.tudelft.nl
In reply to: Tom Lane (#2)
Re: USAGE on schema allowed by default?

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

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: Jochem van Dieten (#3)
Re: USAGE on schema allowed by default?

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