ERROR: permission denied for language c

Question

When creating a function like this with a non-super user I am getting the error below:

ERROR: permission denied for language c SQL state: 42501

The function created is :

CREATE OR REPLACE FUNCTION dblink_connect (text)
RETURNS text
AS '$libdir/dblink','dblink_connect'
LANGUAGE C STRICT;

But if I wanted to give permission on language C to my non-super user, I am getting the error below: postgres=# grant usage on language c to caixa; ERROR: language "c" is not trusted

That means, non-super user can't create function with language C? or is there anything else I am doing wrong?

Answer 1

That's right, according to doc:

Only superusers can create functions in untrusted languages

Quick check:

SELECT lanpltrusted FROM pg_language WHERE lanname LIKE 'c';
 lanpltrusted 
--------------
 f
(1 row)

If you really want this, then you could modify pg_language system catalog (ALTER LANGUAGE doesn't have such option):

UPDATE pg_language SET lanpltrusted = true WHERE lanname LIKE 'c';

Per user @Otheus below: the UPDATE statement must be done in the DB where the function will reside.

Answer 2

Instead of setting the language to trusted which is considered bad, and dangerous, you should rather use roles to provide superuser privilege temporarily to the user during the time he manipulates the stored procedures:

as superuser:

create role dba with superuser noinherit;
grant dba to user;

then logged-in as user you can set role dba

And then you could create stored procedures in C while you temporarily have the role dba.

reset role; when you're finished to come back to normal rights.

More info here: https://dba.stackexchange.com/questions/37336/cannot-create-function-in-plpython3u-permission-denied