How to create INOUT assignment cast on Amazon RDS PostgreSQL? If not possible, then why?

Question

I'm migrating existing application to Amazon RDS PostgreSQL. The application has cross-database support and for compatibility reasons it includes the following SQL:

CREATE CAST (varchar AS numeric) WITH INOUT AS ASSIGNMENT

On plain PostgreSQL, this requires superuser. On Amazon RDS PostgreSQL this fails with

ERROR: must be owner of type character varying or type numeric

• Is there a way to create such a cast in Amazon RDS (perhaps via administrative web interface)?
• If this is not possible, then why? Is this deliberate limitation (e.g. is the cast something dangerous) or just a missing feature?

Note: I guess that whether this is assignment or implicit cast is irrelevant, but included this information for completeness.

Answer 1

On AWS RDS you must first alter type <type> owner to <user> before create cast since the default owner of all types is the rdsadmin user.

To run alter type you must do so with the master user system account you created for RDS or any other user that you have given the rds_superuser role. From the docs:

When you create a DB instance, the master user system account that you create is assigned to the rds_superuserrole. The rds_superuser role is similar to the PostgreSQL superuser role (customarily named postgres in local instances) but with some restrictions.

Answer 2

The restriction is the same modern Postgres and in Amazon RDS. Per documentation:

To be able to create a cast, you must own the source or the target data type and have USAGE privilege on the other type. To create a binary-coercible cast, you must be superuser. (This restriction is made because an erroneous binary-coercible cast conversion can easily crash the server.)

Bold emphasis mine. Since all base types (including varchar and numeric) are owned by the superuser postgres (by default), you need to be superuser, effectively. If you have tried it as postgres already, we can conclude that Amazon RDS has another superuser role in the background owning these types (and probably the whole core system).

The Amazon RDS online manual indicates as much:

When you create a DB instance, the master user system account that you create is assigned to the rds_superuser role. The rds_superuser role is a pre-defined Amazon RDS role similar to the PostgreSQL superuser role (customarily named postgres in local instances), but with some restrictions.

Bold emphasis mine.

• Piotr Findeisen: Erwin, thanks for your answer. Well, of course the create cast I quoted in the question does not work on RDS (hence my question). I know "rds_superuser is not superuser" limitation, but the question is -- can this limitation be worked around (I guess not?)? And why PostgreSQL itself doesn't allow to define such casts by non-superusers.
• Erwin Brandstetter: @PiotrFindeisen: But it does. If you own the type you can create the cast. It makes sense to restrict this, much of what happens in queries depends on available casts and precedence among those. Malicious users could disrupt expected behavior by creating new casts.
• Piotr Findeisen: I see. So to allow creating casts by non-superuser I need alter type <t> owner to <u> first? I wonder why RDS does not change owner of all types to rds_superuser then.