4

Initially we installed all extensions in default "public" schema. Now we have more and more other schemas - thus we want to add extensions and common functions to the schema common.

Unfortunately AWS RDS reports back when trying to move the existing ones like this:

ALTER EXTENSION "hstore" set schema common;

DBCException: SQL Error [42501]: ERROR: must be owner of extension hstore
  org.postgresql.util.PSQLException: PSQLException: ERROR: must be owner of extension hstore

I am using the default user that is also member of role rds_superuser.

  • When logged in, check the ownership of the extension using SELECT extname, extowner::regrole FROM pg_catalog.pg_extension; (assuming you are on 9.5 - in any other case, you have to join pg_authid to get the role name). The rds_superuser is not really a superuser. – dezso Mar 16 '16 at 16:37
  • Does not work with RDS: User has also no permission on pg_authid. but I saw that al extensions belong to the same user with id 10... – Strinder Mar 18 '16 at 9:00
  • Ok, dug deeper - following phenomenon: Extensions in RDS can be created in every schema and also dropped. But not moved. Which is rather bad if you want to correct design errors... – Strinder Mar 18 '16 at 9:00
  • That sounds bad, yes. – dezso Mar 18 '16 at 9:37
  • having a similar issue, any headway? – Phill Pafford Sep 27 '16 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.