How to Manage PostgreSQL Users and Databases in a Database Cluster

Validated on 8 Aug 2019 • Last edited on 14 Aug 2024

PostgreSQL is an open source, object-relational database built for extensibility, data integrity, and speed. Its concurrency support makes it fully ACID-compliant, and it supports dynamic loading and catalog-driven operations to let users customize its data types, functions, and more.

PostgreSQL database clusters come configured with a default database (defaultdb) and a default administrative user (doadmin). These defaults are necessary for cluster replication and administration, so you can’t delete them, but you can add additional users and databases.

By default, every database cluster is publicly accessible. To limit access, you can add trusted sources or manage user permissions.

Tip
If you connect to the database cluster with preconfigured connection details from the cluster’s Overview page, you can edit them to use a different user and password or a different database.

Add or Delete a Database User Using the CLI

How to Create a Database User Using the DigitalOcean CLI

  1. Install doctl, the DigitalOcean command-line tool.

  2. Create a personal access token and save it for use with doctl.

  3. Use the token to grant doctl access to your DigitalOcean account.

              doctl auth init

  4. Finally, run doctl databases user create. Basic usage looks like this, but you can read the usage docs for more details:

                doctl databases user create <database-cluster-id> <user-name> [flags]

    The following example creates a new user with the username example-user for a database cluster with the ID ca9f591d-f38h-5555-a0ef-1c02d1d1e35:

                  doctl databases user create ca9f591d-f38h-5555-a0ef-1c02d1d1e35 example-user
How to Delete a Database User Using the DigitalOcean CLI

  1. Install doctl, the DigitalOcean command-line tool.

  2. Create a personal access token and save it for use with doctl.

  3. Use the token to grant doctl access to your DigitalOcean account.

              doctl auth init

  4. Finally, run doctl databases user delete. Basic usage looks like this, but you can read the usage docs for more details:

                doctl databases user delete <database-cluster-id> <user-id> [flags]

    The following example deletes the user with the username example-user for a database cluster with the ID ca9f591d-f38h-5555-a0ef-1c02d1d1e35:

                  doctl databases user delete ca9f591d-f38h-5555-a0ef-1c02d1d1e35 example-user

Add or Delete a Database User Using the API

How to Create a Database User Using the DigitalOcean API

  1. Create a personal access token and save it for use with the API.

  2. Send a POST request to https://api.digitalocean.com/v2/databases/{database_cluster_uuid}/users

    cURL

    Using cURL:

                    curl -X POST \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $DIGITALOCEAN_TOKEN" \
  -d '{"name": "app-01"}' \
  "https://api.digitalocean.com/v2/databases/9cc10173-e9ea-4176-9dbc-a4cee4c4ff30/users"

    Go

    Using Godo, the official DigitalOcean V2 API client for Go: 

                    import (
    "context"
    "os"

    "github.com/digitalocean/godo"
)

func main() {
    token := os.Getenv("DIGITALOCEAN_TOKEN")

    client := godo.NewFromToken(token)
    ctx := context.TODO()

    addUserRequest := &godo.DatabaseCreateUserRequest{
        Name: "app-01",
    }

    user, _, err := client.Databases.CreateUser(ctx, "88055188-9e54-4f21-ab11-8a918ed79ee2", addUserRequest)

}

    Python

                    import os
from pydo import Client

client = Client(token=os.environ.get("DIGITALOCEAN_TOKEN"))

add_user_resp = client.databases.add_user(database_cluster_uuid="ab7bb7a", body={"name": "app-01"})
How to Delete a Database User Using the DigitalOcean API

  1. Create a personal access token and save it for use with the API.

  2. Send a DELETE request to https://api.digitalocean.com/v2/databases/{database_cluster_uuid}/users/{username}

    cURL

    Using cURL:

                    curl -X DELETE \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $DIGITALOCEAN_TOKEN" \
  "https://api.digitalocean.com/v2/databases/9cc10173-e9ea-4176-9dbc-a4cee4c4ff30/users/app-01"

    Go

    Using Godo, the official DigitalOcean V2 API client for Go: 

                    import (
    "context"
    "os"

    "github.com/digitalocean/godo"
)

func main() {
    token := os.Getenv("DIGITALOCEAN_TOKEN")

    client := godo.NewFromToken(token)
    ctx := context.TODO()

    _, err := client.Databases.DeleteUser(ctx, "9cc10173-e9ea-4176-9dbc-a4cee4c4ff30", "app-01")
}

    Python

                    import os
from pydo import Client

client = Client(token=os.environ.get("DIGITALOCEAN_TOKEN"))

delete_resp = client.databases.delete_user(database_cluster_uuid="aba134a", username="backend_user1")

Add or Delete a Database User Using the Control Panel

To add or delete users or databases to a database cluster, click the name of the database to go to its Overview page, then select the Users & Databases tab.

Screenshot of Users and Databases screen

Create a new database in the Databases section by entering a name in the Add new database field and clicking Save. You can also delete a database here by opening its More menu, clicking Delete, and then confirming the deletion.

Create new users in the Users section by entering a name in the Add new user field and clicking Save. New users receive minimal permissions by default. You can then add user privileges using third-party tools like psql. However, you cannot grant users some superuser permissions.

You can also delete a user here by opening the user’s More menu, clicking Delete, and then confirming the deletion. Similarly, to reset a user’s password, open the user’s More menu and select Reset password. If you have any services that depend on that user’s credentials, such as log forwarding, resetting the password may break that service.

Control Panel Blog Pricing Careers Terms Privacy Status API Docs Tutorials Support