Home > mailing lists

Thread: Restrict user to create only one db with a specific name

Restrict user to create only one db with a specific name

From

Tiffany Thang

Date:

07 March 2020, 02:28:08

Hi,

Is there a way in PostgreSQL 12 to restrict user to creating a database with a specific database name?

For example, userA can only create a database with a name called mydb. Any other names would generate an error.

If that is not possible, will it be possible then to limit userA to creating only one database? Granting the createdb privilege would allow the user to create any number of databases which I want to avoid.

Thanks.

Tiff

Re: Restrict user to create only one db with a specific name

From

"David G. Johnston"

Date:

07 March 2020, 02:39:14

On Fri, Mar 6, 2020 at 4:28 PM Tiffany Thang <tiffanythang@gmail.com> wrote:

Hi,
Is there a way in PostgreSQL 12 to restrict user to creating a database with a specific database name?

For example, userA can only create a database with a name called mydb. Any other names would generate an error.

If that is not possible, will it be possible then to limit userA to creating only one database? Granting the createdb privilege would allow the user to create any number of databases which I want to avoid.

No. Though you could prevent them from being able to connect to unexpected databases via the pg_hba.conf file.

Why does userA need create database privileges?

You could just have them log into an admin database and run a function that creates the database for them using function owner privileges then you can add whatever special logic you want to that function.

David J.

Re: Restrict user to create only one db with a specific name

From

"David G. Johnston"

Date:

07 March 2020, 09:00:51

On Friday, March 6, 2020, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Fri, Mar 6, 2020 at 4:28 PM Tiffany Thang <tiffanythang@gmail.com> wrote:
Hi,
Is there a way in PostgreSQL 12 to restrict user to creating a database with a specific database name?

You could just have them log into an admin database and run a function that creates the database for them using function owner privileges then you can add whatever special logic you want to that function.

Or not...the transaction involved with executing a function causes create database to not work...

David J.

Re: Restrict user to create only one db with a specific name

From

Paul Förster

Date:

07 March 2020, 10:35:22

Hi Tiff,

from what you say, it sounds that each user should have his or her own database.

Considering the other answers here already pointing out the difficulties, why don't you just create a database for each
userwith the same name as the username and grant him or her access to it. 

So, basically like this:

postgres=# create role "userA" login;
CREATE ROLE
postgres=# create database "userA" owner "userA";
CREATE DATABASE
postgres=# create role "userB" login;
CREATE ROLE
postgres=# create database "userB" owner "userB";
CREATE DATABASE

When, say, "userB" goes away, his or her data will go the way all things do:

drop database "userB";
drop role "userB";

Or did I misunderstand you?

Cheers,
Paul

> On 07. Mar, 2020, at 00:28, Tiffany Thang <tiffanythang@gmail.com> wrote:
>
> Hi,
> Is there a way in PostgreSQL 12 to restrict user to creating a database with a specific database name?
>
> For example, userA can only create a database with a name called mydb. Any other names would generate an error.
>
> If that is not possible, will it be possible then to limit userA to creating only one database? Granting the createdb
privilegewould allow the user to create any  number of databases which I want to avoid. 
>
> Thanks.
>
> Tiff

Re: Restrict user to create only one db with a specific name

From

Tiffany Thang

Date:

08 March 2020, 01:35:51

Thanks David and Paul. Because each user wants the ability to drop and recreate their own databases, I want to impose a restriction on the database names. I’ll just implement what Paul has suggested.

Thanks.

Tiff

On Sat, Mar 7, 2020 at 2:35 AM Paul Förster <paul.foerster@gmail.com> wrote:

Hi Tiff,

from what you say, it sounds that each user should have his or her own database.

Considering the other answers here already pointing out the difficulties, why don't you just create a database for each user with the same name as the username and grant him or her access to it.

So, basically like this:

postgres=# create role "userA" login;
CREATE ROLE
postgres=# create database "userA" owner "userA";
CREATE DATABASE
postgres=# create role "userB" login;
CREATE ROLE
postgres=# create database "userB" owner "userB";
CREATE DATABASE

When, say, "userB" goes away, his or her data will go the way all things do:

drop database "userB";
drop role "userB";

Or did I misunderstand you?

Cheers,
Paul

> On 07. Mar, 2020, at 00:28, Tiffany Thang <tiffanythang@gmail.com> wrote:
>
> Hi,
> Is there a way in PostgreSQL 12 to restrict user to creating a database with a specific database name?
>
> For example, userA can only create a database with a name called mydb. Any other names would generate an error.
>
> If that is not possible, will it be possible then to limit userA to creating only one database? Granting the createdb privilege would allow the user to create any number of databases which I want to avoid.
>
> Thanks.
>
> Tiff

Re: Restrict user to create only one db with a specific name

From

Paul Förster

Date:

08 March 2020, 11:37:35

Hi Tiff,

note that there is an absurd high maximum of databases possible inside a PostgreSQL database cluster. I think, the
maximumis 4,294,950,911 but I'm sure you don't get that many users or databases anyway. ;-) 

Cheers,
Paul

> On 07. Mar, 2020, at 23:35, Tiffany Thang <tiffanythang@gmail.com> wrote:
>
> Thanks David and Paul. Because each user wants the ability to drop and recreate their own databases, I want to impose
arestriction on the database names. I’ll just implement what Paul has suggested. 
>
> Thanks.
>
> Tiff
>
> On Sat, Mar 7, 2020 at 2:35 AM Paul Förster <paul.foerster@gmail.com> wrote:
> Hi Tiff,
>
> from what you say, it sounds that each user should have his or her own database.
>
> Considering the other answers here already pointing out the difficulties, why don't you just create a database for
eachuser with the same name as the username and grant him or her access to it. 
>
> So, basically like this:
>
> postgres=# create role "userA" login;
> CREATE ROLE
> postgres=# create database "userA" owner "userA";
> CREATE DATABASE
> postgres=# create role "userB" login;
> CREATE ROLE
> postgres=# create database "userB" owner "userB";
> CREATE DATABASE
>
> When, say, "userB" goes away, his or her data will go the way all things do:
>
> drop database "userB";
> drop role "userB";
>
> Or did I misunderstand you?
>
> Cheers,
> Paul
>
>
> > On 07. Mar, 2020, at 00:28, Tiffany Thang <tiffanythang@gmail.com> wrote:
> >
> > Hi,
> > Is there a way in PostgreSQL 12 to restrict user to creating a database with a specific database name?
> >
> > For example, userA can only create a database with a name called mydb. Any other names would generate an error.
> >
> > If that is not possible, will it be possible then to limit userA to creating only one database? Granting the
createdbprivilege would allow the user to create any  number of databases which I want to avoid. 
> >
> > Thanks.
> >
> > Tiff
>

Re: Restrict user to create only one db with a specific name

From

"Peter J. Holzer"

Date:

08 March 2020, 19:30:59

On 2020-03-06 16:39:14 -0700, David G. Johnston wrote:
> On Fri, Mar 6, 2020 at 4:28 PM Tiffany Thang <tiffanythang@gmail.com> wrote:
>     Is there a way in PostgreSQL 12 to restrict user to creating a database
>     with a specific database name?
[...]
> Why does userA need create database privileges?

Not speaking for the OP, but:

Some test frameworks (e.g. the one included in Django) like to create
their own test database to ensure that it only contains the test data.
So if you are using one of these as intended the user running the tests
needs to be able to create and drop databases.

If you are running a tests from multiple projects against the same
cluster, it might be a good idea to ensure that each job can only create
(and drop) their own test database and not those of other jobs (or -
worse - the production database).

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachment

signature.asc