No longer installable by non-superuser

[spencerbryson]

Version 1.3.1 introduced a language c function parse_type (see here).

The control file still defines superuser as false (see here). However, since this recent inclusion it is no longer possible to create the extension as a non-superuser.

Repro steps on 1.3.1:

postgres=> \c postgres postgres 
You are now connected to database "postgres" as user "postgres".

postgres=# GRANT ALL ON SCHEMA public TO alice;
GRANT

postgres=# \c postgres alice
You are now connected to database "postgres" as user "alice".

postgres=> CREATE EXTENSION pgtap;
ERROR:  permission denied for language c

[theory]

Ugh. I sure would like to do away with the C code again, but it really was the best way to fix #315. Need to give this some thought.

[spencerbryson]

It isn't fully clear when this is appropriate to use, but looking at the C function is it safe enough to consider marking the extension as 'trusted'? Could be the least amount of friction... something to consider.

For reference: https://www.postgresql.org/about/featurematrix/detail/347/

[ewie]

The extension must be marked with superuser=true (which we forgot in #315) because C is an untrusted language. When also setting trusted=true all the extension scripts and functions must follow a secure design. So it's not just the C function that needs to be checked here.

[ewie]

But I think trusted=true is not advisable if pgTAP relies on the search path as David explained: #325 (comment)

[theory]

I'm sorely tempted to dump the C code and replace it with your POC, @ewie. And also submitting a patch to get parseTypeString() exposed in SQL in core.

[ewie]

I'm sorely tempted to dump the C code and replace it with your POC, @​ewie.

Your call, but it does seem like the best choice here.

And also submitting a patch to get parseTypeString() exposed in SQL in core.

Yeah, I was thinking about that too the last few days. You may still get it into pg17 with commitfest 2024-03 before feature freeze. Pre-17 releases should be stable enough for my PoC to cover all types (interval still todo).

[theory]

I can take a stab at it, but I can't tell where the SQL function is created; it's not like extensions with CREATE FUNCTION statements.

[theory]

Started a patch in theory/postgres#1.

[ewie]

I can take a stab at it, but I can't tell where the SQL function is created; it's not like extensions with CREATE FUNCTION statements.

Builtin functions are defined in src/include/catalog/pg_proc.dat. Picking an OID is described in
https://www.postgresql.org/docs/current/system-catalog-initial-data.html.