Advanced PostgreSQL user management

Question

For the users in our institute, we want to provide a quick and simple way to use PostgreSQL databases. Currently, we are already able to authenticate all users through LDAP (PAM). This works rather well. We have to do a create user once and it is possible for the user to log in to the database using the LDAP-backed password.

However, there are a couple of drawbacks to this simple design:

Users may want a couple of throwaway roles with throwaway passwords so that they don‘t need to store their LDAP password.

Users should be able to create their own databases freely. (And they should be able to grant rights on their databases to other users.)

So, in short, each LDAP-backed user with name username should be allowed to create other roles as long as they are prefixed with username_; and the same for databases. (Other suggestions are also welcome.) The LDAP-backed user would then be the superuser for the whole username_ database space.

I’ve seen installations doing something similar for MySQL (wildcard grants), but I wonder, if this is also possible for PostgreSQL.

Answer 1

It appears that you should be able to do this with Postgres functions, created and owned by a user that can both create databases and can create users; one function for creating databases and another for creating users. The key is to specify the SECURITY DEFINER clause in the function definition (See the documentation for details.). Have the first function create the database and set the ownership, etc. to the current_user. The second function, for creating users, can verify the user/role to be created against the current_user to ensure that it meets your naming standard.