Create custom base types in a trusted language

[formalLogicGirl]

Describe the problem

Today extension developers can create custom types in community Postgres. Creating custom types this way requires use of C to implement some functions required for a custom type. However, C is not a trusted language, and pg_tle restricts its users to trusted languages only for implementing extensions. So pg_tle users can not implement custom types.

Describe the proposal

We propose a design enabling users to create custom types via pg_tle.

In order to support custom base types in pg_tle, an API in a trusted language will be provided that allows pg_tle users to use the community Postgres mechanism for creating custom types.

• Like in community postgres, pg_tle will support scalar base type creation. This is the only kind of type creation we are considering for this design. We are not composite types, enumerated types, range types, etc. that are supported in community postgres.
• Custom type creation requires pgtle_admin role. Once created, these types can be used by any user in the same way and with the same syntax as for other postgres types.
• Wherever necessary, pg_tle will provides wrapper functions to invoke the same C code that a postgres extension developer would use to create custom types in community Postgres.

The workflow of creating a custom type in pg_tle will be.

1. Create a shell type by invoking a pg_tle API function.
2. Define input/output functions in a trusted language supported in pg_tle. The input/output functions must accept argument types and return type of bytea.
3. Create the custom type by invoking a pg_tle API function and providing the shell type and input/output functions.
4. Define operators and operator classes for the new type in a trusted language. If this requires marshalling/unmarshalling between the shell type and a type supported in the trusted language, then it will be done by invoking a pg_tle API function.

Proposed pg_tle API functions

Function to create a shell type

pgtle.create_shell_type(namespace text, name text)

namespace : name of the namespace the shell type is created in
name : name of the shell type

Role required pgtle_admin

Ownership The owner of the shell type is the user executing the function.

Function to create a base type

pgtle.create_datatype(
    namespace text, 
    name text, 
    input_function oid, 
    output_function oid,
    internallength int4)

namespace : name of namespace the base type is created in
name : name of the base type
input_function : regprocedure of input function
output_function : regprocedure of output function
internallength : a positive integer if the base type is fixed-length or -1 if the base type is variable-length

In the future we can consider optional arguments for receive_function, send_function, type_modifier_input_function, type_modifier_output_function, analyze_function, and subscript_function. Not doing that right now.

Role required pgtle_admin and superuser

Ownership The owner of the base type is the user executing the function.

Function to map an operator function. Map a user function that operates on bytea to an operator for a custom base type created in pg_tle. The user function must take arguments of type bytea. This function is necessary for defining user functions in plrust to operate on a custom base type. It is necessary because plrust does not have the shell type for the custom type, so a user function in plrust can’t directly operate on arguments of the custom type.

pgtle.create_operator_map_func(userfunc oid, basetype oid)

userfunc : regprocedure of the user function to map
basetype : regtype of the custom base type

Role required pgtle_admin

Ownership The owner of the base type is the user executing the function.

If the user function is in a trusted language like PL/pgSQL that can be used to operate directly on the shell type, then this function is not needed. The pg_tle user can instead call CREATE OPERATOR to do the same thing.

An Alternative to New API

We can consider an alternative to introducing new pgtle functions. We could use the ProcessUtitlity_hook to redefine CREATE TYPE in pg_tle to execute the same logic as in the new functions we propose.

• A custom type will be defined once and consumed many times. Our design is around the definition of a custom type. The user experience around using a custom type is identical to that in community Postgres.
• From a pg_tle extension writer’s perspective this would mean using Postgres CREATE TYPE instead of using pgtle.create_shell_type() and pgtle.create_datatype().
• From the perspective of consumers of that custom type, there is no difference.
• LOC : Postgres extension writers who define custom base types typically write in the order of a few hundred lines of code to define the input/output functions, operators and operator classes. So the lines of code savings with calling CREATE TYPE instead of calling pgtle functions is not significant.
• From a feature implementation perspective, this increases the engineering effort for this capability considerably.

Based on these, for now we are proposing not to try this alternative, but this can still be implemented later.

Describe alternatives

Custom types can be created in community Postgres

• Custom base types can be created only with superuser privilege.
• Requires untrusted C to implement functions.

1. Create a shell type.
2. Define input/output functions (required) that convert between the shell type and cstring. Optionally, define functions receive_function, send_function, type_modifier_input_function, type_modifier_output_function, analyze_function, and subscript_function. Generally these functions have to be coded in C.
3. Create the new data type with providing the shell type and input/output functions. There can also be optional functions such as send/receive functions provided when creating the new data type.
4. (Optional) This is not strictly necessary for creating a type, but is generally done in order to support indexing and hashing, without which a type is limited in its usability. The following are not required to be written as C functions, and can be written in other languages.
   1. Define functions, such as equality and comparison, for the new type.
   2. Define operators for the new type that use the defined functions.
   3. Define operator class to support indexing of the data type.

[adamguo0]

This feature has been implemented by #206