Horton

Thread

  1. Restrict allowed database names?

    Adam Seering <aseering@mit.edu> — 2010-03-20T21:24:30Z

    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?

Thanks,
Adam

  2. Re: Restrict allowed database names?

    Scott Mead <scott.lists@enterprisedb.com> — 2010-03-21T01:01:27Z

    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

  3. Re: Restrict allowed database names?

    Steve Atkins <steve@blighty.com> — 2010-03-21T01:25:18Z

    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

  4. Re: Restrict allowed database names?

    Sergey Konoplev <gray.ru@gmail.com> — 2010-03-21T14:26:26Z

    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

  5. Re: Restrict allowed database names?

    Daniel Verite <daniel@manitou-mail.org> — 2010-03-21T17:43:16Z

    	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

  6. Re: Restrict allowed database names?

    Sergey Konoplev <gray.ru@gmail.com> — 2010-03-21T17:56:13Z

    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

  7. Re: Restrict allowed database names?

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-03-21T18:02:33Z

    "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

  8. Re: Restrict allowed database names?

    Adrian von Bidder <avbidder@fortytwo.ch> — 2010-03-22T07:24:11Z

    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