How to allow a user to create databases only with a certain prefix?

We're having a multi-user Linux server. I'd like to give each user the ability to create and manage databases at will, but to keep things sane, force them that each database name they create must start with their user name as prefix. For example, user joe would be only allowed to create databases like joe_app1 and joe_app2 but not app1 or jack_app1. Is this possible in PostgreSQL? Or is there another way how to separate name-spaces for user-created databases?

PostgreSQL does not allow you to do this inside PostgreSQL. You cannot create a database inside a transaction so procedural language functions cannot create databases or drop databases. I hope some day this is corrected, but it is the one piece of DDL that does not work inside a transaction.

What this means is you have to do it from outside the database with a separate application that runs whatever checks you want to run. Some key things to keep in mind are:

-
You probably want to have a dedicated user (role with login, noinherit) for the database creation process.

-
You do not want to give the permission to create dbs to the general users.

-
The script should create the database and then ALTER DATABASE ... OWNER TO ... in order to ensure that the user has full permissions to it.

StackExchange Database Administrators Q#40091, answer score: 3