sql - Problems with createdb in postgres

Question

I have to run a simulation with several postgresql databases spread on different machines which all of them are running linux.

I successfully compiled and built postgresql from the source code and I can also run the server, but when I try to create a new db with this command:

./postgresql/bin/createdb db1

I get this error:

createdb: could not connect to database postgres: FATAL:  role "giulio" does not exist

where giulio is my username to access all the machines.

On some machine it works while on other it does not. I really cannot figure out the root of the problem. I suppose it is something related with the access control of postgres.

I did several research on google but I was not able to found and to solve the problem.

Does anyone know how to get this work?

Thanks,

-Giulio

Answer 1

PostgreSQL has its own users and roles that are separate from that of your OS. Generally there is a dedicated super user, postgres. For user management info, look here:

http://www.postgresql.org/docs/9.1/interactive/user-manag.html

When executing postgres commands, you need to specify the user with the -U flag (unless you are already logged in as an existing db user). When you called the createdb script, because you didn't use the -U flag, the server assumed that the uid of the caller (giulo) should be used, but you didn't add a user "giulio" to the db, and hence the error message.

So execute the command as

./postgresql/bin/createdb -U postgres db1

and it should work. Then, later on, you may want to create other users and roles in your db rather than doing everything as the superuser.