partially effective revoke on pg_catalog

Started by hubert depesz lubaczewskiover 18 years ago5 messagesbugs
Jump to latest

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

(depesz@[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@[local]:5830) 14:23:55 [depesz]

\d

ERROR: permission denied for schema pg_catalog
(test@[local]:5830) 14:23:57 [depesz]

select count(*) from pg_tables;

count
-------
48
(1 row)

(test@[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@[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)

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: hubert depesz lubaczewski (#1)
Re: partially effective revoke on pg_catalog

hubert depesz lubaczewski <depesz@depesz.com> writes:

# revoke usage on schema pg_catalog from public;
REVOKE

This is not a supported operation.

regards, tom lane

In reply to: Tom Lane (#2)
Re: partially effective revoke on pg_catalog

On Mon, Sep 10, 2007 at 10:38:34AM -0400, Tom Lane wrote:

hubert depesz lubaczewski <depesz@depesz.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)

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: hubert depesz lubaczewski (#3)
Re: partially effective revoke on pg_catalog

hubert depesz lubaczewski <depesz@depesz.com> writes:

On Mon, Sep 10, 2007 at 10:38:34AM -0400, Tom Lane wrote:

hubert depesz lubaczewski <depesz@depesz.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

In reply to: Tom Lane (#4)
Re: partially effective revoke on pg_catalog

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)