Managing users, roles, and privileges

Gen 2

As part of provisioning a new deployment in IBM Cloud®, you can use the service credential console page to create a user with different roles (Manager and Writer).

Databases for PostgreSQL deployments no longer include a default admin user. Instead, customers create a user with the Manager or Writer role using the IBM Cloud® service credential interface — via UI or CLI. These users come with necessary credentials to connect to and manage the deployment.

The manager user

The manager user functions as a admin-like user and is automatically granted the PostgreSQL default role pg_monitor, which provides access to monitoring views and functions within the database. The created user has the CREATEROLE and CREATEDB privileges, inheriting permissions from both ibm_admin and ibm_writer, enabling broader access and management capabilities within the deployment.

The manageruser (admin-like) comes with the following roles:

pg_read_all_data
pg_write_all_data
pg_monitor
pg_read_all_settings
pg_read_all_stats
pg_stat_scan_tables
pg_signal_backend
pg_checkpoint
pg_create_subscription

When the manager user (admin-like) creates a resource in a database, like a table, the user owns that object. Resources that are created by manager user are not accessible by other users, unless you expressly grant permissions to them.

The biggest difference between the manager user and any other users you add to your deployment is the pg_monitor and pg_signal_backend roles. The pg_monitor role provides a set of permissions that makes the manager user appropriate for monitoring the database server. The pg_signal_backend role provides the manager user the ability to send signals to cancel queries and connections that are initiated by other users. It is not able to send signals to processes owned by superusers.

You can also use the manager user to grant the following two roles to other users on your deployment.

To expose the ability to cancel queries to other database users, grant the pg_signal_backend role from the manager user. Use a command like:

GRANT pg_signal_backend TO joe;

To set up a specific monitoring user, mary, use a command like:

GRANT pg_monitor TO mary;

Changing the user password in the UI

Changing a user password is not supported via the IBM Cloud console on Gen 2. However, you can update a password using tools, such as psql by executing the following command:

ALTER ROLE username WITH PASSWORD 'new_password';

Creating the manager user in the CLI

Use one of the following commands from the IBM Cloud CLI Cloud Databases plug-in to create the manager user.

ibmcloud resource service-key-create <service_key_name> Manager --instance-name <instance_name>

ibmcloud resource service-key-create <service_key_name> Manager --instance-id <guid>

These commands can be used when creating a user with either the Writer or Manager role. In this example, the command creates a PostgreSQL user (ibmcloud_d0388…) with CREATEROLE and CREATEDB privileges. This user inherits permissions from both ibm_admin and ibm_writer, enabling broader access and management capabilities within the deployment.

rolname rolsuper rolinherit rolcreaterole rolcreatedb rolcanlogin memberof
ibm_admin f t f f f {pg_read_all_data,pg_write_all_data,pg_monitor,pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables,pg_signal_backend,pg_checkpoint,pg_create_subscription}
ibm_monitoring f t f f t {pg_use_reserved_connections}
ibm_rest f f t t t {pg_use_reserved_connections,ibm_admin,ibm_writer,ibmcloud_d0388c96d13841b1b45f1039c73ea6c7}
ibm_rewind f t f f t {}
ibm_superuser t t t t t {}
ibm_writer f t f f f {pg_read_all_data,pg_write_all_data}
ibmcloud_d0388c96d13841b1b45f1039c73ea6c7 f t t t t {ibm_admin,ibm_writer}
{: caption="ibm_admin and ibm_writer permissions" caption-side="top"}

Similarly, for creating a user with the Writer role, use the following command:

ibmcloud resource service-key-create <service_key_name> Writer --instance-name <instance_name>

The user (ibmcloud_b153...) with writer role inherits the ibm_writer permissions:

rolname rolsuper rolinherit rolcreaterole rolcreatedb rolcanlogin memberof
ibm_admin f t f f f {pg_read_all_data,pg_write_all_data,pg_monitor,pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables,pg_signal_backend,pg_checkpoint,pg_create_subscription}
ibm_monitoring f f f f t {pg_monitor,pg_use_reserved_connections}
ibm_replication f t f f t {pg_use_reserved_connections}
ibm_rest f f t t t {pg_use_reserved_connections,ibm_admin,ibm_writer,ibmcloud_b153b496ae5a41a08a27db730e43b835}
ibm_rewind f t f f t {}
ibm_superuser t t t t t {}
ibm_writer f t f f f {pg_read_all_data,pg_write_all_data}
ibmcloud_b153b496ae5a41a08a27db730e43b835 f t f f t {ibm_writer}
{: caption="ibm_writerpermissions" caption-side="top"}

Deleting the user in the CLI

Use the following command from the IBM Cloud CLI Cloud Databases plug-in to delete the created user.

ibmcloud resource service-key-delete <service_key_name>

Changing the user password in the CLI

Changing a user password is not supported via the CLI on Gen 2. However, you can update a password using tools, such as psql by executing the following command:

ALTER ROLE username WITH PASSWORD 'new_password';

Changing the user password through the API

Changing a user password is not supported via API on Gen 2. However, you can update a password using tools, such as psql by executing the following command:

ALTER ROLE username WITH PASSWORD 'new_password';

Users created with psql

You can bypass creating users through IBM Cloud entirely, and create users directly in PostgreSQL with psql. This allows you to use PostgreSQL's native role and user management. Users/roles created in psql must have all of their privileges set manually, as well as privileges to the objects that they create.

Users that are created directly in PostgreSQL do not appear in Service credentials, but you can add them if you choose.

Note that these users are not integrated with IAM controls, even if added to Service credentials.

Additional users and connection strings

Access to your Databases for PostgreSQL deployment is not limited to the manager user. Additional users can be created using the CLI, with the Cloud Databases CLI plug-in, or the Cloud Databases API.

All users on your deployment can use the connection strings, including connection strings for private endpoints.

When you create a user, it is assigned certain database roles and privileges. These privileges include the ability to log in, create databases, and create other users.

Creating users in the UI

  1. Go to the service dashboard for your service.
  2. Click Service credentials to open Service credentials.
  3. Click New credential.
  4. Choose a descriptive name for your new credential.
  5. Optional Specify if the new credentials should use a public or private endpoint. Use { "service-endpoints": "private" } in the Add inline configuration parameters field to generate connection strings using the specified endpoint. Use of the endpoint is not enforced, it just controls which hostnames are in the connection strings. Private endpoints are generated by default. On Gen 2, only private endpoints are supported.
  6. Click Add to provision the new credentials. A username and password, and an associated database user in the PostgreSQL database are auto-generated.

The new credentials appear in the table, and the connection strings are available as JSON in a click-to-copy field under View credentials.

Creating users from the CLI

If you manage your service through the IBM Cloud CLI and the cloud databases plug-in, you can create a new user with cdb user-create. For example, to create a new user for an "example-deployment", use the following command:

ibmcloud cdb user-create example-deployment <NEW_USER_NAME> <NEW_PASSWORD>

Once the task has finished, you can retrieve the new user's connection strings with the ibmcloud cdb deployment-connections command.

Creating users from the API

The Foundation endpoint that is shown on the Overview panel Deployment details of your service provides the base URL to access this deployment through the API. To create and manage users, use the base URL with the /users endpoint.

curl -X POST 'https://api.{region}.databases.cloud.ibm.com/v4/ibm/deployments/{id}/users' \
-H "Authorization: Bearer $APIKEY" \
-H "Content-Type: application/json" \
-d '{"username":"jane_smith", "password":"newsupersecurepassword"}'

After the task finishes, retrieve the new user's connection strings from the /users/{userid}/connections endpoint.

Adding users to Service credentials

Creating a new user from the CLI or API doesn't automatically populate that user's connection strings into Service credentials. To add them, create a new credential with the existing user information.

Enter the username and password in the JSON field Add inline configuration parameters, or specify a file where the JSON information is stored. For example, putting {"existing_credentials":{"username":"Robert","password":"supersecure"}} in the field generates Service credentials with the username "Robert" and password "supersecure" filled into connection strings.

Generating credentials from an existing user does not check for or create that user.