On 26th of March 2021, Noah Misch committed patch:
Add "pg_database_owner" default role. Membership consists, implicitly, of the current database owner. Expect use in template databases. Once pg_database_owner has rights within a template, each owner of a database instantiated from that template will exercise those rights. Reviewed by John Naylor. Discussion: https://postgr.es/m/20201228043148.GA1053024@rfd.leadboat.com
This will definitely come in handy.
As you perhaps know, whenever you create something in template1 database, when you will make new database (using default options) – it will get copy of whatever is in template1.
For example, one might add some monitoring functions, or anything like this.
Let's see how that works.
I have 3 users, depesz (which is superuser), test, and test2).
As test or test2, when they query pg_stat_activity, in query column they see generally “insufficient privilege".
Now, I logged to template1 as depesz, and issued:
=$ CREATE FUNCTION get_running_queries() RETURNS TABLE (pid int4, query text) AS $$ SELECT pid, query FROM pg_stat_activity WHERE backend_type = 'client backend'; $$ LANGUAGE SQL security definer; =$ REVOKE ALL ON FUNCTION get_running_queries() FROM public; =$ GRANT EXECUTE ON FUNCTION get_running_queries() TO pg_database_owner;
Now, when I connect to template1 as test or test2, and try to use this function, I'll get:
=$ FOR u IN test test2; do psql -d template1 -U $u -X -c 'select * from get_running_queries()'; done ERROR: permission denied FOR FUNCTION get_running_queries ERROR: permission denied FOR FUNCTION get_running_queries
But when I'll make db with test as owner:
$ CREATE DATABASE test WITH owner test; CREATE DATABASE
And redo the test in test database:
=$ FOR u IN test test2 do echo "===== user: $u =====" psql -d test -U $u -X -c 'select * from get_running_queries()' echo done ===== USER: test ===== pid | query --------+--------------------------------------- 174949 | SELECT * FROM get_running_queries() 169886 | SELECT pg_sleep(1000000); 170124 | SELECT * FROM pg_stat_activity ; 170312 | CREATE DATABASE test WITH owner test; (4 ROWS) ===== USER: test2 ===== ERROR: permission denied FOR FUNCTION get_running_queries
Of course, this example is pretty simplistic, but I hope it shows what can be done.
It allows easy creation of things that will be available to db owners, and not to any other users, without having to manually grant privileges in every new database.
Great stuff, thanks to all involved.