Multiple databases with Digital Ocean Managed Databases Service
By DevOps on Fri 24 May 2019in
If you are creating one db cluster per app, you can use the
doadmin user. It's cheaper, however, to run multiple
applications on the same db cluster or dev/staging/prod versions of the same
app. We can create databases and users via the UI, but by default all users
have full rights to all databases.
A better solution is to create users with restricted permissions. To do that we need to set permissions via SQL.
First, install the Postgres client library on your Droplet (assuming Ubuntu 18.04):
sudo apt-get install postgresql-client postgresql-client-common
Log into the database shell as the
psql -U doadmin -h <host> -p 25060 -d defaultdb
Create the production database and make it private by default:
create database app_prod; revoke all on database app_prod from public;
On your dev machine, generate a strong password for the db user using e.g. pwgen:
pwgen -s 16
Create the prod db user, putting in the user password:
create user app_prod with encrypted password 'CHANGEME';
Give the app user rights to manage the prod db:
grant all privileges on database app_prod to app_prod;
Exit the shell with
\q. Confirm that the db user can connect:
psql -U app_prod -h <host> -p 25060 -d app_prod
Follow the same procedure to create a test database and user.
create database app_test; revoke all on database app_test from public; create user app_test with encrypted password 'CHANGEME2'; grant all on database app_test to app_test;
These db users have full rights on their respective databases, e.g. they can create tables. They can't, however, create databases. This is generally what is expected by web frameworks that run database migrations on startup.
Applications with higher security requirements can separate the user account which manages the database from the user which accesses the database at runtime. If an attacker manages to compromise your application, then they are limited in what they can do. Other examples are creating read-only views which hide sensitive information.
See https://dba.stackexchange.com/questions/117109/how-to-manage-default-privileges-for-users-on-a-database-vs-schema for more details.