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
  • 185,527
  • 28
  • 463
  • 633
eComEvo
  • 399
  • 1
  • 4
  • 15

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
  • 185,527
  • 28
  • 463
  • 633