Greetings Bene,
* Benedikt Grundmann (bgrundmann@janestreet.com) wrote:
> I'm trying to understand how to decode proacl in pg_proc. The
> documentation says:
>
> PostgreSQL grants default privileges on some types of objects to PUBLIC.
> ... EXECUTE privilege for functions; ... Also, these initial default
> privilege settings can be changed using the ALTER DEFAULT PRIVILEGES
> command.
>
> I also found this email
> <https://www.postgresql.org/message-id/7646.1357696137@sss.pgh.pa.us> by
> Tom saying that NULL means the default of execute to public.
>
> Questions:
>
> a) Does NULL mean execute to public? Or does it mean whatever
> pg_default_acl contains for functions?
NULL means that the default rights are in place. For functions that
means 'PUBLIC' has 'EXECUTE' rights on the function, yes.
> b) What does it mean if pg_default_acl is empty?
This means that no 'DEFAULT PRIVILEGES' have been set using the command
'ALTER DEFAULT PRIVILEGES'. See:
https://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html
> c) If NULL means execute to public can somebody explain why this happens:
>
> postgres_prod@proddb_testing=# select proacl from pg_proc where proname =
> 'hstore_eq';
> ─[ RECORD 1 ]
> proacl │ ¤
>
> Time: 87.862 ms
> postgres_prod@proddb_testing=# grant execute on function hstore_eq(hstore,
> hstore) to public;
> GRANT
> Time: 88.931 ms
> postgres_prod@proddb_testing=# select proacl from pg_proc where proname =
> 'hstore_eq';
> ─[ RECORD 1 ]────────────────────────────────────────────
> proacl │ *{=X/postgres_prod,postgres_prod=X/postgres_prod}*
>
> I would have expected the bold to still be NULL. Also I haven't found any
> combination of statements to set it back to NULL (short of dropping and
> recreating hstore_eq).
The act of running the GRANT causes the ACL to be set explicitly. There
is no way to set it back to NULL once it's been set (but you can
certainly set the privileges to any possible value, including one which
has the same effect as having it be set to NULL..).
> Which leads me to I guess the most important questions.
>
> d) Other than compactness in the representation of acls is there any
> practical difference between an the above representation and having NULL in
> proacl?
No.
If you're curious, check the acldefault() function. Using that you can
see what a value of 'NULL' actually means, and for a function, you'll
see it means exactly the same as the ACL above.
Thanks!
Stephen