RE: Remove default privilege from DB

After that:

What are they?

I am a bit puzzled. I checked the documentation and execute on function is the hard wired default privilege.

https://www.postgresql.org/docs/current/static/sql-grant.html

PostgreSQL grants default privileges on some types of objects to PUBLIC. No privileges are granted to PUBLIC by default on tables, table columns, sequences, foreign data wrappers, foreign servers, large objects, schemas, or tablespaces. For other types of objects, the default privileges granted to PUBLIC are as follows: CONNECT and TEMPORARY (create temporary tables) privileges for databases; EXECUTE privilege for functions; and USAGE privilege for languages and data types (including domains). The object owner can, of course, REVOKE both default and expressly granted privileges.

So after revoking it from public you should actually get an ACL like {suser=X/suser} and the entry for the grants should make it disapper.

Example:

charles@db.localhost=# \ddp
Default access privileges

Owner | Schema | Type | Access privileges

-------+--------+------+-------------------

(0 rows)

charles@db.localhost=# select * from pg_default_acl;

defaclrole | defaclnamespace | defaclobjtype | defaclacl

------------+-----------------+---------------+-----------

(0 rows)

charles@db.localhost=# ALTER DEFAULT PRIVILEGES FOR ROLE charlesc REVOKE EXECUTE ON FUNCTIONS FROM public;

ALTER DEFAULT PRIVILEGES

charles@db.localhost=# \ddp

             Default access privileges

  Owner   | Schema |   Type   |  Access privileges

----------+--------+----------+---------------------

charlesc |        | function | charlesc=X/charlesc

(1 row)

Now only user charlesc can execute (new) functions created by himself. This is the most typical use case when restricting access to self-made functions.

charles@db.localhost=# select * from pg_default_acl;

defaclrole | defaclnamespace | defaclobjtype |       defaclacl

------------+-----------------+---------------+-----------------------

   25269137 |               0 | f             | {charlesc=X/charlesc}

(1 row)

charles@db.localhost=# ALTER DEFAULT PRIVILEGES FOR ROLE charlesc GRANT EXECUTE ON FUNCTIONS TO public;

ALTER DEFAULT PRIVILEGES

charles@db.localhost=# \ddp

         Default access privileges

Owner | Schema | Type | Access privileges

-------+--------+------+-------------------

(0 rows)

charles@db.localhost=# select * from pg_default_acl;

defaclrole | defaclnamespace | defaclobjtype | defaclacl

------------+-----------------+---------------+-----------

(0 rows)

Now again. everybody can execute functions created by charlesc.

What version of PostgreSQL are you using?
And how did you get those first entries at all?

What happens if you issue

ALTER DEFAULT PRIVILEGES FOR ROLE suser GRANT EXECUTE ON FUNCTIONS TO PUBLIC;

again?

Regards
Charles

select * from pg_default_acl

24629 = suser | namespace 0 = none in document

Hmmm... It's very strange for me. I don't find any point which links this user to this database.

Do you have any idea?

Thanks

  dd