Use a postgres extension as a non-privileged user

user_3073859 0
Viewed 139

The Problem

I have the following trigger function that uses the moddatetime extension:

/* BEFORE UPDATE trigger - Updates "entity_version.updated_on", setting it to the current UTC time. */
CREATE EXTENSION IF NOT EXISTS moddatetime;  -- Needs superuser privileges!

DROP TRIGGER IF EXISTS trig_entity_version_before_update
ON entity_version;

CREATE TRIGGER trig_entity_version_before_update
    BEFORE UPDATE
    ON entity_version
    FOR EACH ROW EXECUTE PROCEDURE moddatetime(updated_on);  -- updated_on is TIMESTAMP type

The trigger works perfectly fine, but the issue is that the first line (CREATE EXTENSION) requires super-user privileges. Since these databases are going to be created by users (via a script) I don't want them the user that makes these databases and triggers to have super user access.

What I've tried

As a first step, running the script as a super user works fine, as you'd expect.

Naturally, the next step would be to separate the creation of the extension as a superuser from the trigger creation script. But doing that, if I run the above script without CREATE EXTENSION line, I get the following error:

function moddatetime() does not exist

Which I suppose makes sense given that the script never declares moddatetime, but I cannot find anywhere in the documentation how to define the extension as available without using CREATE EXTENSION. Surely there must be a way to import or use an extension without having the create it? Something akin to:

USING EXTENSION moddatetime;  -- Does something like this exist?

... rest of the trigger script ...
postgresql-11 postgresql
2 Answers

You need the extension installed if you want to use it.

PostgreSQL v13 introduces the notion of a trusted extension, which can be installed by non-superusers with the CREATE privilege on the database, but this extension is not among them. So upgrading won't fix the problem.

You could connect to template1 as superuser and create the extension there. Then it would automatically be present in all newly created databases.

But frankly, I don't see the point. You can do the same thing with a regular PL/pgSQL trigger – only the performance wouldn't be as good.

user_64643080

As per @laurenz-albe his suggestion, I've just stepped off of using the extension for this particular use-case. Fortunately, writing a function to do what the extension does is relatively trivial. I'll include it here for reference in case someone is looking specifically to replace moddatetime.

DROP FUNCTION IF EXISTS fn_entity_version_before_update() CASCADE;

CREATE FUNCTION fn_entity_version_before_update() RETURNS TRIGGER LANGUAGE PLPGSQL AS
$fn_body$
BEGIN
    NEW.updated_on = NOW() AT TIME ZONE 'utc';
    RETURN NEW;
END;
$fn_body$;

DROP TRIGGER IF EXISTS trig_entity_version_before_update
ON entity_version;

CREATE TRIGGER trig_entity_version_before_update
    BEFORE UPDATE
    ON entity_version
    FOR EACH ROW EXECUTE PROCEDURE fn_entity_version_before_update();
user_30738590
Related