Restrict allowed database names?

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 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

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 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?

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