Manage database users on a DBaaS instance

Give each application its own user on a DBaaS instance, with only the rights it needs, and keep the admin user for managing the database. The admin user is the Username under Connection Details, admin by default, or the name you chose when you created the DBaaS instance. You use the admin user to create the other users and grant them rights.

Before you begin

You need to connect to the DBaaS instance as the admin user; see Connect to a DBaaS instance. The commands work on MySQL 8.0, MariaDB 10.11, and PostgreSQL 18.

In these commands, reporting is an example read-only user and webapp an example user that reads and writes; use your own names. <database-name> is the database the application uses, and <password> is the new user’s password. Choose a long, random password for each user; the DBaaS instance takes whatever password you set, so its strength is up to you.

MySQL and MariaDB users

Create every user for any host, as '<user>'@'%', where <user> is the user’s name. Connections reach the DBaaS instance through E2E’s network, so a user created for '%' is the one that signs in. A user created for one address has its connection dropped; see Troubleshooting DBaaS.

Create a read-only MySQL or MariaDB user

Connect as the admin user, then run:

CREATE USER 'reporting'@'%' IDENTIFIED BY '<password>';
GRANT SELECT ON `<database-name>`.* TO 'reporting'@'%';

The user can read every table in that database. A write, such as an INSERT, gets ERROR 1142.

Create a MySQL or MariaDB user that reads and writes

CREATE USER 'webapp'@'%' IDENTIFIED BY '<password>';
GRANT SELECT, INSERT, UPDATE, DELETE ON `<database-name>`.* TO 'webapp'@'%';

The user can read and change rows in every table in that database.

Change a MySQL or MariaDB user’s password

Run this with the user’s new password as <new-password>:

ALTER USER 'reporting'@'%' IDENTIFIED BY '<new-password>';

Update the application that uses it.

Remove a MySQL or MariaDB user

An application that signs in as this user is refused once you remove it, so move the application to another user first. Then run:

DROP USER 'reporting'@'%';

The user is removed from the DBaaS instance.

PostgreSQL users

In PostgreSQL, a user is a role that can sign in. A new user can connect to every database on the DBaaS instance and can read what you grant it. Run the GRANT lines while connected to <database-name>.

Create a read-only PostgreSQL user

Connect as the admin user, then run:

CREATE ROLE reporting LOGIN PASSWORD '<password>';
GRANT CONNECT ON DATABASE "<database-name>" TO reporting;
GRANT USAGE ON SCHEMA public TO reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reporting;

The user can read every table in the public schema, including tables the admin user creates later. A write, such as an INSERT, gets permission denied for table.

Create a PostgreSQL user that reads and writes

CREATE ROLE webapp LOGIN PASSWORD '<password>';
GRANT CONNECT ON DATABASE "<database-name>" TO webapp;
GRANT USAGE ON SCHEMA public TO webapp;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO webapp;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO webapp;

The user can read and change rows in every table in the public schema. Creating tables stays with the admin user: a CREATE TABLE gets permission denied for schema public.

Change a PostgreSQL user’s password

Run this with the user’s new password as <new-password>:

ALTER ROLE reporting PASSWORD '<new-password>';

The new password works at once. Update the application that uses it.

Remove a PostgreSQL user

An application that signs in as this user is refused once you remove it, so move the application to another user first. Then take the user’s rights back in every database where you granted them, connected to each database in turn:

ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON TABLES FROM reporting;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM reporting;
REVOKE ALL ON SCHEMA public FROM reporting;
REVOKE ALL ON DATABASE "<database-name>" FROM reporting;

After the last database, drop the user once:

DROP ROLE reporting;

The user is removed from the DBaaS instance. If DROP ROLE reports that objects depend on the role, see Troubleshooting DBaaS.

The admin user’s password

To reset the admin user’s password, see Reset the DBaaS instance password.

Your applications now each have their own user. Next, limit which addresses can reach the DBaaS instance; see Whitelist an IP address for a DBaaS instance.