Postgresql – Navicat Premium doesn’t show/list tables in postgres database schema

The line in your netstat report shows that the database is only listening on localhost:5432 (127.0.0.1) for incoming tcp connections.

Proto Recv-Q Send-Q Local Address   Foreign Address  State   PID/Program name
tcp        0      0 127.0.0.1:5432  0.0.0.0:*        LISTEN  3561/postgres

So it can only accept local tcp connections regardless of what permissions you've specified in pg_hba.conf. pg_hba.conf only specifies allowed connections, but does not specify what interfaces your service will listen to.

The addresses the server listens on is specified with the listen_addresses GUC in postgresql.conf. If you want the server to listen for remote connections you should specify the ip(s) you want it to listen on or * to listen on all available interfaces on the host.

To have your postgresql server listen on all interfaces on the host, you should have the following line in postgresql.conf:

listen_addresses = '*'

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.

• Is it recommended to install extensions into pg_catalog 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:

• How does the search_path influence identifier resolution and the “current schema”