Granting a user account permission to create databases in PostgreSQL

All we need is an easy explanation of the problem, so here it is.

How do I give a user account in PostgreSQL the ability to create and drop databases? Is there a way to do this with GRANT?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

It’s done with ALTER USER username CREATEDB;

See ALTER USER in the doc.

To drop a database, either you’re superuser (which can be granted with ALTER USER too) or you must own the database.

Method 2

First, you have to login as postgres user:

$ sudo -u postgres psql postgres

# \password postgres

Enter new password:

After entering new password for postgres user (special kind of user on PostgreSQL), you are now logged in as postgres and you can grant permission to other users. Let’s say you have user named user1. To grant him ability to create and drop databases, you have to write (as postgres user):

ALTER USER user1 CREATEDB;

Hope this helps…

Method 3

Create user from the start with CREATEROLE and CREATEDB permissions

After you’ve logged in to the PG server with the command line client, with a user that has the appropriate rights to create users (like the postgres user, which by default on UNIXes can be impersonated easily by the system super user with $ sudo -u postgres psql postgres):

CREATE ROLE user_name PASSWORD 'tYPe_YoUr_PaSSwOrD' NOSUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;
  • NOSUPERUSER – the user being created does not have superuser rights (like the postgres user, recommended).
  • CREATEDB – the user being created can create databases which it will own.
  • CREATEROLE – the user being created can create roles (logins) for objects it owns or has specific access to (like databases it has created).
  • INHERIT – the user being created inherits some default options, optional.
  • LOGIN – the user being created has the right to log in.

The PG binaries (applications) can also be used. For example, the above SQL is equivalent to running:

sudo -u postgres createuser --createdb --createrole --pwprompt user_name

Give existing user rights to create DB

After you’ve logged in to the PG server with the command line client, with a user that has the appropriate rights to alter users (like the postgres user, which by default on UNIXes can be impersonated easily by the system super user with $ sudo -u postgres psql postgres):

ALTER USER user_name CREATEDB;

or, to give the same rights as the examples above:

ALTER USER user_name CREATEDB CREATEROLE LOGIN;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply