Skip to content
Ruachost Help Center

Managing PostgreSQL databases and users from the command line

PostgreSQL includes powerful command‑line tools that allow administrators to create, modify, and delete databases and users. On ruachost.com, you can manage PostgreSQL directly from the terminal for advanced control and automation.

Why Use the Command Line?

Section titled “Why Use the Command Line?”
  • Faster than using a web interface.
  • Enables scripting and automation.
  • Provides full access to PostgreSQL features.
  • Essential for developers and system administrators.

Creating PostgreSQL Users

Section titled “Creating PostgreSQL Users”

  1. Log in to your server via SSH.

  2. Switch to the PostgreSQL superuser:

    Terminal window
    su - postgres

  3. Create a new user interactively:

    Terminal window
    createuser --interactive --pwprompt
    • Enter the username.
    • Enter and confirm the password.
    • Choose whether the user is a superuser.
    • Choose whether the user can create databases.
    • Choose whether the user can create other roles.

Creating PostgreSQL Databases

Section titled “Creating PostgreSQL Databases”

  1. Log in as the PostgreSQL superuser:

    Terminal window
    su - postgres

  2. Create a database owned by a specific user:

    Terminal window
    createdb -O username dbname
    • Replace username with the database owner.
    • Replace dbname with the database name.

???? Tip: Users with permission to create databases can run:

Terminal window
createdb dbname

Granting User Privileges

Section titled “Granting User Privileges”

  1. Connect to PostgreSQL:

    Terminal window
    psql -U postgres

  2. Grant privileges:

    GRANT ALL PRIVILEGES ON DATABASE dbname TO username;
    • Replace dbname with the database name.
    • Replace username with the user.

Deleting PostgreSQL Databases

Section titled “Deleting PostgreSQL Databases”

  1. Log in as the PostgreSQL superuser.

  2. Run:

    Terminal window
    dropdb dbname

    ⚠️ Warning: This deletes the database and all its data immediately without confirmation.

Deleting PostgreSQL Users

Section titled “Deleting PostgreSQL Users”

  1. Log in as the PostgreSQL superuser.

  2. Run:

    Terminal window
    dropuser username

    ⚠️ Important: You cannot drop a user if they own any databases or objects.

    • First, reassign ownership or drop the database.