Обсуждение: granting right to create and delete just one database

Hi All,

Is there any way to grant rights to a user such that they can drop and 
re-create only a single database?

cheers,

Chris

Chris Withers wrote:
> Is there any way to grant rights to a user such that they can drop and 
> re-create only a single database?

No; what I'd do if I needed that is to create a SECURITY DEFINER function
that is owned by a user with the CREATEDB privilege.
This function can be called by a normal user that has the EXECUTE privilege
on the function.

Don't forget to "SET search_path" on such a function (as mentioned in the
documentation).  It might also be a good idea to REVOKE EXECUTE on the
function from PUBLIC.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

On 05/06/2019 09:52, Laurenz Albe wrote:
> Chris Withers wrote:
>> Is there any way to grant rights to a user such that they can drop and
>> re-create only a single database?
> No; what I'd do if I needed that is to create a SECURITY DEFINER function
> that is owned by a user with the CREATEDB privilege.
> This function can be called by a normal user that has the EXECUTE privilege
> on the function.
>
> Don't forget to "SET search_path" on such a function (as mentioned in the
> documentation).  It might also be a good idea to REVOKE EXECUTE on the
> function from PUBLIC.
Thanks, that's a great idea! Is this pattern documented anywhere as a 
complete finished thing?

cheers,

Chris

Chris Withers wrote:
> > > Is there any way to grant rights to a user such that they can drop and
> > > re-create only a single database?
> > No; what I'd do if I needed that is to create a SECURITY DEFINER function
> > that is owned by a user with the CREATEDB privilege.
>
> Thanks, that's a great idea! Is this pattern documented anywhere as a 
> complete finished thing?

I'm afraid that is left as an exercise to the reader.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com