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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply