How to Manage Users in Vultr Managed Databases for PostgreSQL

Introduction

PostgreSQL is an open-source relational database management system (RDBMS) that lets you create databases and manage records. Vultr Managed Databases for PostgreSQL offer a secure, highly available, and scalable solution you can use in your production applications without managing the database infratsurcture directly.

This guide explains how to manage users in a Vultr Managed Database for PostgreSQL cluster. You will set up new users using the Vultr Customer Portal, the Vultr API, and the Vultr CLI tool to create standard database users. To manage user privileges, you will apply the PostgreSQl psql CLI tool to assign the database users necessary privileges to access your managed database.

Prerequisites

Before you begin:

• Deploy a Vultr Managed Database for PostgreSQL
• Enable the Personal Access Token API key in your Vultr Account Settings and allow your computer's IP Address to access the key
• Install the PostgreSQL psql CLI tool to access your Vultr Managed Database for MySQL
• Install the Vultr CLI tool on your computer
• Install the Postman API desktop application

Create a New User using the Vultr Customer Portal

In this section, you are to create a user from the Vultr Customer Portal.

1. Open the Vultr Customer Portal.

2. Click the Products menu group and navigate to Databases to access your available managed databases

   

3. Click your target Vultr Managed Database for PostgreSQL to open the cluster control panel.

4. Navigate to the Users & Databases tab.

   

5. Verify that the default database vultradmin user is available in the Users section and the default database defaultdb in the Databases section.

   

6. To create a new user, click Add New User within the Users section.

7. Enter your desired database user Username and Password in the respective fields. Leave the Password field empty to assign an auto-generated password to the new user.

   

8. Click the Create New User button to create the database user

9. When successful, verify that the new user is available in your Users section

   

You have created a new Vultr Managed Database for PostgreSQL user using the Vultr Customer Portal. Use the PostgreSQL psql CLI to assign the new user different privileges and access to multiple databases within the cluster.

Create a New PostgreSQL User using the Vultr API

The Vultr API key grants you access to all infrastructure resources assigned to your Vultr account. You can use your Vultr API key to set up a new Vultr Managed Database for PostgreSQL user using the Postman API platform or the Vultr CLI tool.

Depending on your desired API tool, the Vultr API uses your target PostgreSQL database ID to set up new users within the cluster. Follow the optional steps below to create a new Vultr Managed Database for PostgreSQL user with your Vultr API key.

Create a New User using the Postman API Platform

1. Access your Postman API Platform interface.

2. Within the Collections tab, click the + New Collection button.

   

3. Select Blank Collection from the drop-down list to set up a new collection.

4. Click the New Collection name and rename the value to Vultr API Connection.

   

5. Within the collection, click the Variables tab.

6. Click the Add new variable field, and set up the following two variables with the respective Initial Value.

   • VARIABLE: domain VALUE: https://api.vultr.com/v2
   • VARIABLE: VULTR_API_KEY VALUE: Your-Vultr-API-Key

   

7. Click the Save button on the collection top bar to save changes.

8. Navigate to the Authorization tab.

9. Click the Type drop-down and select Bearer Token from the list of options.

10. In the Token field, call your Vultr API Key variable in the format {{VULTR_API_KEY}} to apply it to the field and use it within your collection.

    

11. Click the Save button to apply changes.

12. On the left Collections navigation menu, right-click your collection and select Add a request from the list.

    

13. Get a list of your available Vultr Managed Databases:

    • Keep GET as the selected Request type, and call your domain variable {{domain}} to represent the https://api.vultr.com/v2/ URL. Then, add /databases after the variable to create the https://api.vultr.com/v2/databases API endpoint.

      

    • Click the Send button to generate an array of databases available on your Vultr account in the bottom Body section.

      

    • Find your target PostgreSQL database, select and copy the assigned id value.

    • Within the upper section of your request, set up a new Key and Value in Params tab to use in your user queries with the following values:

      • Key: databaseid Value: Your target PostgreSQL database ID

      

    • Click the Save button to apply changes and use your new databaseid parameter value

14. Get the list of available database users:

    • Within your GET request field, increment your field with :databaseId/users to create the API endpoint https://api.vultr.com/v2/databases/:databaseId/users.

    • Click Send to generate an array of available users in your Vultr Managed Database for PostgreSQL.

      

    • Verify that the default vultradmin user details for the selected database are available on the list.

15. Create a new database user:

    • Click the GET request method type, and select POST from the list of options

      

    • Navigate to the request Body section, and select the raw radio button

    • Find and click the new Text drop-down at the end of the menu list. Select JSON from the list of options to set it as the request body

      

    • Within the Body field, enter the following configurations. Replace new-user2 with your desired username, and strong-password with your user password:

      json Copy

      {
          "username": "new_user2",
          "password": "strong-password"
      }
      

      

    • Click Send to create the new database user.

      

16. To verify the new database users list, change the Body type back to none and click the POST dropdown to change the value to GET. Then, click Send to send a new request and verify the list of available database users.

    

Create a New User using the Vultr CLI

1. Open a new terminal session on your computer.

2. View the available Vultr CLI help commands list to verify that the tool is available.

   console Copy

   $ vultr-cli -h
   

3. Export your Vultr API Key as a new environment variable.

   console Copy

   $ export VULTR_API_KEY=[Paste-Your-Vultr-API-KEY-Here]
   

4. Using vultr-cli, get the list of available databases on your Vultr account.

   console Copy

   $ vultr-cli database list
   

   Verify and copy your target database ID value similar to the output below:

   $ ID                         9-ec33-4776-a99a-09
   $ DATE CREATED               2023-11-04 06:33:54
   $ PLAN                       vultr-dbaas-hobbyist-cc-1-25-1
   $ PLAN DISK                  25
   $ PLAN RAM                   1024
   $ PLAN VCPUS                 1
   $ PLAN REPLICAS              0
   $ REGION                     NRT
   $ DATABASE ENGINE            pg
   $ DATABASE ENGINE VERSION    15
   $ VPC ID
   $ STATUS                     Running
   $ LABEL                      test-db
   $ TAG
   $ DB NAME                    defaultdb
   $ HOST                       vultr-prod-9-vultr-prod-169a.vultrdb.com
   $ USER                       vultradmin
   $ PASSWORD                   strong-admin-password
   $ PORT                       16751
   $ MAINTENANCE DOW            saturday
   $ MAINTENANCE TIME           14:00
   $ LATEST BACKUP              2023-11-04 10:40:00
   $ TRUSTED IPS                []
   $
   $ PG AVAILABLE EXTENSIONS
   $ NAME                       VERSIONS
   $ address_standardizer       [3.2.4]
   $

5. Get the list of users available on your target database. Replace [databaseid] with the ID value generated in your databases output.

   console Copy

   $ vultr-cli database user list [databaseId]
   

   Verify that all available database users are listed in your output similar to the one below:

   $ USERNAME      vultradmin
   $ PASSWORD      ************
   $ ---------------------------
   $ USERNAME      new_user
   $ PASSWORD      ************
   $ ---------------------------
   $ USERNAME      new_user2
   $ PASSWORD      ************
   $ ---------------------------
   $ ======================================
   $ TOTAL
   $ 3

6. To create a new database user, run the following command. Replace new_user1 and strong-password with your desired user values

   console Copy

   $ vultr-cli database user create <databaseId> -u <new_user1> -p <strong-password>
   

   Output:

   $ USERNAME      new_user1
   $ PASSWORD      ************

7. Verify the new list of database users.

   console Copy

   $ vultr-cli database user list <databaseId>
   

   Output:

   $ USERNAME      vultradmin
   $ PASSWORD      ************
   $ ---------------------------
   $ USERNAME      new_user
   $ PASSWORD      ************
   $ ---------------------------
   $ USERNAME      new_user2
   $ PASSWORD      ************
   $ ---------------------------
   $ USERNAME      new_user1
   $ PASSWORD      ************
   $ ---------------------------
   $ ======================================
   $ TOTAL
   $ 4

You have created a Vultr Managed Database for PostgreSQL user using the Vultr CLI tool. When successful, you can manage the new user privileges using the PostgreSQL psql CLI tool.

Manage the Vultr Managed Database for PostgreSQL User Privileges

To manage user privileges, you must use a PostgreSQL client such as psql to set up the new user privileges. By default, a Vultr Managed Database for PostgreSQL Cluster contains the administrative user vultradmin with super user privileges to every database in the cluster. Follow the steps in this section to assign your new database user the necessary privileges to databases within the cluster.

1. To access your database, copy your connection string from your Vultr Managed Database for PostgreSQL control panel.

   console Copy

   $ psql <connection-string>
   

   Output:

   psql (16.0, server 15.4)
   SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
   Type "help" for help.
   
   defaultdb=>

2. View the list of available database users and roles.

   sql Copy

   defaultdb=> \du;
   

   Your output should look like the one below:

   defaultdb=> List of roles
   defaultdb=> Role name  |                         Attributes
   defaultdb=> -----------+------------------------------------------------------------
   defaultdb=> _vultrdb   | Superuser, Replication
   defaultdb=> new_user   |
   defaultdb=> new_user1  |
   defaultdb=> new_user2  |
   defaultdb=> postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS
   defaultdb=> vultradmin | Create role, Create DB, Replication, Bypass RLS

3. To view a specific user's privileges, for example vultradmin, run the following command.

   sql Copy

   defaultdb=> SELECT table_catalog, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'vultradmin';
   

   Output:

   defaultdb=>  table_catalog | table_schema | table_name | privilege_type
   defaultdb=> ---------------+--------------+------------+----------------
   defaultdb=> defaultdb     | public       | posts      | INSERT
   defaultdb=> defaultdb     | public       | posts      | SELECT
   defaultdb=> defaultdb     | public       | posts      | UPDATE
   defaultdb=> defaultdb     | public       | posts      | DELETE
   defaultdb=> defaultdb     | public       | posts      | TRUNCATE
   defaultdb=> defaultdb     | public       | posts      | REFERENCES
   defaultdb=> defaultdb     | public       | posts      | TRIGGER
   defaultdb=> (7 rows)

Grant Database User Privileges

1. To grant all privileges for a specific database to a user, use the GRANT ALL ON DATABASE command with your target PostgreSQL database. For example, grant the user new_user full privileges to the testdb database

   sql Copy

   defaultdb=> GRANT ALL ON DATABASE testdb TO new_user;
   

2. To grant privileges on a particular table to the user, switch to a database, and apply privileges to the target table. For example, test user privileges on the table posts:

   • Grant all privileges on an existing table within the working database.

     sql Copy

     defaultdb=> GRANT ALL ON posts TO new_user1;
     

   • Grant only READ privileges to the user using the GRANT SELECT command

     sql Copy

     defaultdb=> GRANT SELECT ON posts TO new_user1;
     

   • Grant only INSERT privileges to the user

     sql Copy

     defaultdb=> GRANT INSERT ON posts TO new_user1;
     

   The above commands assign the database user new_user1 the necessary privileges to the posts table.

Revoke Database User Privileges

1. To revoke all permissions on a database from a user, for example defaultdb, run the following command.

   sql Copy

   defaultdb=> REVOKE ALL ON DATABASE defaultdb FROM new_user;
   

   The above SQL statement revokes all the new_user database user privileges on the defaultdb database.

2. Revoke the user's permissions to a specific table. For example posts.

   sql Copy

   defaultdb=> REVOKE ALL ON posts FROM new_user2;
   

3. To revoke INSERT and DELETE privileges, use the REVOKE INSERT, DELETE command and specify the target database table.

   sql Copy

   defaultdb=> REVOKE INSERT, DELETE ON posts FROM new_user1;
   

   The above SQL command removes INSERT and DELETE privileges for the database user new_user_1 user on the posts table.

4. To only allow READ privileges on a particular table, use the REVOKE ALL command and GRANT SELECT command to remove all privileges, but assign the user READ access to the table

   • Revoke all user privileges on a particular table.

     sql Copy

     defaultdb=> REVOKE ALL ON posts FROM new_user;
     

   • Grant table READ privileges to the user

     sql Copy

     defaultdb=> GRANT SELECT ON posts TO new_user;
     

Conclusion

In this guide, you have created new Vultr Managed Database for PostgreSQL users using the Vultr Customer Portal, Vultr API, and the Vultr CLI tool. In addition, you managed new user permissions using the PostgreSQL psql client tool to assign different privileges on database resources. Depending on your desired database tools, you can set up multiple users, and assign different privileges to each user to structure your database access hierachy.

For more information about Vultr Managed Databases, please visit the following resources:

• Vultr Managed Databases Quickstart
• Vultr Managed Databases for PostgreSQL Reference Guide