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;

That copies all installed extensions into the new database. Your user obviously can do that.

Also, if she created the database she may already have the necessary privileges to run CREATE EXTENSION. I quote the manual:

For most extensions this means superuser or database owner privileges are needed.

2) Create a function with SECURITY DEFINER executing all necessary DML statements, owned by a superuser. Revoke all privileges from public and grant execute to the user:

CREATE FUNCTION foo() ... SECURITY DEFINER;
REVOKE ALL ON FUNCTION foo() FROM public;
GRANT EXECUTE ON FUNCTION foo() ALL ... to my_user;

Be sure to read the chapter "Writing SECURITY DEFINER Functions Safely" in the manual.