Thread: Restrict allowed database names?
Hi,
I'm trying to set up an internal general-purpose PostgreSQL server installation. I want most users with login
accessto 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
withany name. The goal is to let users create arbitrary databases, but to force them to get approval for names that
someoneelse (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?
Thanks,
Adam
On Sat, Mar 20, 2010 at 5:24 PM, Adam Seering <aseering@mit.edu> 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?
Hmmm... nothing like that I'm afraid...
But, you could possibly make a shell script to the 'createdb' executable that would force a name-style, but even then, for any user to be able to successfully run the command, they need database logon / create database privs, so if someone : cat `which createdb` and you had made a script, they'd see what you were up to. It may be a way to get started though.
--Scott M
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 accessto 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 withany name. The goal is to let users create arbitrary databases, but to force them to get approval for names that someoneelse (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 trustyour 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 createthe database. Cheers, Steve
On 21 March 2010 00:24, Adam Seering <aseering@mit.edu> wrote: > Hi, > I'm trying to set up an internal general-purpose PostgreSQL server installation. I want most users with login accessto 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 withany name. The goal is to let users create arbitrary databases, but to force them to get approval for names that someoneelse (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'spossible? > What about PL/pgSQL wrapper function for CREATE DATABASE with database name check and SECURITY DEFINER option. And of course you should not set CREATEDB option to regular users. -- Regards, Sergey Konoplev
Sergey Konoplev wrote: > What about PL/pgSQL wrapper function for CREATE DATABASE with database > name check and SECURITY DEFINER option. Not possible because CREATE DATABASE can't be executed within a function (nor within a transaction). Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
On 21 March 2010 20:43, Daniel Verite <daniel@manitou-mail.org> wrote: > Sergey Konoplev wrote: > >> What about PL/pgSQL wrapper function for CREATE DATABASE with database >> name check and SECURITY DEFINER option. > > Not possible because CREATE DATABASE can't be executed within a function (nor > within a transaction). Ah, exactly. Well than what if we use PL/Python or PL/Perl function where we do connect to this postgres server and do CREATE DATABASE? Looks very tricky but seems to be working. -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802
"Daniel Verite" <daniel@manitou-mail.org> writes:
> Sergey Konoplev wrote:
>> What about PL/pgSQL wrapper function for CREATE DATABASE with database
>> name check and SECURITY DEFINER option.
> Not possible because CREATE DATABASE can't be executed within a function (nor
> within a transaction).
Note that the reasons why that's true are equally good reasons to not
allow triggers or any other user-added operations for CREATE DATABASE.
regards, tom lane
On Sunday 21 March 2010 02.01:27 Scott Mead wrote: > On Sat, Mar 20, 2010 at 5:24 PM, Adam Seering <aseering@mit.edu> 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? > > Hmmm... nothing like that I'm afraid... > > But, you could possibly make a shell script to the 'createdb' > executable that would force a name-style, but even then, for any user to > be able to successfully run the command, they need database logon / > create database privs, so if someone : cat `which createdb` and you had > made a script, they'd see what you were up to. It may be a way to get > started though. Extending this: have your users not have createdb permission and write this script as a suid program. cheers -- vbi > > --Scott M -- this email is protected by a digital signature: http://fortytwo.ch/gpg