Re: Restrict allowed database names?

Re: Restrict allowed database names?

On Sunday 21 March 2010 02.01:27 Scott Mead wrote:
> On Sat, Mar 20, 2010 at 5: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?
> 
> 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

Re: Restrict allowed database names?

"Daniel Verite"  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

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

Re: Restrict allowed database names?

	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

Re: Restrict allowed database names?

On 21 March 2010 00:24, 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?
>

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

Re: Restrict allowed database names?

On 21 March 2010 20:43, Daniel Verite  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

Re: Restrict allowed database names?

Restrict allowed database names?

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