partially effective revoke on pg_catalog

user depesz is superuser. i connect to depesz database, and:

(depesz(at)[local]:5830) 14:20:34 [depesz]
# revoke usage on schema pg_catalog from public;
REVOKE

now, i reconnect to the same database with test user (which is not
superuser):

(test(at)[local]:5830) 14:23:55 [depesz]
> \d
ERROR: permission denied for schema pg_catalog
(test(at)[local]:5830) 14:23:57 [depesz]
> select count(*) from pg_tables;
count
-------
48
(1 row)

(test(at)[local]:5830) 14:23:59 [depesz]
> select count(*) from pg_catalog.pg_tables;
ERROR: permission denied for schema pg_catalog

something looks weird here.

search_path is default:

(test(at)[local]:5830) 14:24:03 [depesz]
> show search_path;
search_path
----------------
"$user",public
(1 row)

pg version - 8.3devel from cvs.

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

hubert depesz lubaczewski <depesz(at)depesz(dot)com> writes:
> # revoke usage on schema pg_catalog from public;
> REVOKE

This is not a supported operation.

regards, tom lane

On Mon, Sep 10, 2007 at 10:38:34AM -0400, Tom Lane wrote:
> hubert depesz lubaczewski <depesz(at)depesz(dot)com> writes:
> > # revoke usage on schema pg_catalog from public;
> > REVOKE
> This is not a supported operation.

ok, but i belive it should either dont allow admin to do so, or, if it
does allow, it should behave more consistently.

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

hubert depesz lubaczewski <depesz(at)depesz(dot)com> writes:
> On Mon, Sep 10, 2007 at 10:38:34AM -0400, Tom Lane wrote:
>> hubert depesz lubaczewski <depesz(at)depesz(dot)com> writes:
>>> # revoke usage on schema pg_catalog from public;
>>> REVOKE
>> This is not a supported operation.

> ok, but i belive it should either dont allow admin to do so, or, if it
> does allow, it should behave more consistently.

There are few "training wheels" for superuser mode. Try something like
"delete from pg_proc" if you are looking for ways to break your
database.

regards, tom lane

On Mon, Sep 10, 2007 at 11:17:21AM -0400, Tom Lane wrote:
> > ok, but i belive it should either dont allow admin to do so, or, if it
> > does allow, it should behave more consistently.
> There are few "training wheels" for superuser mode. Try something like
> "delete from pg_proc" if you are looking for ways to break your
> database.

i'm perfectly fine with "revoke from pg_catalog" not working/not
allowed, but dont you think that the outcome should be a bit more
consistent?

if it would "break the database" - i'm happy with it.
if it will reject hhe command as "it is not possible" - i'm happy with
it.

but now postgresql raports to user that revoke worked. and at first
sight it actually does seem like it.
but a second check showes that the revoke is not really 100% effective.

again - i'm in no position to ask to give the ability to revoke the
privileges. all i'm asking is to put some consistency - either break it,
or forbid. but dont say "revoked" when it's not really true.

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)