Postgresql – Cannot create PostgreSQL user

At the SQL level, every user can indeed connect to a newly created database, until the following SQL command is issued:

REVOKE connect ON DATABASE database_name FROM PUBLIC;

Once done, each user or role that should be able to connect has to be granted explicitly the connect privilege:

GRANT connect ON DATABASE database_name TO rolename;

Edit: In a multi-tenant scenario, more than just the connect privilege would be removed. For multi-tenancy tips and best practices, you may want to read on the postgresql public wiki: Shared Database Hosting and Managing rights in PostgreSQL.

It seems that there are two distinct questions here.

1. Why don't the createuser instructions apply?

   It's because they apply to PostgreSQL 9.1 or older, and not the newest versions. createuser now has a new flag --interactive. Per documentation:

   --interactive Prompt for the user name if none is specified on the command line, and also prompt for whichever of the options -d/-D, -r/-R, -s/-S is not specified on the command line. (This was the default behavior up to PostgreSQL 9.1.)

   So by adding this flag you'll get back to the behavior described by the tutorial.

2. Once the user is created, why does createdb fails with a password authentication problem?

   It's because you're not using your newly created user to create the database, although it's what would seem to make sense in this context.

   Generally with all PostgreSQL command line tools, when a user is not specified on the command line, the OS user is assumed instead (but it's only useful when there happens to be a database user named after the OS user, otherwise that may unfortunately be a source of confusion).

Anyway, instead of createdb newdb_name, try:

createdb -U newusername newdb_name

Then you should be asked for the password of newusername that you created in the step above.

Technically createdb has to connect to a database (template1 in this case) to be able to create another database, because this is done by a SQL statement and no SQL can be issued when you're not connected to a database in the first place.

Also by doing this newusername will be the database owner of newdb_name, that will give it full over control over it and is probably what your application needs.