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]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
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