3

I need to connect to our AWS RDS PostgreSQL 9.5 instance so I can add the citext extension to pg_catalog schema so it is available to all schemas (PG prepends pg_catalog to the beginning of the search_path by default).

I'm easily able to do this on my local machine:

> sudo su - postgres
> psql
> \c mydatabase
> CREATE EXTENSION IF NOT EXISTS citext SCHEMA pg_catalog;
> \q
> exit

If I login to the RDS using a direct connection as AWS docs show, I am given permission denied for schema pg_catalog when I try to CREATE EXTENSION:

psql
   --host=myrdsawshostname
   --port=5432
   --username=myusername
   --password
   --dbname=mydatabase

This is the only way I know how to connect and I can only seem to connect as "myusername" not "postgres".

What am I doing wrong here?

Improve this question
5
  • Did you tried as "CREATE EXTENSION IF NOT EXISTS citext" without giving any schema ? – Sahap Asci Sep 28 '16 at 10:55
  • What is available to all schemas supposed to mean. It does no make sense in Postgres terminology. Maybe you mean available to all users? – Erwin Brandstetter Sep 28 '16 at 12:30
  • @ErwinBrandstetter I'm actually referring to what I learned from another post of yours The system schemas pg_temp (schema for temporary objects of the current session) and pg_catalog are automatically part of the search path and searched first, in this order. Did I misinterpret in this context? stackoverflow.com/a/9067777/1161463 – eComEvo Sep 28 '16 at 18:31
  • @ErwinBrandstetter to clarify, I mean I want the extensions I add to be available for use in all schemas that come after the schema that has that extension in the search_path. I think your answer gives a safer solution to this. – eComEvo Sep 28 '16 at 18:34
  • @eComEvo: The search_path decides visibility of objects. Schemas in Postgres are much (but not completely!) like directories in a file system in this respect, and the search_path much like the directory search path in the OS. If an object is visible, it is available for use in any schema. See: What is the search path? – Erwin Brandstetter Sep 28 '16 at 22:25
2

Your attempt to write to the pg_catalog schema probably fails because you don't have a superuser on RDS, just a role with limited admin privileges. And Amazon is smart enough to prevent users from messing with the system catalogs. I.e.: no CREATE privilege in the schema pg_catalog.

It's generally not advisable to install additional modules like citext into the pg_catalog system schema. (Some exceptions have the schema preset.) Install it to public or some dedicated schema.

The "home" schema of the installed extension needs to be in the search_path. The manual:

The schema containing the citext operators must be in the current search_path (typically public); if it is not, the normal case-sensitive text operators will be invoked instead.

You can easily set the search_path for one / some / all users or databases or user / database combinations or temporarily or for the current session or generally:

Improve this answer

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.