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
Attachments:
=?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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 publicI'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 the public schema.
Thanks!
François
Attachments:
=?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 the public 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general