PostgreSQL – Cannot Revoke Default Privileges from User

permissionspostgresqlpsql

I am using the psql tool:

Command:

my_db=# \ddp

Result:

                         Default access privileges
     Owner      | Schema |   Type   |          Access privileges
----------------+--------+----------+--------------------------------------
 postgres       | kpi    | function | kpi_updater=X/postgres              +
                |        |          | intranet2=X/postgres
 postgres       | kpi    | sequence | kpi_updater=rwU/postgres            +
                |        |          | intranet2=rwU/postgres
 postgres       | kpi    | table    | kpi_updater=arwdDxt/postgres        +
                |        |          | intranet2=arwdDxt/postgres
 postgres       | kpi    | type     | kpi_updater=U/postgres              +
                |        |          | intranet2=U/postgres

Next I use the following command, that returns without error:

ALTER DEFAULT PRIVILEGES IN SCHEMA kpi REVOKE EXECUTE ON FUNCTIONS FROM intranet2;

I run \ddp one more time and… I get exactly the same result: default privileges were not modified.

What am I missing here ?

Thanks !

Best Answer

Edited to answer the question related to the \ddp command not the \dp command as @personne3000 pointed out in the comment below.

You probably want to use ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA kpi REVOKE EXECUTE ON FUNCTIONS FROM intranet2;

This is because postgres is the user that was granted the default privilege of execute on the functions in the schema kpi and is granting it to intranet2, as noted by the permissions of intranet2=X/postgres

I created a small example to illustrate what's going on.

pgsql@[local]:5432:pgsql:=# CREATE ROLE bob;
CREATE ROLE
Time: 0.526 ms
pgsql@[local]:5432:pgsql:=# CREATE SCHEMA we_like_bob;
CREATE SCHEMA
Time: 0.608 ms

pgsql@[local]:5432:pgsql:=# ALTER DEFAULT PRIVILEGES FOR ROLE bob IN SCHEMA we_like_bob GRANT EXECUTE ON FUNCTIONS TO pgsql;
ALTER DEFAULT PRIVILEGES
Time: 1.480 ms
pgsql@[local]:5432:pgsql:=# \ddp
             Default access privileges
 Owner |   Schema    |   Type   | Access privileges 
-------+-------------+----------+-------------------
 bob   | we_like_bob | function | pgsql=X/bob
(1 row)

User pgsql has the execute privilege, X, granted by bob.

Try to revoke the execute privilege for the role pgsql, as pgsql (a superuser).

pgsql@[local]:5432:pgsql:=# ALTER DEFAULT PRIVILEGES FOR ROLE pgsql IN SCHEMA we_like_bob REVOKE EXECUTE ON FUNCTIONS FROM pgsql;
ALTER DEFAULT PRIVILEGES
Time: 0.176 ms
pgsql@[local]:5432:pgsql:=# \ddp
             Default access privileges
 Owner |   Schema    |   Type   | Access privileges 
-------+-------------+----------+-------------------
 bob   | we_like_bob | function | pgsql=X/bob
(1 row)

Didn't work because the command is not removing the bob role's default ability to grant execute on functions to role pgsql, it is removing the pgsql role's ability to revoke execute on functions from pgsql.

If we change it to remove the privilege of execute from the bob role for the role pgsql, then it works.

pgsql@[local]:5432:pgsql:=# ALTER DEFAULT PRIVILEGES FOR ROLE bob IN SCHEMA we_like_bob REVOKE EXECUTE ON FUNCTIONS FROM pgsql;
ALTER DEFAULT PRIVILEGES
Time: 0.644 ms
pgsql@[local]:5432:pgsql:=# \ddp
         Default access privileges
 Owner | Schema | Type | Access privileges 
-------+--------+------+-------------------
(0 rows)

pgsql@[local]:5432:pgsql:=#
Related Question