# Managing PostgreSQL databases and users from the command line

> Create and delete PostgreSQL databases and users, and grant privileges, from the command line.

Source: https://helpcenter.ruachost.com/developers/databases/managing-postgresql-databases-and-users-from-the-command-line/

> **PostgreSQL is available on Managed VPS only:**
Ruachost shared hosting supports MySQL (MariaDB) databases, not PostgreSQL. To use PostgreSQL, choose a [Managed VPS](https://ruachost.com/vps).

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?

- 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

1. Log in to your server via SSH.
2. Switch to the PostgreSQL superuser:

   ```bash
   su - postgres
   ```
3. Create a new user interactively:

   ```bash
   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

1. Log in as the PostgreSQL superuser:

   ```bash
   su - postgres
   ```
2. Create a database owned by a specific user:

   ```bash
   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:

```bash
createdb dbname
```

## Granting User Privileges

1. Connect to PostgreSQL:

   ```bash
   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

1. Log in as the PostgreSQL superuser.
2. Run:

   ```bash
   dropdb dbname
   ```

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

## Deleting PostgreSQL Users

1. Log in as the PostgreSQL superuser.
2. Run:

   ```bash
   dropuser username
   ```

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

   - First, reassign ownership or drop the database.

> **Important Notes:**
- Always back up databases before deleting or renaming.
- Use strong passwords for PostgreSQL accounts.
- Avoid granting superuser privileges unless absolutely necessary.
- Clear application cache after making changes to users or databases.