Thread: User postgres unable to revoke privileges?

Hi all!

I would like to remove the second line from default privileges, because dataanalysts can't create new tables in public
anyway:

# psql -U postgres
psql (9.1.9)
Type "help" for help.

regress=# \ddp
                        Default access privileges
    Owner     |    Schema    | Type  |         Access privileges
--------------+--------------+-------+-----------------------------------
 dataanalysts | dataanalysts | table | dataanalysts=arwdDxt/dataanalysts
 dataanalysts | public       | table | dataanalysts=r/dataanalysts
 svanalytics  | public       | table | dataanalysts=r/svanalytics
(3 rows)

regress=# ALTER DEFAULT PRIVILEGES FOR ROLE dataanalysts IN SCHEMA public REVOKE SELECT ON TABLES FROM dataanalysts;
ERROR:  permission denied for schema public

I'm logged in as postgres, the database superuser. Why am I getting a permission denied?

Thanks!
François Beausoleil

=?iso-8859-1?Q?Fran=E7ois_Beausoleil?= <francois@teksol.info> writes:
> regress=# ALTER DEFAULT PRIVILEGES FOR ROLE dataanalysts IN SCHEMA public REVOKE SELECT ON TABLES FROM dataanalysts;
> ERROR:  permission denied for schema public

> I'm logged in as postgres, the database superuser. Why am I getting a permission denied?

I suspect you already revoked public CREATE privilege in schema public.
Note where the fine manual says:

schema_name
    The name of an existing schema. Each target_role must have CREATE privileges for each specified schema.

There was some debate previously about whether that restriction was a
good idea at all; and given this example, it seems like we definitely
shouldn't require it during a REVOKE.

            regards, tom lane

Le 2013-06-06 à 17:59, Tom Lane a écrit :

> =?iso-8859-1?Q?Fran=E7ois_Beausoleil?= <francois@teksol.info> writes:
>> regress=# ALTER DEFAULT PRIVILEGES FOR ROLE dataanalysts IN SCHEMA public REVOKE SELECT ON TABLES FROM dataanalysts;
>> ERROR:  permission denied for schema public
>
>> I'm logged in as postgres, the database superuser. Why am I getting a permission denied?
>
> I suspect you already revoked public CREATE privilege in schema public.

Ha, yes, you are right.

> Note where the fine manual says:
>
> schema_name
>     The name of an existing schema. Each target_role must have CREATE privileges for each specified schema.
>
> There was some debate previously about whether that restriction was a
> good idea at all; and given this example, it seems like we definitely
> shouldn't require it during a REVOKE.

I may not have read that section carefully enough. I'll try again, by adding postgres back with create privileges on
thepublic schema. 

Thanks!
François

=?iso-8859-1?Q?Fran=E7ois_Beausoleil?= <francois@teksol.info> writes:
> Le 2013-06-06 � 17:59, Tom Lane a �crit :
>> Note where the fine manual says:
>>
>> schema_name
>> The name of an existing schema. Each target_role must have CREATE privileges for each specified schema.
>>
>> There was some debate previously about whether that restriction was a
>> good idea at all; and given this example, it seems like we definitely
>> shouldn't require it during a REVOKE.

> I may not have read that section carefully enough. I'll try again, by adding postgres back with create privileges on
thepublic schema. 

FYI, after some further discussion on pgsql-hackers we've decided to
drop this permission check altogether.  Future PG releases won't behave
this way, so there won't be any ordering dependency between doing ALTER
DEFAULT PRIVILEGES and doing GRANT/REVOKE CREATE ON SCHEMA.

            regards, tom lane