Hi From: Durumdara [mailto:durumdara@xxxxxxxxx] Dear Charles! 2018-02-12 10:03 GMT+01:00 Charles Clavadetscher <clavadetscher@xxxxxxxxxxxx>:
After that: Default access privileges Owner | Schema | Type | Access privileges ------------+--------+----------+------------------- suser | | function | suser | | sequence | suser | | table | suser | | type | (4 rows)
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 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? What happens if you issue ALTER DEFAULT PRIVILEGES FOR ROLE suser GRANT EXECUTE ON FUNCTIONS TO PUBLIC; again? Regards select * from pg_default_acl 24629;0;"r";"{}" 24629;0;"S";"{}" 24629;0;"f";"{}" 24629;0;"T";"{}" 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 |
