Thread: BUG #8685: "alter default privileges" cannot revoke default execute privilege on functions

The following bug has been logged on the website:

Bug reference:      8685
Logged by:          Manuel Zahariev
Email address:      mz@alumni.sfu.ca
PostgreSQL version: 9.1.11
Operating system:   Ubuntu 13.10
Description:

ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM <role>
...does not remove default execute privileges on functions




====================
Log:


$sudo -u postgres psql
psql (9.1.11)
Type "help" for help.


postgres=# CREATE DATABASE db;
CREATE DATABASE
postgres=# CREATE ROLE u WITH PASSWORD '1234' login;
CREATE ROLE
postgres=# \c db
You are now connected to database "db" as user "postgres".
db=# ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM u;
ALTER DEFAULT PRIVILEGES
db=# CREATE FUNCTION f() RETURNS varchar AS $$
db$# SELECT 'Hello'::varchar;
db$# $$ LANGUAGE 'SQL' SECURITY DEFINER;
CREATE FUNCTION
db=# SELECT * FROM f();
   f
-------
 Hello
(1 row)


db=# \q
$psql -h localhost db u
Password for user u:
psql (9.1.11)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.


db=> SELECT * FROM f();  -- should fail
   f
-------
 Hello
(1 row)

mz wrote
> The following bug has been logged on the website:
>
> Bug reference:      8685
> Logged by:          Manuel Zahariev
> Email address:

> mz@.sfu

> PostgreSQL version: 9.1.11
> Operating system:   Ubuntu 13.10
> Description:
>
> ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM
> <role>
> ...does not remove default execute privileges on functions
>
> db=# ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM u;

"u" inherits its ability to execute functions from PUBLIC.  With grants you
do not tell the system what not to do (i.e., it does not block already
granted permissions) but only what to allow.  In order to revoke execute on
functions globally you must remove the corresponding GRANT from PUBLIC.
Revoking only removes a previously applied GRANT.  In the case of default
privileges it only revokes previously established default grants.

From the documentation:

http://www.postgresql.org/docs/9.3/interactive/sql-alterdefaultprivileges.html

ALTER DEFAULT PRIVILEGES [FOR ROLE xxx] REVOKE EXECUTE ON FUNCTIONS FROM
PUBLIC;

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-8685-alter-default-privileges-cannot-revoke-default-execute-privilege-on-functions-tp5783941p5783947.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.