How to Show/List Tables in PostgreSQL (psql, SQL & pgAdmin)

Whether you’re debugging queries, automating CI/CD workflows, or just getting familiar with a new database, knowing how to list tables in PostgreSQL is a foundational skill. From psql commands to Python scripts, there’s no shortage of ways to get the job done.

But access comes with responsibility—especially when your database holds sensitive data. That’s why understanding not just the how, but the how to do it securely, is critical.

In this guide, you’ll learn multiple ways to list tables in Postgres—and how to enhance the process with centralized access, audit logs, and least-privilege controls that make database access secure by default.

Prerequisites

There are several requirements you must meet before you can leverage the methods we’re about to discuss, including:

• You must have a PostgreSQL server installed.
• You need user credentials, usually the username and password, but this will depend on whether the PostgreSQL server needs authentication.
• You should have enough database access rights, such as CONNECT and USAGE on the relevant schema. To view tables, you may also need SELECT privileges on system catalogs.
• You need tools to connect to the PostgreSQL server, such as psql, pgAdmin, or psycopg2.

Understanding PostgreSQL Tables

PostgreSQL tables can be broadly categorized into three types: user-defined, system, and temporary tables. 

As the name suggests, user-defined tables are created by the end user to store application-specific data. System tables or catalogs, on the other hand, come predefined and are created by the database vendor. They contain metadata about the database’s configuration, objects, and structure, and they reside in pg_catalog.

Lastly, temporary tables are created during specific sessions or transactions to store data, such as results from complex queries. These are dropped at the end of the session or transaction, hence the name "temporary."

Method 1: SQL Queries

SQL queries are the language or questions you use to ask a database for specific answers. There are two queries you can use to list tables in Postgres: 

Using information_schema.tables

The information_schema lets you list all the tables in a PostgreSQL database and see the metadata of the database objects. 

Its basic syntax is: 

SELECT table_schema, table_name 
 FROM information_schema.tables  
 WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema');

While information_schema works across RDBMSs like SQL and MySQL, it lacks some PostgreSQL-specific metadata.

Using pg_catalog.pg_tables

This SQL query provides more PostgreSQL-specific context like ownership and tablespace information.

Its syntax:

SELECT schemaname, tablename FROM pg_catalog.pg_tables 
 WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

Method 2: Using psql Command-Line

The psql command-line tool lets you interact with PostgreSQL databases. One of the simplest ways to use psql is through the \dt command. 

1. Open the command prompt from your terminal and connect to the database you want using PostgreSQL using psql -U postgres -d dvdrental
2. Use \dt to list tables.

In place of \dt, you can also use the following commands, depending on the information you want to display:

• \dt+: This provides extended information such as the size and description of the table.
• \dt public.*: This tells the database to list all tables across all schemas.

Method 3: Using pgAdmin GUI

Yet another Postgres list tables alternative is pgAdmin, a graphical user interface (GUI) for managing PostgreSQL databases. This is the easiest method to use, especially for inexperienced users, because it provides a visual interface with a tree-like structure. The process:

1. Launch pgAdmin and connect to your PostgreSQL server.
2. Expand the Servers dropdown menu on the left, then Databases, and select the one you want. 
3. Expand the Schemas group option > public > Tables in the tree.  

Method 4: Programmatic Access

This method uses code and APIs to list tables in Postgres. With code, for example, you can use programming languages like Python and Node.js to connect to your PostgreSQL database and execute queries to list tables. Here’s how:

Python with psycopg2

import psycopg2.  
 conn = psycopg2.connect(...) 
 cur = conn.cursor() 
 cur.execute("""     
 SELECT table_schema, table_name 
 FROM information_schema.tables  
 WHERE table_type = 'BASE TABLE' 
 AND table_schema NOT IN ('pg_catalog', 'information_schema'); """)
 print(cur.fetchall()) 
 cur.close()  
 conn.close()

Node.js with pg

const { Client } = require('pg'); const client = new Client({ /* connection config */ }); client.connec t(); client.query ( `SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema')`, (err, res) => { if (err) throw err; res.rows.forEac h(row => console.log (row)); client.end (); } );

Security Considerations

While all the Postgres show tables methods we’ve discussed work and are easy to follow, be aware that the slightest misconfiguration can lead to an expensive security incident. That’s why it’s important to follow several best practices, including:

• Avoid hardcoding passwords: We recommend using environment variables (e.g., .env files managed with tools like dotenv) or secrets management systems such as .pgpass, Vault, AWS Secrets Manager, or Keycloak. Hardcoding gives attackers easy access to your database because it essentially involves embedding credentials into the source code.
• Use least privileges: As is the case when dealing with any kind of database, especially ones containing sensitive information, you should never grant any user too much access.
• Monitor actions: Keep an eye out for any actions performed on your databases by logging every session. 
• Leverage secure access tools: A solution like StrongDM can help enhance the security of your databases through centralized control, audit logs, and enforcing least privilege.

Troubleshooting Common Issues

When working with either of the Postgres show tables methods, you may encounter errors — don’t let them stop you in your tracks. Here’s how you can solve them:

Automating Table Discovery for DevOps

This involves using techniques and tools to identify and analyze tables automatically, especially in CI/CD workflows. For instance, using Bash:

psql -U your_user -d your_db -c "\dt *.*" > tables_list.txt

The above command lists all the tables across all schemas and saves the results in the file tables_list.txt. It’s also helpful for automated database inventory, audits, or schema diffing in pipelines. 

How to List PostgreSQL Tables Securely (and Reliably) with StrongDM

Knowing how to list tables in PostgreSQL is essential—whether you're exploring a new database, building automation into your CI/CD workflows, or performing routine audits. But access without oversight is a security risk waiting to happen.

Manually running \dt, executing SQL, or connecting via Python scripts may get you the table names—but without visibility, control, or audit logs, every query is a potential blind spot. StrongDM simplifies the process while securing every step.

1. Replace Static Database Credentials with Identity-Based Access

Traditional methods require local usernames, passwords, or stored secrets that are hard to rotate and easy to leak. StrongDM replaces static credentials with real-time, identity-based access.

• Authenticate users through your SSO or identity provider
• Assign PostgreSQL access dynamically based on role and policy
• No need to distribute .pgpass files or embed secrets in scripts

Result: users connect securely without ever seeing a password—and permissions can be revoked instantly.

2. Grant and Revoke Access in Real Time—No Manual Role Changes

Instead of manually granting SELECT on system catalogs or troubleshooting schema access errors, StrongDM lets you:

• Define least-privilege access at the query level
• Restrict database visibility by role, user, or time window
• Auto-expire access when it’s no longer needed (e.g., post-debugging)

Dev teams get fast, secure access. Security teams stay in control.

3. Log Every Table Query Automatically—No Agent Needed

With StrongDM, every database query—including SELECT table_name statements and schema explorations—is logged automatically:

• Full visibility into who ran what query, when, and from where
• Export logs to your SIEM (Splunk, Datadog, Sentinel)
• Replay sessions or investigate suspicious activity in seconds

Security events aren’t hidden in application logs or forgotten cron jobs—they’re captured in full.

4. Enforce Least Privilege and Zero Trust by Default

Letting developers explore pg_catalog or information_schema tables unrestricted? That’s a misstep.

StrongDM helps you:

• Scope access down to specific schemas, databases, or environments
• Limit access to read-only roles for query-only use cases
• Enforce multi-factor authentication and session logging for all access

Zero Trust access doesn’t just apply to users—it applies to queries, too.

5. Enable Secure Table Discovery in DevOps Pipelines

CI/CD automations often include schema checks, test table listings, and metadata scans. StrongDM enables these workflows without compromising secrets:

• Inject temporary credentials into pipeline jobs securely
• Automatically rotate credentials and revoke access after use
• Monitor and log all automated access—no extra code or scripts needed

No more hardcoded passwords or shadow access in your automation scripts.

Whether you’re using psql, pgAdmin, or Python, listing PostgreSQL tables should be easy—but not unaccountable.

StrongDM gives you centralized access, full audit trails, and policy-based controls to ensure that even the simplest table listing is secure, observable, and compliant.

Ready to make PostgreSQL access secure by default? Book a StrongDM demo and see how it’s done.