PostgreSQL List All Schemas: psql, information_schema, pg_namespace, and search_path
For SQL users who connect to a PostgreSQL database that somebody else organized, and need to know which schemas it holds and which ones their role can use.
On this page
You connect to a PostgreSQL database that somebody else set up, and the tables you need aren't in public. In psql, list the schemas with a meta-command:
\dn
From any other client, query the information schema:
SELECT schema_name
FROM information_schema.schemata
ORDER BY schema_name;
That query returns only the schemas that your role holds a privilege on. To see every schema, query pg_catalog.pg_namespace, as shown under List schemas with pg_catalog.
To follow the examples on PostgreSQL 18.6, create two roles and two schemas as a superuser:
CREATE ROLE app_user LOGIN;
CREATE ROLE analyst LOGIN;
CREATE SCHEMA sales AUTHORIZATION app_user;
CREATE SCHEMA reporting AUTHORIZATION app_user;
Run every query below as analyst, unless the text names another role. analyst holds no privilege on sales or reporting.
Which schemas each method shows
The methods filter differently, so a schema missing from one list can still be in the database:
| Method | System schemas | Filtered by your privileges |
|---|---|---|
\dn in psql | only with \dnS | no |
information_schema.schemata | yes | yes |
pg_catalog.pg_namespace | yes | no |
Two system schemas are in every database. The pg_catalog schema holds the system tables and the built-in data types, functions and operators. The information schema holds the standard views, schemata among them.
List schemas with psql
\dn
List of schemas
Name | Owner
-----------+-------------------
public | pg_database_owner
reporting | app_user
sales | app_user
(3 rows)
\dn doesn't filter by privilege, so analyst sees sales and reporting without a grant on either. \dn leaves out the system schemas. The other forms of the command change the output:
\dnSadds the system schemasinformation_schema,pg_catalogandpg_toast.\dn+adds each schema's access privileges and description.\dn s*lists only the schemas whose names start withs, heresales.
The owner of public is pg_database_owner, a role that stands for the owner of the current database. PostgreSQL 15 made that change for new databases. A database upgraded from an older release, or restored from a dump, keeps its old owner of public.
List schemas with information_schema
The standard view works in any client that sends SQL, such as a migration script or an application:
SELECT schema_name,
schema_owner
FROM information_schema.schemata
ORDER BY schema_name;
| schema_name | schema_owner |
|---|---|
| information_schema | postgres |
| pg_catalog | postgres |
| public | pg_database_owner |
sales and reporting are missing. The schemata view returns only "all schemas in the current database that the current user has access to (by way of being the owner or having some privilege)". analyst owns neither schema and holds no privilege on either. A role that holds a privilege on sales sees sales in the same query.
List schemas with pg_catalog
The pg_namespace catalog has no privilege filter, so analyst gets every schema:
SELECT nspname AS schema_name,
pg_catalog.pg_get_userbyid(nspowner) AS schema_owner
FROM pg_catalog.pg_namespace
ORDER BY nspname;
| schema_name | schema_owner |
|---|---|
| information_schema | postgres |
| pg_catalog | postgres |
| pg_toast | postgres |
| public | pg_database_owner |
| reporting | app_user |
| sales | app_user |
This list includes sales and reporting, and also pg_toast, the schema where PostgreSQL stores values too large for a table row. A session that creates a temporary table also creates two schemas, such as pg_temp_20 and pg_toast_temp_20. They stay in the list after the session ends.
Use pg_get_userbyid to turn the owner's ID into a role name, rather than a join to the pg_user view. pg_user lists only roles that can log in, and pg_database_owner can't, so the join drops public.
Filter out the system schemas
Two conditions remove the system schemas:
SELECT nspname AS schema_name
FROM pg_catalog.pg_namespace
WHERE nspname NOT LIKE 'pg\_%'
AND nspname <> 'information_schema'
ORDER BY nspname;
| schema_name |
|---|
| public |
| reporting |
| sales |
The filter is safe because schema names that begin with pg_ "are reserved for system purposes and cannot be created by users". That holds for a superuser too: CREATE SCHEMA pg_mine; fails with unacceptable schema name "pg_mine".
Keep the backslash. LIKE reads an unescaped underscore as any single character, so 'pg_%' also drops a schema named pgbouncer. The same conditions work on information_schema.schemata, with schema_name in place of nspname.
Inspect schema owners and privileges
A role needs the USAGE privilege on a schema to use the objects in it, and the CREATE privilege to create objects there. has_schema_privilege(schema, privilege) returns true when the current role holds the privilege:
SELECT n.nspname AS schema_name,
has_schema_privilege(n.nspname, 'USAGE') AS can_use,
has_schema_privilege(n.nspname, 'CREATE') AS can_create
FROM pg_catalog.pg_namespace n
WHERE n.nspname NOT LIKE 'pg\_%'
AND n.nspname <> 'information_schema'
ORDER BY n.nspname;
| schema_name | can_use | can_create |
|---|---|---|
| public | t | f |
| reporting | f | f |
| sales | f | f |
analyst can use public, but not sales or reporting. The owner of a schema grants access to it. Here app_user runs:
GRANT USAGE ON SCHEMA sales TO analyst;
After the grant, sales appears in analyst's information_schema.schemata. reporting stays out.
can_create is false on public. PostgreSQL 15 removed "PUBLIC creation permission on the public schema" in new databases. When analyst runs CREATE TABLE notes (id int); without a schema name, PostgreSQL tries to create the table in public and fails with permission denied for schema public.
Check the current search_path
An unqualified table name reaches only the schemas on the search path:
SHOW search_path;
| search_path |
|---|
| "$user", public |
"$user" stands for a schema named after the current role. PostgreSQL ignores that entry when no such schema exists. pg_catalog isn't in the setting, but PostgreSQL searches it before the schemas of the setting: pg_catalog "is always effectively part of the search path".
Set the path to put a schema first, then ask for the path as PostgreSQL applies it:
SET search_path = sales, public;
SELECT current_schemas(true);
| current_schemas |
|---|
| {pg_catalog,sales,public} |
current_schemas(true) lists sales because the grant above gave analyst USAGE on it. Without the grant, current_schemas leaves sales out and returns {pg_catalog,public}.
PostgreSQL has no default-schema setting of its own. current_schema() returns the first schema on the path, here sales, and a CREATE TABLE without a schema name creates the table there. analyst holds no CREATE privilege on sales, so such a CREATE TABLE fails too.
SET lasts until the session ends. To set the path for every later session of a role, use ALTER ROLE:
ALTER ROLE analyst SET search_path = sales, public;
Browse schemas visually in DbSchema
DbSchema lists the schemas of the database when you connect, and draws the tables of the ones you pick:
- Choose File → New Model, keep Connect to Database selected, click PostgreSQL and press Connect to PostgreSQL.
- Fill in the connection dialog and press Connect.
- In Reverse Engineer the Database, tick the schemas to import and press Load.
DbSchema draws the imported tables in a new model, and the tree on the left lists each schema with its tables, views and other objects. Importing only reads from the database and changes nothing in it. To run the catalog queries above, press Editor in the toolbar and choose SQL Editor.
Download DbSchema at https://dbschema.com/download.html, connect to your PostgreSQL database, and tick the schemas you want to see. Connecting, reverse-engineering, the diagrams and the SQL Editor are in the free Community Edition. To list the tables inside one schema, read Show Tables in PostgreSQL, and to see the columns of one table, Describe Table in PostgreSQL.

