Introduction
PostgreSQL is an open-source relational database management system (RDBMS) that lets you create databases and manage records. Rcs 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 Rcs Managed Database for PostgreSQL cluster. You will set up new users using the Rcs Customer Portal, the Rcs API, and the Rcs 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 Rcs Managed Database for PostgreSQL
- Enable the Personal Access Token API key in your Rcs Account Settings and allow your computer's IP Address to access the key
- Install the PostgreSQL
psql
CLI tool to access your Rcs Managed Database for MySQL - Install the Rcs CLI tool on your computer
- Install the Postman API desktop application
Create a New User using the Rcs Customer Portal
In this section, you are to create a user from the Rcs Customer Portal.
Open the Rcs Customer Portal.
Click the Products menu group and navigate to Databases to access your available managed databases
Click your target Rcs Managed Database for PostgreSQL to open the cluster control panel.
Navigate to the Users & Databases tab.
Verify that the default database
vultradmin
user is available in the Users section and the default databasedefaultdb
in the Databases section.To create a new user, click Add New User within the Users section.
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.
Click the Create New User button to create the database user
When successful, verify that the new user is available in your Users section
You have created a new Rcs Managed Database for PostgreSQL user using the Rcs 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 Rcs API
The Rcs API key grants you access to all infrastructure resources assigned to your Rcs account. You can use your Rcs API key to set up a new Rcs Managed Database for PostgreSQL user using the Postman API platform or the Rcs CLI tool.
Depending on your desired API tool, the Rcs API uses your target PostgreSQL database ID to set up new users within the cluster. Follow the optional steps below to create a new Rcs Managed Database for PostgreSQL user with your Rcs API key.
Create a New User using the Postman API Platform
Access your Postman API Platform interface.
Within the Collections tab, click the +
New Collection
button.Select
Blank Collection
from the drop-down list to set up a new collection.Click the
New Collection
name and rename the value toRcs API Connection
.Within the collection, click the Variables tab.
Click the
Add new variable
field, and set up the following two variables with the respectiveInitial Value
.- VARIABLE:
domain
VALUE:https://api.vultr.com/v2
- VARIABLE:
VULTR_API_KEY
VALUE:Your-Rcs-API-Key
- VARIABLE:
Click the Save button on the collection top bar to save changes.
Navigate to the Authorization tab.
Click the Type drop-down and select
Bearer Token
from the list of options.In the Token field, call your Rcs API Key variable in the format
{{VULTR_API_KEY}}
to apply it to the field and use it within your collection.Click the Save button to apply changes.
On the left Collections navigation menu, right-click your collection and select
Add a request
from the list.Get a list of your available Rcs Managed Databases:
Keep GET as the selected Request type, and call your domain variable
{{domain}}
to represent thehttps://api.vultr.com/v2/
URL. Then, add/databases
after the variable to create thehttps://api.vultr.com/v2/databases
API endpoint.Click the Send button to generate an array of databases available on your Rcs 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
andValue
in Params tab to use in your user queries with the following values:- Key:
databaseid
Value:Your target PostgreSQL database ID
- Key:
Click the Save button to apply changes and use your new
databaseid
parameter value
Get the list of available database users:
Within your GET request field, increment your field with
:databaseId/users
to create the API endpointhttps://api.vultr.com/v2/databases/:databaseId/users
.Click Send to generate an array of available
users
in your Rcs Managed Database for PostgreSQL.Verify that the default
vultradmin
user details for the selected database are available on the list.
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, andstrong-password
with your user password:json{ "username": "new_user2", "password": "strong-password" }
Click Send to create the new database user.
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 Rcs CLI
Open a new terminal session on your computer.
View the available Rcs CLI help commands list to verify that the tool is available.
console$ vultr-cli -h
Export your Rcs API Key as a new environment variable.
console$ export VULTR_API_KEY=[Paste-Your-Rcs-API-KEY-Here]
Using
vultr-cli
, get the list of available databases on your Rcs account.console$ 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] $
Get the list of users available on your target database. Replace
[databaseid]
with theID
value generated in your databases output.console$ 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
To create a new database user, run the following command. Replace
new_user1
andstrong-password
with your desired user valuesconsole$ vultr-cli database user create <databaseId> -u <new_user1> -p <strong-password>
Output:
$ USERNAME new_user1 $ PASSWORD ************
Verify the new list of database users.
console$ 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 Rcs Managed Database for PostgreSQL user using the Rcs CLI tool. When successful, you can manage the new user privileges using the PostgreSQL psql
CLI tool.
Manage the Rcs 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 Rcs 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.
To access your database, copy your connection string from your Rcs Managed Database for PostgreSQL control panel.
console$ 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=>
View the list of available database users and roles.
sqldefaultdb=> \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
To view a specific user's privileges, for example
vultradmin
, run the following command.sqldefaultdb=> 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
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 usernew_user
full privileges to thetestdb
databasesqldefaultdb=> GRANT ALL ON DATABASE testdb TO new_user;
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.
sqldefaultdb=> GRANT ALL ON posts TO new_user1;
Grant only READ privileges to the user using the
GRANT SELECT
commandsqldefaultdb=> GRANT SELECT ON posts TO new_user1;
Grant only INSERT privileges to the user
sqldefaultdb=> GRANT INSERT ON posts TO new_user1;
The above commands assign the database user
new_user1
the necessary privileges to theposts
table.
Revoke Database User Privileges
To revoke all permissions on a database from a user, for example
defaultdb
, run the following command.sqldefaultdb=> REVOKE ALL ON DATABASE defaultdb FROM new_user;
The above SQL statement revokes all the
new_user
database user privileges on thedefaultdb
database.Revoke the user's permissions to a specific table. For example
posts
.sqldefaultdb=> REVOKE ALL ON posts FROM new_user2;
To revoke INSERT and DELETE privileges, use the
REVOKE INSERT, DELETE
command and specify the target database table.sqldefaultdb=> 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 theposts
table.To only allow READ privileges on a particular table, use the
REVOKE ALL
command andGRANT SELECT
command to remove all privileges, but assign the userREAD
access to the tableRevoke all user privileges on a particular table.
sqldefaultdb=> REVOKE ALL ON posts FROM new_user;
Grant table
READ
privileges to the usersqldefaultdb=> GRANT SELECT ON posts TO new_user;
Conclusion
In this guide, you have created new Rcs Managed Database for PostgreSQL users using the Rcs Customer Portal, Rcs API, and the Rcs 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 Rcs Managed Databases, please visit the following resources: