Postgres allow admin role to grant permissions for users on any database

Question

I've a User role "joe" in postgresql 14 that should act as admin without having superuser role.

postgres=# \du+
                                                                        List of roles
   Role name    |                         Attributes                         |              Member of               |              Description               
----------------+------------------------------------------------------------+--------------------------------------+----------------------------------------
 joe         | Create role, Create DB, Bypass RLS                         | {pg_read_all_data,pg_write_all_data} | 

Now "joe" creates a new database and needs to grant following permissions to "alice":

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA PUBLIC TO alice;
WARNING:  no privileges were granted for "spatial_ref_sys"
WARNING:  no privileges were granted for "geography_columns"
WARNING:  no privileges were granted for "geometry_columns"

The problem here might be that the schema is owned by user "postgres" who installed postgis in template1.

Is it possible to give Joe permissions to grant all kind of privileges on every database on every user without making him a superuser even if the schema is not owned by him?

Answer 1

No, that is not possible: only the owner of an object (or somebody who got the privilege gragted WITH GRANT OPTION) can do that.

But this is not necessary: these three objects belong to the PostGIS extension, and you should leave them alone anyway. So simply ignore these warnings.

Answer 2

If you grant 'joe' the role of the user 'example_user' that owns the schema, then 'joe' should be able to

SET ROLE TO example_user;

and, in that role, be able to do everything 'example_user' can do.

(Note: "able to do everything" is signficant - any privileges 'example_user' has now become available to Joe. But, he's not a superuser. :-)

---

EDIT: Well, the caveat here is that if you're talking about the schema public (as you are), and it's owned by 'postgres' (by default), then granting 'joe' that role does make him a superuser. I don't have admin access to a db right now, but I believe that if you ...

• make a role of, say, 'public_admin', with appropriate privileges
• grant public_admin to postgres
• grant public_admin to joe
• alter schema public owner to public_admin

Then, 'joe' should be able to administer the public schema without have to set the role to postgres. 'postgres' probably doesn't need to have explicit membership in 'public_admin', since it is a superuser role, but it makes it clear that you still expect postgres to be able administer public as well.

^{I'm not putting those four steps in CAPS since I'm not intending for that to be proper syntax. Should be close, though.}