Waiting for PostgreSQL 14 – Add “pg_database_owner” default role.

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;

And redo the test in test database:

=$ FOR u IN test test2
    echo "===== user: $u ====="
    psql -d test -U $u -X -c 'select * from get_running_queries()'
===== 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.