Обсуждение: Grant CREATE privilege on all schemas

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

ivanov17@riseup.net writes:
> Is there a way to grant roles CREATE
> privileges on all schemas?

This is not supported, and it's not likely to ever become supported
in exactly the way you phrased it, because that would presumably
include CREATE on the pg_catalog schema.  If you give somebody
that, you might as well just skip the fooling around and give them
full superuser, because they could hack their way to that in less
time than it's taking me to type this email.  In general, you
want to be pretty darn chary about giving out permissions on
schemas that are likely to be in other users' search_path, for
much the same reasons that you don't give random users write
permission on /usr/bin/.

            regards, tom lane

David G. Johnston писал(а) 2023-09-12 05:42:
> 
> There are default privileges that can be setup so that when new
> objects are created existing roles are given the described grants at
> that time.
> 
> https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html
> 
> Note the absence of DATABASE as an object type on that page.

Thank you for your answer. Unfortunately, I'm not even a junior DBA, so
I might be misunderstanding something. Сan I ask more questions?

Typically, for each of my websites, I create one database, one database
owner role, and its private schema. And I want to have a role that can
to access each private schema to create tables and write data to provide
database migrations.

I can't know what schema I need to create tomorrow, so I think it's
reasonable to grant privileges to the migration role in advance. But
maybe I don't need predefined roles at all.

Now I see that I might to have another problem because tables should be
created by migration role, but after that they must be fully accessible
to the database owner. I think I also need to grant full default
privileges to the database/schema owners on all tables created by the
migration role in their own schemas, right?

Also, when creating databases/schemas, I need to give the migration role
full default privileges to use private schemas, create tables in them,
and write data. In this case, I no longer need the predefined roles.

Does this sound like a good plan? Could you please correct me if there
is a better way to do this?

> There really isn't such a thing as "feature request" here - there
> isn't anyone that really makes it a point to fulfill such requests and
> there is more than manageable work in process already.  That said,
> discussion about what PostgreSQL can and cannot do, sent to the
> -general list, do get read by many including developers.

Thank you. I thought that I missed something.

-- 
With appreciation, 
Ivanov

On Tue, 2023-09-12 at 17:38 +0000, ivanov17@riseup.net wrote:
> Thank you for your answer. Unfortunately, I'm not even a junior DBA, so
> I might be misunderstanding something. Сan I ask more questions?
>
> Typically, for each of my websites, I create one database, one database
> owner role, and its private schema. And I want to have a role that can
> to access each private schema to create tables and write data to provide
> database migrations.
>
> I can't know what schema I need to create tomorrow, so I think it's
> reasonable to grant privileges to the migration role in advance. But
> maybe I don't need predefined roles at all.
>
> Now I see that I might to have another problem because tables should be
> created by migration role, but after that they must be fully accessible
> to the database owner. I think I also need to grant full default
> privileges to the database/schema owners on all tables created by the
> migration role in their own schemas, right?
>
> Also, when creating databases/schemas, I need to give the migration role
> full default privileges to use private schemas, create tables in them,
> and write data. In this case, I no longer need the predefined roles.
>
> Does this sound like a good plan? Could you please correct me if there
> is a better way to do this?

The owner of the tables must be the user that creates them, that is your
migration role.

If the database and the schemas are owned by a different user, that does
not matter, as long as the migration user has CREATE on all schemas.

You could use ALTER DEFAULT PRIVILEGES to make sure that every schema
created by the database owner has that required permission by default.

While that should work fine, I deem it more complicated than necessary.
I would opt for the migration user being the same as the database owner.

Yours,
Laurenz Albe

Tom Lane писал(а) 2023-09-12 06:04:
> ivanov17@riseup.net writes:
>> Is there a way to grant roles CREATE
>> privileges on all schemas?
> 
> This is not supported, and it's not likely to ever become supported
> in exactly the way you phrased it, because that would presumably
> include CREATE on the pg_catalog schema.

Oh, now I understand. I think that if something like this is ever
implemented, system catalogs should not be accessible to such a role.

> If you give somebody
> that, you might as well just skip the fooling around and give them
> full superuser, because they could hack their way to that in less
> time than it's taking me to type this email.  In general, you
> want to be pretty darn chary about giving out permissions on
> schemas that are likely to be in other users' search_path, for
> much the same reasons that you don't give random users write
> permission on /usr/bin/.

Thank you very much, now it becomes clearer to me.

-- 
With appreciation, 
Ivanov

On 2023-09-12 17:59, Laurenz Albe wrote:

> The owner of the tables must be the user that creates them, that is your
> migration role.
> 
> If the database and the schemas are owned by a different user, that does
> not matter, as long as the migration user has CREATE on all schemas.
> 
> You could use ALTER DEFAULT PRIVILEGES to make sure that every schema
> created by the database owner has that required permission by default.
> 
> While that should work fine, I deem it more complicated than necessary.
> I would opt for the migration user being the same as the database owner.
> 
> Yours,
> Laurenz Albe

Thank you. I use different Ansible roles to configure CI and web
servers, so it's easier for me to create different database roles for
different purposes.

But you're right: this results in the need to assign many additional
privileges because objects in schemas are not owned by schema owners. It
works, but doesn't look very good.

I think I have found the final solution, and it even looks elegant.

    GRANT web1 TO migration;
    GRANT web2 TO migration;
    ALTER ROLE migration IN DATABASE web1 SET role TO web1;
    ALTER ROLE migration IN DATABASE web2 SET role TO web2;

Now when I connect to the database as the migration role, all operations
are performed by the database owner role, so all created objects in the
schema owned by the database owner. And no confusion with privileges.

-- 
With appreciation,
Ivanov