Docs / Managed databases

Users and databases

5 minute read

Every managed database starts with one admin user and one database. Give each app its own user with only the rights it needs, so you can rotate or remove one without touching the others. You can manage users and databases from the console, the simplicloud db commands, Terraform or the API.

1. Create a database

Open Databases, pick orders-db and choose Users and databases, then Add database. For PostgreSQL and MySQL you can keep several databases on one server; Redis uses numbered databases 0–15 and has no create step.

simplicloud db databases create orders-db reporting
simplicloud db databases list orders-db
# orders     owner admin
# reporting  owner admin

2. Add a user for each app

Choose Add user and type a name such as app_rw. The password is generated for you and shown once in the console; you can show it again later from the same screen. Owners, admins and developers can add users; viewers cannot.

simplicloud db users create orders-db app_rw
# user      app_rw
# password  q8Vt2mLx0RkP4sYw   (copy it now)
simplicloud db users create orders-db reports_ro

3. Limit what each user can do

New users can connect but own nothing. Grant rights with normal SQL while connected as admin. A read-only user for reports or a BI tool looks like this:

-- read and write for the app
GRANT CONNECT ON DATABASE orders TO app_rw;
GRANT USAGE, CREATE ON SCHEMA public TO app_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
-- read only for reports
GRANT CONNECT ON DATABASE orders TO reports_ro;
GRANT USAGE ON SCHEMA public TO reports_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reports_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reports_ro;

• •MySQL: GRANT SELECT ON shop.* TO 'reports_ro'@'%';
• •Redis: users are ACL users; set rights with ACL SETUSER app_rw on ~session:* +@read +@write.

4. Reset or remove a password

Reset a password when someone leaves or a secret leaks. The old password stops working straight away, so update the app's environment variable and redeploy right after.

simplicloud db users reset-password orders-db app_rw
simplicloud apps env set orders-api DATABASE_URL="postgresql://app_rw:[email protected]:5432/orders?sslmode=require" --secret
simplicloud apps deploy orders-api
simplicloud db users delete orders-db reports_ro