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?
available to all schemas
supposed to mean. It does no make sense in Postgres terminology. Maybe you meanavailable to all users
? – Erwin Brandstetter Sep 28 '16 at 12:30The 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:31search_path
. I think your answer gives a safer solution to this. – eComEvo Sep 28 '16 at 18:34search_path
decides visibility of objects. Schemas in Postgres are much (but not completely!) like directories in a file system in this respect, and thesearch_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