Installing extension as non superuser on Amazon AWS RDS
Amazon RDS has a custom list of allowed extensions. You can see it with:
SHOW rds.extensions;

See online documentation.
And you should do it as role rds_superuser or as a member of that role (actual superuser is not available). Their website informs:

The rds_superuser role can do the following:

Add extensions that are available for use with Amazon RDS. [...]