{"id":6027,"library":"pgcli","title":"pgcli: PostgreSQL Command-Line Interface","description":"pgcli is a Python package that serves as an interactive command-line interface for the PostgreSQL database server. It enhances the standard PostgreSQL experience with features like auto-completion, syntax highlighting, smart-completion, and extensive support for psql-like back-slash commands. The project is actively maintained with a consistent release cadence, with version 4.4.0 being the current stable release.","status":"active","version":"4.4.0","language":"en","source_language":"en","source_url":"https://github.com/dbcli/pgcli","tags":["database","cli","postgresql","terminal"],"install":[{"cmd":"pip install pgcli","lang":"bash","label":"PyPI (recommended)"},{"cmd":"brew install pgcli","lang":"bash","label":"macOS (Homebrew)"}],"dependencies":[{"reason":"Required for connecting to a PostgreSQL database. The CLI client interacts with a running PostgreSQL instance.","package":"PostgreSQL","optional":false},{"reason":"System-level development headers for Python and PostgreSQL's C library (libpq) are required to build the `psycopg` or `psycopg-binary` adapter, especially on Linux.","package":"python-dev / libpq-dev","optional":false},{"reason":"The core PostgreSQL adapter. `psycopg` for Linux/macOS, `psycopg-binary` for Windows.","package":"psycopg / psycopg-binary","optional":false},{"reason":"Core library providing interactive terminal features like auto-completion and syntax highlighting.","package":"prompt_toolkit","optional":false},{"reason":"Optional, for integrating with system keyring services to securely store database credentials.","package":"keyring","optional":true},{"reason":"Optional, for establishing SSH tunnels to connect to remote PostgreSQL instances.","package":"sshtunnel","optional":true}],"imports":[],"quickstart":{"code":"pgcli -h localhost -p 5432 -U $(whoami) -d my_database","lang":"bash","description":"Connect to a local PostgreSQL database named 'my_database' using the current system username. You will be prompted for a password if required. Alternatively, use a connection URL: `pgcli postgresql://user:password@host:port/dbname`."},"warnings":[{"fix":"Upgrade your Python environment to Python 3.9 or newer. Ensure your virtual environment uses a supported Python version.","message":"Python 3.8 and older are no longer supported. Version 4.0.0 dropped support for Python <3.8, and version 4.2.0 dropped support for Python <3.9.","severity":"breaking","affected_versions":">=4.0.0"},{"fix":"Install the necessary system packages, e.g., `sudo apt-get install python3-dev libpq-dev` (Debian/Ubuntu) or `sudo yum install python3-devel postgresql-devel` (RHEL/CentOS/Fedora) before running `pip install pgcli`.","message":"Installation on Linux often requires system-level development packages for `libpq` and Python (`python-dev`, `libpq-dev`, or equivalents like `postgresql-devel`, `python-devel`). Without these, the `psycopg` adapter build may fail.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Ensure the proxy's root certificate is installed in your system's trust chain. Verify outbound firewall rules allow PostgreSQL traffic (port 5432 by default). Consider using an SSH tunnel if direct access is restricted.","message":"Connection issues can arise due to network proxies (e.g., Zscaler) interfering with direct TCP connections or SSL certificate validation. Errors like 'SSL routines:ssl3_get_server_certificate:certificate verify failed' are common.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Always set `.pgpass` file permissions strictly (e.g., `chmod 600`). Avoid storing production passwords directly in shell history. Use secure credential management systems or rely on interactive password prompts where appropriate.","message":"Storing passwords in shell history or inadequately permissioned `.pgpass` files (e.g., `chmod 644`) poses a security risk. Environment variables (like `PGPASSWORD`) can also be inspected.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Utilize `pgcli`'s configuration options like `destructive_statements_require_transaction` and `destructive_warning` (introduced in 4.4.0) to prompt for confirmation before executing potentially damaging queries.","message":"The interactive nature and ease of use can inadvertently lead to executing destructive SQL commands. This is a common footgun in any direct database CLI.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-04-14T00:00:00.000Z","next_check":"2026-07-13T00:00:00.000Z"}