Creating UUID extension as the non super privileged user

Question

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.

Answer 1

I see two possibilities:

1.

Install the extension into a template database (just a regular database) and use it as template for CREATE DATABASE:

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.

2.

Create a 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.