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?
-
Why not give every user only one database and let them use schemas to separate the name spaces?– user1822Apr 16, 2013 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.– PetrApr 16, 2013 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.– dezsoApr 16, 2013 at 8:27
Add a comment
|
1 Answer
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.
-
+1 as this is the answer I was lazy to write :) On the other hand, I see the
CREATE/DROP DATABASEexception a quite logical choice. Encasing it in a transaction could raise quite a few interesting effects.– dezsoApr 25, 2013 at 5:46 -
I am not sure how the side effects would be much more unmanageable than with
drop tableordrop userboth 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. Apr 25, 2013 at 6:19