USAGE on schema allowed by default?

From: Jochem van Dieten <jochemd(at)oli(dot)tudelft(dot)nl>
To: pgsql-general(at)postgresql(dot)org
Subject: USAGE on schema allowed by default?
Date: 2002-11-30 20:22:13
Message-ID: 3DE91DF5.3090201@oli.tudelft.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Greg Sabino Mullane 2002-11-30 21:10:39 GnuPG / PGP signed MD5 checksums for PostgreSQL 7.3
Previous Message Dan Langille 2002-11-30 20:19:09 Re: 7.4 Wishlist