MIME-Version: 1.0
Date: Tue, 12 Sep 2023 01:05:30 +0000
From: ivanov17@riseup.net
To: pgsql-novice@lists.postgresql.org
Subject: Grant CREATE privilege on all schemas
Message-ID: <e7fdd3ef1f36f903aa3b13a628a88a61@riseup.net>
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/e7fdd3ef1f36f903aa3b13a628a88a61%40riseup.net>
Precedence: bulk

Hello! 

I have a small PostgreSQL 15 installation with a few databases that are
used mainly for websites. Now I'm trying to set up automatic database
migrations using CI tools. 

I would like to use a separate role that have access to all schemas in
all databases, even if they don't exist yet. But I would not like to
create a superuser for this. 

Since PostgreSQL 14, there are predefined roles pg_read_all_data and
pg_write_all_data. This is amazing and almost exactly what I need. Both
roles have USAGE rights on all schemas. But none of them have CREATE
privileges on these schemas.

So, I have two questions. Is there a way to grant roles CREATE
privileges on all schemas? I believe that partially limited permissions
are better than full permissions anyway. But I can't set permissions for
databases and schemas that haven't been created yet. Or maybe there is
another way to organize the process of migrating multiple databases?

And finally, where it would better to create a feature request? I think
that it would be great to have another predefined role like
pg_create_any_data_object that would allow creating data objects in all
databases.

Thank you.

-- 
With appreciation, 
Ivanov