--- title: "Manage database users on a DBaaS instance" description: "Create MySQL, MariaDB, or PostgreSQL users on a DBaaS instance with only the rights an application needs, change their passwords, and remove them." content_type: "how-to" product: "database" platform: "MyAccount" url: "/myaccount/database/how-to/manage-database-users/" topic: ["security-and-access"] relationships: {"requires":["/myaccount/database/how-to/connect-to-a-database/"]} --- # 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](/myaccount/database/how-to/connect-to-a-database/). 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. `` is the database the application uses, and `` 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 `''@'%'`, where `` 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](/myaccount/database/troubleshooting/database/#error-2013-lost-connection-for-a-user-you-created). ### Create a read-only MySQL or MariaDB user Connect as the admin user, then run: ```sql CREATE USER 'reporting'@'%' IDENTIFIED BY ''; GRANT SELECT ON ``.* 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 ```sql CREATE USER 'webapp'@'%' IDENTIFIED BY ''; GRANT SELECT, INSERT, UPDATE, DELETE ON ``.* 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 ``: ```sql ALTER USER 'reporting'@'%' IDENTIFIED BY ''; ``` 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: ```sql 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 ``. ### Create a read-only PostgreSQL user Connect as the admin user, then run: ```sql CREATE ROLE reporting LOGIN PASSWORD ''; GRANT CONNECT ON DATABASE "" 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 ```sql CREATE ROLE webapp LOGIN PASSWORD ''; GRANT CONNECT ON DATABASE "" 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 ``: ```sql ALTER ROLE reporting 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: ```sql 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 "" FROM reporting; ``` After the last database, drop the user once: ```sql DROP ROLE reporting; ``` The user is removed from the DBaaS instance. If `DROP ROLE` reports that objects depend on the role, see [Troubleshooting DBaaS](/myaccount/database/troubleshooting/database/#role--cannot-be-dropped-because-some-objects-depend-on-it). ## The admin user's password To reset the admin user's password, see [Reset the DBaaS instance password](/myaccount/database/how-to/reset-the-database-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](/myaccount/database/how-to/whitelist-an-ip-address/).