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
