One database per service: roles, privileges and the read-only user who sees nothing

One managed instance for all services, one database per service, one role per database. That’s the model that reconciles cost and isolation: the instance is shared, the data isn’t. It works well as long as you know three properties of PostgreSQL that everyone learns the hard way once.

Property 1: privileges apply to tables that already exist

GRANT SELECT ON ALL TABLES IN SCHEMA public TO bi_reader sounds complete. But it only applies to the tables that exist at the time of the GRANT. The table the application creates in its next migration belongs to the owner, and bi_reader can’t see it. The error surfaces weeks later as "table does not exist" in the BI tool, and nobody connects it to that GRANT from back then.

The fix is ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT ON TABLES TO bi_reader. With it, the read-only user automatically gets privileges on everything the owner creates in the future. The FOR ROLE app_owner is what matters: default privileges apply per creating role. Without that clause they apply to the role executing the statement, and that’s rarely the application.

Property 2: privileges add up, they don’t replace each other

If you downgrade a user from readwrite to read by running GRANT SELECT, you haven’t downgraded anything. The old INSERT and UPDATE privileges remain. Privileges in PostgreSQL are a set, and GRANT adds to it. A privilege change must therefore always run REVOKE ALL first and then grant the desired level. Revoke before grant, every time, even when it seems redundant on first creation.

Property 3: default privileges live in the database

Roles are global on a PostgreSQL instance; default privileges and table privileges live in the respective database. To set a read-only user’s privileges, you have to be connected to the right database, not to the admin database. A script that does everything over a single connection to the instance sets the privileges in the wrong database and reports success.

Which privilege levels are enough?

In practice, a service rarely needs more than three user types besides the owner:

What’s deliberately missing: a level for instance admin. When you create a user, the Scaleway console offers an "admin privileges" switch. Set it for an application user and you have a user whose password is readable in environment variables, in a Secret and in the cockpit, and who may create new databases and users on the instance. According to Scaleway, the switch does not override the permissions set for the individual databases – but an application user doesn’t need these powers in the first place. A leaked application password must never reach further than its own database.

Names and passwords are not parameters

An unpleasant property of SQL: CREATE ROLE ... PASSWORD takes a literal, not a bind parameter. If you assemble role names and passwords from input, you have to validate and escape both yourself. For names, a strict rule helps, such as lowercase letters, digits and underscores. For passwords, a rule that excludes the backslash, because PostgreSQL reads it as an escape or not depending on standard_conforming_strings. Scaleway also limits database and user names to 32 characters; a name derived from a long domain has to be shortened first.

Adopt instead of recreating

Existing databases shouldn’t be recreated but adopted. The test of whether name, role and password match isn’t a look into pg_database, but a connection as that role to that database. Only that proves all three at once. And an adopted database must never be deleted automatically, because it contains data older than the automation. It is only ever released again.

The statements, in the right order

For a read-only user on a database owned by the role app_owner, the complete sequence looks like this, executed over a connection to the database itself:

1. CREATE ROLE bi_reader LOGIN PASSWORD '...', once, at instance level.
2. REVOKE ALL ON ALL TABLES IN SCHEMA public FROM bi_reader, so a previous state doesn’t live on.
3. GRANT CONNECT ON DATABASE app TO bi_reader and GRANT USAGE ON SCHEMA public TO bi_reader, without which the user can’t even see the list of tables.
4. GRANT SELECT ON ALL TABLES IN SCHEMA public TO bi_reader for the current state.
5. ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT ON TABLES TO bi_reader for everything in the future.

Step three is the one most often forgotten. The user can connect but sees no tables, and the error message names the schema, not the missing privilege. For write users, sequences need a grant of their own, otherwise the first INSERT with a serial column fails.

What is different in MySQL?

In MySQL, privileges on datenbank.* apply to all tables, including future ones. There are no default privileges because it doesn’t need them. On the other hand, users are bound to a host, and a user allowed to connect from anywhere is called 'name'@'%'. If you manage both engines with the same tool, you need two sets of statements behind the same interface. The three levels stay the same, their implementation doesn’t.

In what order do you delete?

A database with additional users can’t simply be dropped while those users hold privileges in it. First remove the additional users, then the database, then the owner role. And before that, the dump. If you bake this into a tool, hard-code the order, because the third time it’s done by hand, someone forgets it.

How Clusterward encapsulates this

On the service page, one click creates database, role and password in a single step. Additional users get one of the three levels; the statements behind them revoke before granting and set default privileges for the owner, in the right database. There is no instance admin. Adopted databases are verified by logging in and never dropped. Details under Managed databases.

Conclusion

One database per service is the right model. It needs three things you can’t see: default privileges for future tables, revoke before grant, and no instance admin for application users. Bake those three into a tool once and you never have to explain them again.

Planning databases for your services? Tell us how many services run on how many databases. We’ll show you what roles and privileges look like per service. Ask about databases →

Sources and further reading

• PostgreSQL documentation: ALTER DEFAULT PRIVILEGES
• PostgreSQL documentation: Privileges
• Scaleway: How to manage database users
• MySQL Reference Manual: GRANT statement