DEV Community

Chat2DB profile image Jing
Jing for Chat2DB

Posted on • Edited on

Your PostgreSQL Command Cheat Sheet (But Way More Useful!)

#programming #postgres #sql #database

This guide covers a range of commonly used commands for interacting with and managing your PostgreSQL databases, from basic connections and data viewing to backup/restore operations and security configurations.

I. Common Database Commands

1. Logging into a PostgreSQL Database:

To connect to a PostgreSQL database named mydatabase on localhost (port 5432) as user postgres:

psql -U postgres -h localhost -p 5432 mydatabase
Enter fullscreen mode Exit fullscreen mode

2. Logging into a Specific Database (alternative):

If you’re already in a context where psql knows the host/port, or if you're connecting locally with sufficient peer authentication:

psql -U root -d mydatabase;
Enter fullscreen mode Exit fullscreen mode

(Note: Using *root* as a PostgreSQL username is unconventional; *postgres* is the typical superuser.)

3. Viewing Tables and Data:

3.1 List All Databases: Inside psql:

\l
Enter fullscreen mode Exit fullscreen mode

3.2 Connect to a Different Database: Inside psql:

\c mydatabase
Enter fullscreen mode Exit fullscreen mode

3.3 List All Tables in the Current Database: Inside psql (for tables in the default public schema):

\dt
Enter fullscreen mode Exit fullscreen mode

3.4 View Content of a Specific Table (e.g., first 10 rows): Inside psql:

SELECT * FROM mytable LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

3.5 Exit **psql**: Inside psql:

\q
Enter fullscreen mode Exit fullscreen mode

3.6 List All Users (Roles): Inside psql:

\du
Enter fullscreen mode Exit fullscreen mode

3.7 Create a User and Set a Password: Inside psql (as a superuser):

CREATE USER newuser WITH PASSWORD 'your_password';
Enter fullscreen mode Exit fullscreen mode

3.8 Change a Specific User’s Password: Inside psql (as a superuser or the user themselves if they have login rights):

ALTER USER username WITH PASSWORD 'new_password';
Enter fullscreen mode Exit fullscreen mode

4. Backing Up a Database (Including Create Database Command):

This command dumps mydatabase into a custom-format backup file.

pg_dump -U postgres -h localhost -p 5432 -F c -b -v -C -f /path/to/backup/mydatabase_backup.dump mydatabase
Enter fullscreen mode Exit fullscreen mode

Parameter Explanation:

  • pg_dump: The PostgreSQL database backup utility.
  • -U postgres: Specifies the database username as postgres.
  • -h localhost: Specifies the database server hostname.
  • -p 5432: Specifies the database server port.
  • -F c: Sets the backup file format to 'custom'. This format is compressed by default, allows for selective restore, and supports parallel restore.
  • -b: Includes large objects (blobs) in the backup.
  • -v: Enables verbose mode, showing detailed progress.
  • -C: Includes commands in the backup file to create the database itself.
  • -f /path/to/backup/mydatabase_backup.dump: Specifies the output backup file path and name.
  • mydatabase: The name of the database to back up.

5. Restoring a Database from a Backup File (Including Create Database Command):

This command restores a database from a backup created with the -C option.

pg_restore -U postgres -h localhost -p 5432 -C -d postgres -v /path/to/backup/mydatabase_backup.dump
Enter fullscreen mode Exit fullscreen mode

Parameter Explanation:

  • pg_restore: The utility for restoring PostgreSQL backups created by pg_dump.
  • -U postgres: Specifies the database username.
  • -h localhost: Specifies the database server hostname.
  • -p 5432: Specifies the database server port.
  • -C: Creates the database before restoring. The backup must have been created with -C.
  • -d postgres: Specifies the initial database to connect to. When using -C, pg_restore connects to this database (commonly postgres or template1) to issue the CREATE DATABASE command for the new database being restored.
  • -v: Enables verbose mode.
  • /path/to/backup/mydatabase_backup.dump: The path to the backup file to restore.

II. Requiring Password Authentication for PostgreSQL (Especially in Docker)

1. Explanation:

If you can log into PostgreSQL within a Docker container without a password, it’s typically because PostgreSQL’s host-based authentication (pg_hba.conf) is configured to trust local connections or connections from certain IP addresses.

2. PostgreSQL Authentication Methods:

PostgreSQL supports various methods, including:

  • trust: Allows connection unconditionally.
  • reject: Rejects connection unconditionally.
  • password: Requires a clear-text password (not recommended over insecure connections).
  • md5: Requires an MD5-hashed password.
  • scram-sha-256: Uses SCRAM-SHA-256 password authentication (recommended for new setups).
  • peer: Uses the client's operating system user name for authentication (for local Unix domain socket connections).
  • ident: Uses the ident protocol to get the client's operating system user name (for TCP/IP connections).

These are configured in pg_hba.conf, located in the PostgreSQL data directory.

3. Modify pg_hba.conf Configuration File:

Find and edit pg_hba.conf. You can locate it using:

sudo find / -name pg_hba.conf
# Or, if you know your PostgreSQL data directory (e.g., /var/lib/pgsql/data):
# ls /var/lib/pgsql/data/pg_hba.conf
Enter fullscreen mode Exit fullscreen mode

Change authentication methods from trust (or peer if you want to enforce passwords for local users too) to scram-sha-256 (recommended) or md5.

Example **pg_hba.conf** entries:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     scram-sha-256
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the replication privilege.
local   replication     all                                     scram-sha-256
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
Enter fullscreen mode Exit fullscreen mode

4. Restart PostgreSQL Service:

After modifying pg_hba.conf, restart PostgreSQL for changes to take effect.

For system service (e.g., using systemd):

sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

For Docker containers:

docker restart my_postgres_container_name
Enter fullscreen mode Exit fullscreen mode

5. Set PostgreSQL User Passwords:

Ensure your PostgreSQL users have passwords set.

# Switch to the postgres OS user
sudo -i -u postgres

# Enter psql
psql

# Set password for the 'postgres' user (or any other user)
ALTER USER postgres WITH PASSWORD 'your_secure_password';

# Exit psql
\q
exit # to exit from postgres OS user session
Enter fullscreen mode Exit fullscreen mode

6. Logging into PostgreSQL with a Password:

Here are a few ways to provide a password:

Method 1: Using the **PGPASSWORD** Environment Variable (session-specific):

export PGPASSWORD='your_secure_password'
psql -U postgres -h localhost -p 5432 -d mydatabase
unset PGPASSWORD # Good practice to unset it after use
Enter fullscreen mode Exit fullscreen mode

Method 2: Using a **.pgpass** File: Create a .pgpass file in your home directory (~/.pgpass).

nano ~/.pgpass
Enter fullscreen mode Exit fullscreen mode

Add entries in the format hostname:port:database:username:password:

localhost:5432:mydatabase:postgres:your_secure_password
localhost:5432:*:postgres:your_secure_password # For any database for user postgres
Enter fullscreen mode Exit fullscreen mode

Set strict permissions for this file:

chmod 600 ~/.pgpass
Enter fullscreen mode Exit fullscreen mode

Now, psql will automatically try to use credentials from this file:

psql -U postgres -h localhost -p 5432 -d mydatabase
Enter fullscreen mode Exit fullscreen mode

Method 3: Passing Password Inline with **PGPASSWORD** (for one-time commands):

PGPASSWORD='your_secure_password' psql -U postgres -h localhost -p 5432 -d mydatabase
Enter fullscreen mode Exit fullscreen mode

The psql client will also prompt for a password if pg_hba.conf requires one and it's not provided by other means.

III. Setting User Access Permissions

To ensure a user myuser can only connect to a specific database mydatabase and has appropriate object-level permissions:

1. Create User and Database (if they don’t exist):

SQL

-- As a superuser in psql
CREATE USER myuser WITH PASSWORD 'myuser_password';
CREATE DATABASE mydatabase;
-- Grant connect privilege on the database to the user
GRANT CONNECT ON DATABASE mydatabase TO myuser;
Enter fullscreen mode Exit fullscreen mode

(By default, users can’t connect to databases unless explicitly granted *CONNECT* privilege, or if they are the owner, or if the *public* role has *CONNECT* on *template1* which is usually the case.)

2. Configure Table and Other Object Permissions:

Connect to the specific database and grant permissions:

\c mydatabase -- Connect to mydatabase
-- Grant usage on the schema (e.g., public)
GRANT USAGE ON SCHEMA public TO myuser;
-- Grant specific DML privileges on all tables in the public schema
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;
-- Or for specific tables:
-- GRANT SELECT ON TABLE mytable1, mytable2 TO myuser;
-- GRANT INSERT ON TABLE mytable1 TO myuser;
-- You might also need to grant permissions on sequences, functions, etc.
-- GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO myuser;
Enter fullscreen mode Exit fullscreen mode

Privileges:

  • SELECT: Read data.
  • INSERT: Add new data.
  • UPDATE: Modify existing data.
  • DELETE: Remove data.
  • USAGE (on schema): Allows access to objects within the schema (but not necessarily the objects themselves).

3. Ensure Access Control Rules in pg_hba.conf are Correct:

Edit pg_hba.conf to allow myuser to connect to mydatabase from specific IP addresses or ranges using a password method (e.g., scram-sha-256 or md5).

# Example entry in pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    mydatabase      myuser          192.168.1.0/24          scram-sha-256
Enter fullscreen mode Exit fullscreen mode

This line allows user myuser to connect to mydatabase from any IP in the 192.168.1.0/24 network, using SCRAM-SHA-256 password authentication.

4. Restart PostgreSQL Service:

After modifying pg_hba.conf, restart PostgreSQL:

sudo systemctl restart postgresql
# Or for Docker:
# docker restart my_postgres_container_name
Enter fullscreen mode Exit fullscreen mode

Summary of Granting Permissions:

  • Create user & database, grant connect: 
CREATE USER myuser WITH PASSWORD 'myuser_password'; 
CREATE DATABASE mydatabase; 
GRANT CONNECT ON DATABASE mydatabase TO myuser;
Enter fullscreen mode Exit fullscreen mode
  • Configure object permissions (inside **mydatabase**): 
\c mydatabase GRANT USAGE ON SCHEMA public TO myuser; 
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO myuser; -- Example
Enter fullscreen mode Exit fullscreen mode
  • Edit **pg_hba.conf** for network access: 
host    mydatabase      myuser          192.168.1.0/24          scram-sha-256
Enter fullscreen mode Exit fullscreen mode
  • Restart PostgreSQL.

By following these steps, you can ensure that myuser can only connect to mydatabase and has only the necessary permissions within it.

Simplify Your PostgreSQL Management with Chat2DB

Managing PostgreSQL through the command line is powerful, but for many day-to-day tasks, a modern GUI can significantly boost productivity. If you’re looking for an intelligent, versatile database client, consider Chat2DB.

Chat2DB (https://chat2db.ai) is an AI-powered tool designed to streamline your database operations across a wide range of SQL and NoSQL databases, including PostgreSQL.

With Chat2DB, you can:

  • Connect and Manage Multiple Databases: Easily switch between PostgreSQL instances or even different database types from a single interface.
  • AI-Powered SQL Assistance: Generate SQL queries from natural language, get explanations for complex SQL, or even convert SQL between different database dialects. This can be incredibly helpful when learning new commands or exploring your schema.
  • Intuitive Schema Browse: Visually explore your databases, schemas, tables, users, and permissions.
  • Data Management & Visualization: Effortlessly view, edit, import, and export data.
  • Secure & Private: Chat2DB supports private deployment, ensuring your data interactions remain within your control.

Top comments (0)