I need to load the uuid extension on my database, which is being created in runtime. The user that created the db is not superuser, she only has the "CREATEDB" permissions.
Is there a way to do this? Thanks.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
I see two possibilities:
Install the extension into a template database (just a regular database) and use it as template for
CREATE DATABASE new_db TEMPLATE my_template_db;
That copies all installed extensions into the new database. Your user obviously can do that.
Also, if she created the database she should have the necessary privileges to run
CREATE EXTENSION anyway. I quote the manual:
For most extensions this means superuser or database owner privileges are needed.
SECURITY DEFINER function executing all necessary DML statements, owned by a superuser. Revoke all privileges from
public and grant
EXECUTE to the user role:
CREATE FUNCTION foo() ... SECURITY DEFINER; REVOKE ALL ON FUNCTION foo() FROM public; GRANT EXECUTE ON FUNCTION foo() to my_user;
Read the chapter "Writing SECURITY DEFINER Functions Safely" in the manual.