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 namedmydatabase onlocalhost (port5432) as userpostgres:

psql -U postgres -h localhost -p 5432 mydatabase

2. Logging into a Specific Database (alternative):

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

psql -U root -d mydatabase;

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

3. Viewing Tables and Data:

3.1 List All Databases: Insidepsql:

\l

3.2 Connect to a Different Database: Insidepsql:

\c mydatabase

3.3 List All Tables in the Current Database: Insidepsql (for tables in the defaultpublic schema):

\dt

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

SELECT * FROM mytable LIMIT 10;

3.5 Exit**psql**: Insidepsql:

\q

3.6 List All Users (Roles): Insidepsql:

\du

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

CREATE USER newuser WITH PASSWORD 'your_password';

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

ALTER USER username WITH PASSWORD 'new_password';

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

This command dumpsmydatabase 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

Parameter Explanation:

• pg_dump: The PostgreSQL database backup utility.
• -U postgres: Specifies the database username aspostgres.
• -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

Parameter Explanation:

• pg_restore: The utility for restoring PostgreSQL backups created bypg_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 (commonlypostgres ortemplate1) to issue theCREATE 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 totrust 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 inpg_hba.conf, located in the PostgreSQL data directory.

3. Modifypg_hba.conf Configuration File:

Find and editpg_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

Change authentication methods fromtrust (orpeer if you want to enforce passwords for local users too) toscram-sha-256 (recommended) ormd5.

Example**pg_hba.conf**entries:

# TYPE  DATABASE        USER            ADDRESS                 METHOD# "local" is for Unix domain socket connections onlylocal   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-256host    replication     all             127.0.0.1/32            scram-sha-256host    replication     all             ::1/128                 scram-sha-256

4. Restart PostgreSQL Service:

After modifyingpg_hba.conf, restart PostgreSQL for changes to take effect.

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

sudo systemctl restart postgresql

For Docker containers:

docker restart my_postgres_container_name

5. Set PostgreSQL User Passwords:

Ensure your PostgreSQL users have passwords set.

# Switch to the postgres OS usersudo -i -u postgres# Enter psqlpsql# Set password for the 'postgres' user (or any other user)ALTER USER postgres WITH PASSWORD 'your_secure_password';# Exit psql\qexit # to exit from postgres OS user session

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 mydatabaseunset PGPASSWORD # Good practice to unset it after use

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

nano ~/.pgpass

Add entries in the formathostname:port:database:username:password:

localhost:5432:mydatabase:postgres:your_secure_passwordlocalhost:5432:*:postgres:your_secure_password # For any database for user postgres

Set strict permissions for this file:

chmod 600 ~/.pgpass

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

psql -U postgres -h localhost -p 5432 -d mydatabase

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

PGPASSWORD='your_secure_password' psql -U postgres -h localhost -p 5432 -d mydatabase

Thepsql client will also prompt for a password ifpg_hba.conf requires one and it's not provided by other means.

III. Setting User Access Permissions

To ensure a usermyuser can only connect to a specific databasemydatabase and has appropriate object-level permissions:

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

SQL

-- As a superuser in psqlCREATE USER myuser WITH PASSWORD 'myuser_password';CREATE DATABASE mydatabase;-- Grant connect privilege on the database to the userGRANT CONNECT ON DATABASE mydatabase TO myuser;

(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 schemaGRANT 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;

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 inpg_hba.conf are Correct:

Editpg_hba.conf to allowmyuser to connect tomydatabase from specific IP addresses or ranges using a password method (e.g.,scram-sha-256 ormd5).

# Example entry in pg_hba.conf# TYPE  DATABASE        USER            ADDRESS                 METHODhost    mydatabase      myuser          192.168.1.0/24          scram-sha-256

This line allows usermyuser to connect tomydatabase from any IP in the192.168.1.0/24 network, using SCRAM-SHA-256 password authentication.

4. Restart PostgreSQL Service:

After modifyingpg_hba.conf, restart PostgreSQL:

sudo systemctl restart postgresql# Or for Docker:# docker restart my_postgres_container_name

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;

• 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

• Edit**pg_hba.conf**for network access:

host    mydatabase      myuser          192.168.1.0/24          scram-sha-256

• Restart PostgreSQL.

By following these steps, you can ensure thatmyuser can only connect tomydatabase 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, considerChat2DB.

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.