createdb: database creation failed: ERROR: permission denied to create database

Question

I'm pretty much confused about root user,super user,user and permissions! I am not able to create a database inside user "athleticu". Following are the commands I used:-

athleticu@ip-172-30-4-103:/home/ubuntu$ createdb -T template0 simple_db1
createdb: database creation failed: ERROR:  permission denied to create database
athleticu@ip-172-30-4-103:/home/ubuntu$ sudo createdb -T template0 simple_db1
sudo: unable to resolve host ip-172-30-4-103
createdb: could not connect to database template1: FATAL:  role "root" does not exist

Please somebody clarify my doubts and tell me what should I write!

Stroganoff

Are you sure that's the right Address for the server: "unable to resolve host up-172-30-4-103"? – Adlay

You need to connect using the postgres super user. Typically that is postgres. Try createdb -U postgres .... – Colene

Actually I am connecting to an instance of AWS machine using:- sudo ssh -i "athleticu-test-keypair.pem" [email protected] and then it's get like this : ubuntu@ip-172-30-4-103:~$ @Adlay – Stroganoff

Do you mean I should first login into postgres user and then I should login into athleticu? @a_horse_with_no_name – Stroganoff

Try this Solution worked for me – Spermatocyte

Answer 1

Hey I have already solved this. What you have to do is to first login as postgres user as follows:

$ su postgres

$ psql

postgres=# alter user athleticu createdb;
ALTER ROLE

Hope it helps you :)

Stroganoff

Couldn't get into postgres without a password, but elevating to root allowed me to: sudo su postgres – Timepiece

Note that as of postgresql 8.1, "ALTER USER is now an alias for ALTER ROLE". – Baritone

@Timepiece your comment was very helpful, I didn't know about sudo su – Carpospore

Answer 2

Type \du in psql and you will see a list of all the registered users and what type of privileges each one has. In order to grant privileges to the user which is logged in (eg 'user1'), I had to sign out and log in using one of the superuser roles in that list (eg. 'user2'), using the following command:

    psql -U 'user2' -h localhost 'database2'

where 'database2' is the name of the one that specific superuser 'user2' has privileges to. Once you are logged in as a superuser, you can grant privileges to 'user1' by:

    ALTER ROLE user1 WITH CREATEDB 

or

    ALTER ROLE user1 WITH SUPERUSER

Then sign in again as user1, who is now a superuser.

This blog was helpful as well as this link.

Fenn

Works well. In my case (version 10.17) I had to write ALTER ROLE "user1" WITH CREATEDB; — note quotation marks and semicolon. – Fallacious

Answer 3

Currently, this worked for me:

sudo su postgres
psql

ALTER USER username WITH CREATEDB;
\q

exit

Sources: alter role, alter user

Petigny

Answer 4

The root user is an account on the system independent from Postgres. There is only one root user.

A superuser is an account in Postgres with access to everything. There may be many superusers.

System accounts and Postgres accounts are different things, although unless you specify a Postgres username when you connect to the database (through utilities like psql, createdb, dropdb, or otherwise), it will use the current system user's name in hopes that there is a corresponding Postgres account with the same name. The root user does not, by default, have a corresponding account in Postgres.

When you install Postgres on *nix, it creates both a superuser named postgres and a system user named postgres.

Therefore, when you need to do something with Postgres as the built-in superuser, you have two options:

1. You may sudo su - postgres to become the postgres system user and execute your command (createdb, psql, etc). Because the system user has the same name as the database superuser, your command will connect as the appropriate account.
2. You may specify the username to execute as with the -U switch, eg psql -U postgres ....

Depending on your Postgres server's authentication settings, you may be required to enter a password with either or both connection methods.

Elonore

hey thanks a lot! When you install Postgres on *nix - what is meant by *nix in your comment? does it mean Unix/Linux ? and about using postgres on ubuntu? Please clarify a little more! @Elonore – Stroganoff

*nix refers to any operating system derived from or related to Unix, including the various Linux distributions (of which Ubuntu is one), the BSDs, and Mac OS X. – Elonore

Answer 5

What you can do when you have fresh installation of PostgreSQL is create your user with some rights (see createuser documentation):

my-user> sudo su - postgres -c "createuser <my-user> --createdb"

This will allow my-user to create DBs just like so:

my-user> createdb <my-db>

If you want the my-user to be able to do anything just use the --superuser flag instead:

my-user> sudo su - postgres -c "createuser <my-user> --superuser"

Centripetal

Answer 6

I got the same error and I found out that the reason was that I was trying to create a database outside of psql as a user which did not exist for postgresql. I found out about it and solved it by taking the following steps:

1. In my terminal I logged in as postgres user (the root user by default for postgresql) by typing sudo -u postgres psql
2. While inside the psql I typed \du to see all users and their privileges. I found out that I had only one user (the postgres one) and I had to create another superuser which had the same username as my Linux user (george)
3. I typed (still inside psql) CREATE USER george SUPERUSER; and this way I created a new super user called george.
4. I exited psql (by typing \q) and I was now able from outside psql, meaning from my terminal, to run created db <database name> with no issues at all.

Mahone

Answer 7

Error ? You are trying to perform database actions( Creating Database, creating Roles) using a user that doesn't have the permission for those types of actions you are trying to perform.

solution ? Simply login to your database on the command line, i.e for PostgreSQL one will use "sudo -u postgres psql", then confirm that users specific assigned roles using the command "\du", most probably he/she doesn't have the necessary permissions to perform the actions you wanted. Then simply assign the roles you want the user to perform ,i.e create Database or simply make user "Superuser" by following along(https://chartio.com/resources/tutorials/how-to-change-a-user-to-superuser-in-postgresql/)

Wilser

Answer 8

For example, you can create the user(role) john with CREATEDB privilege or add CREATEROLE privilege to the user(role) john as shown below. *You probably need to log in with any superusers(e.g., postgres) and you can omit WITH which is optional:

CREATE ROLE john WITH LOGIN PASSWORD 'apple' CREATEDB;

Or:

ALTER ROLE john WITH CREATEDB;

Then, you can create and drop orange database by logging in with john as shown below. *Be careful, there is the restriction to add some privileges:

CREATE DATABASE orange;

DROP DATABASE orange;

Breach