Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Why not give every user only one database and let them use schemas to separate the name spaces? –  a_horse_with_no_name Apr 16 '13 at 7:30
    
@a_horse_with_no_name Yes, that would be an option, but since our users work on different projects and they need to backup/restore their respective databases separately, it could make things a bit complicated for them. –  Petr Pudlák Apr 16 '13 at 7:38
    
You can't really do this inside PostgreSQL. However, I imagine you could set up a script (with the appropriate ownership and permissions) which can create new DBs, checking the prefix first. It should then set the ownership of the DB as well. –  dezso Apr 16 '13 at 8:27

1 Answer 1

up vote 2 down vote accepted

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:

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

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

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

share|improve this answer
    
+1 as this is the answer I was lazy to write :) On the other hand, I see the CREATE/DROP DATABASE exception a quite logical choice. Encasing it in a transaction could raise quite a few interesting effects. –  dezso Apr 25 '13 at 5:46
    
I am not sure how the side effects would be much more unmanageable than with drop table or drop user both of which can be rolled back. I don't know why one couldn't wait until commit to do essentially an rm -rf and block connections in the mean time. –  Chris Travers Apr 25 '13 at 6:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.