CODEWITHSUNDEEP
databasepostgresqlrootrolespermission-denied
SonamGupta
SonamGupta

Reputation: 1945

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

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!

Upvotes: 68

Views: 129418

Answers (8)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

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;

Upvotes: 0

Ferroao
Ferroao

Reputation: 3033

Currently, this worked for me:

sudo su postgres
psql

ALTER USER username WITH CREATEDB;
\q

exit

Sources: alter role, alter user

Upvotes: 20

bitbuoy
bitbuoy

Reputation: 393

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/)

Upvotes: 0

George Linardis
George Linardis

Reputation: 130

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.

Upvotes: 0

SonamGupta
SonamGupta

Reputation: 1945

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 :)

Upvotes: 109

nyxz
nyxz

Reputation: 7428

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"

Upvotes: 3

polyccon
polyccon

Reputation: 731

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.

Upvotes: 49

dmfay
dmfay

Reputation: 2477

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.

Upvotes: 11

Related Questions