Thread: Weird Grant/Revoke/Usage behavior

Weird Grant/Revoke/Usage behavior

From
"Joshua D. Drake"
Date:
Hello,

The below seems incorrect. If I am in the schema the behavior seems 
correct. I can't see or select from the table.
However if I am not in the schema I am able to see the table and its 
structure. The user jd is not a superuser.

cleancontact=# revoke usage on schema financials from jd;
REVOKE
cleancontact=# \c cleancontact jd
You are now connected to database "cleancontact" as user "jd".
cleancontact=> \d financials.foo                         Table "financials.foo"Column |  Type  |
Modifiers
--------+--------+---------------------------------------------------------id     | bigint | not null default
nextval('financials.foo_id_seq'::text)fname | text   |
 
Indexes:   "foo_pkey" PRIMARY KEY, btree (id)

cleancontact=> set search_path='financials';
SET
cleancontact=> \d
No relations found.
cleancontact=> \d foo
Did not find any relation named "foo".
cleancontact=>



Re: Weird Grant/Revoke/Usage behavior

From
Bruce Momjian
Date:
Can someone comment on this?

---------------------------------------------------------------------------

Joshua D. Drake wrote:
> Hello,
> 
> The below seems incorrect. If I am in the schema the behavior seems 
> correct. I can't see or select from the table.
> However if I am not in the schema I am able to see the table and its 
> structure. The user jd is not a superuser.
> 
> cleancontact=# revoke usage on schema financials from jd;
> REVOKE
> cleancontact=# \c cleancontact jd
> You are now connected to database "cleancontact" as user "jd".
> cleancontact=> \d financials.foo
>                           Table "financials.foo"
>  Column |  Type  |                        Modifiers
> --------+--------+---------------------------------------------------------
>  id     | bigint | not null default nextval('financials.foo_id_seq'::text)
>  fname  | text   |
> Indexes:
>     "foo_pkey" PRIMARY KEY, btree (id)
> 
> cleancontact=> set search_path='financials';
> SET
> cleancontact=> \d
> No relations found.
> cleancontact=> \d foo
> Did not find any relation named "foo".
> cleancontact=>
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Weird Grant/Revoke/Usage behavior

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Can someone comment on this?

It's operating as designed.  Schemas you don't have USAGE privilege on
are ignored if listed in your search path.
        regards, tom lane