Clever Geek Handbook

Createdb: Failed to create database: ERROR: Create database permission allowed

I'm pretty much confused about what root , super user , user and permissions are ! I cannot create a database inside the "athleticu" user. 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, someone will clarify my doubts and tell me what to write?

+8


source to share


4 answers


Hey, I already solved that. What you need to do is first log in as the postgres user like this:

$ su postgres

$ psql

postgres=# alter user athleticu createdb;
ALTER ROLE


Hope this helps you :)

+17


source


Enter \du

in psql and you will see a list of all registered users and what privileges they have. To grant privileges to the user who is logged in (for example "user1"), I had to log out and log in using one of the superuser roles in this list (for example, "user2") using the following command:

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

where 'database2' is the name of the one to which the user superuser 'user2' has the privilege. Once you are logged in as superuser, you can grant "user1" privileges:

    ALTER ROLE user1 WITH CREATEDB


or

    ALTER ROLE user1 WITH SUPERUSER

Then login again as user1 who is now the superuser.

This blog was helpful as well as this link .

+14


source


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

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

System accounts and accounts Postgres - are two different things, though, if you do not specify a user's Postgres name when connecting to the database (through a utility such as psql

, createdb

, dropdb

or otherwise), it will use the current system user name in the hope that there is a corresponding account Postgres entry with the same name. the root user does not have a corresponding Postgres account by default.

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

system user postgres

.

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

  • You can sudo su - postgres

    become a postgres

    system user and execute your command ( createdb

    , psql

    etc.). Since the system user has the same name as the database superuser, your team will connect as the appropriate account.
  • You can specify the username to execute like with a radio button -U

    , for example psql -U postgres ...

    .

Depending on your Postgres server authentication settings, you may need to enter a password using one or both of the connection methods.

+4


source


What you can do when you have a fresh PostgreSQL installation, create your user with some privileges (see createuser documentation ):

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

This will allow my-user

you to create databases like this:

my-user> createdb <my-db>

If you want to my-user

be able to do something, use the flag instead --superuser

:

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


source






More articles:


All Articles