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?
14 Answers
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.
2Instead 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.
In my case for uuid functions in RDS postgres 12.5. All I had to do is:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp';Probably my answer will be useful for someone who is working with the relevant topic.
During DB movement from one project to another I was trying to re-create\recover a function uuid_generate_v1 in the GCP Postres installation:
CREATE OR REPLACE FUNCTION public.uuid_generate_v1() RETURNS uuid LANGUAGE c PARALLEL SAFE STRICT
AS '$libdir/uuid-ossp', $function$uuid_generate_v1$function$;which failed with the same error as in the question ERROR: language "c" is not trusted.
I was not able to use any of the provided answers, because GCP doesn't provide superuser access to the Postgres. But I found a workaround, which could be helpful for somebody.
Appeared that this function as-well as bunch of others (uuid_generate_v1mc, uuid_generate_v3, uuid_generate_v4, uuid_generate_v5, uuid_nil, uuid_ns_dns, uuid_ns_oid, uuid_ns_url, uuid_ns_x500), comes with uuid-ossp extension. So what I did I just create an extension which brought all necessary functions.
CREATE EXTENSION "uuid-ossp";
