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?

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
eComEvo
  • 379
  • 1
  • 3
  • 15
  • 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? http://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

1 Answers1

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:

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600