PostgreSQL – psql Command-Line Client Not Showing All Schemas

postgresql

I have PostgreSQL 9.3 server. Connecting using an JDBC client (Squirrel) and executing

select schema_name from information_schema.schemata

shows me all the schemas on the server.

Connecting the same using psql via an SSH session:

su -l postgres
psql -U postgres -W -p 5432 -h localhost

and the same select shows only a few schemas:

schema_name
--------------------
pg_toast
pg_temp_1
pg_toast_temp_1
pg_catalog
public
information_schema

There is only one DB instance on the server. Username and password are the same. Why is that? Do I need to configure something for psql?

Best Answer

It's not the client application that makes the results differ, but the database user you're connected with.

In 9.3, schemata returns only the schemas that the session's user owns, either directly or indirectly through a granted role.

The doc says:

The view schemata contains all schemas in the current database that are owned by a currently enabled role.

Note that it was changed in later versions to be less restrictive.

See What permissions are required to return rows from information_schema.schemata? (9.3)
and How to check if PostgreSQL public schema exists? on stackoverflow

Related Question