Re: Restrict allowed database names?

On Mar 20, 2010, at 2:24 PM, Adam Seering wrote:

> Hi,
> I'm trying to set up an internal general-purpose PostgreSQL server installation. I want most users with login access to the server to be able to create databases, but only with names that follow a specified naming convention (in particular, approximately "is prefixed with the owner's username"). A subset of administrative users can create users with any name. The goal is to let users create arbitrary databases, but to force them to get approval for names that someone else (or some other service) might conceivably want.
>
> Is there any way to enforce this within PostgreSQL? Maybe something like a trigger on CREATE DATABASE, if that's possible?

I don't think so.

There are several other ways you could do it, though.

Put a wrapper script around createdb that "refuses" to create a database named outside of your naming strategy and trust your users not to work around it.

The same, but add a cron job that'll drop any badly named database every hour or so.

Don't grant any normal database users createdb privs at all, instead requiring them to use an external tool to create databases. Have that tool - whether it be a cgi script or something suid, or some other hack - use a privileged user to create the database.

Cheers,
Steve