How to Manage PostgreSQL Users and Databases in a Database Cluster
Validated on 8 Aug 2019 • Last edited on 7 Jun 2023
PostgreSQL is an open source, object-relational database built with a focus on 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.
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
To create a database user via the command-line, follow these steps:
Use the token to grant doctl access to your
DigitalOcean account.
doctl auth init
Finally, create a database user with
doctl databases user create. The basic usage looks
like this, but you'll want to read the usage docs for more details:
doctl databases user create <database-id> <user-name> [flags]
How to delete a database user using the DigitalOcean CLI
To delete a database user via the command-line, follow these steps:
Use the token to grant doctl access to your
DigitalOcean account.
doctl auth init
Finally, delete a database user with
doctl databases user delete. The basic usage looks
like this, but you'll want to read the usage docs for more details:
doctl databases user delete <database-id> <user-id> [flags]
Add or Delete a Database User Using the API
How to create a database user using the DigitalOcean API
To create a database user using the DigitalOcean API, follow these steps:
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.
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.