Thread: 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