It seems that there are two distinct questions here.
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.
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.
See this part of the pg_dump
manpage:
-W, --password
Force pg_dump to prompt for a password before connecting to a database.
This option is never essential, since pg_dump will automatically prompt
for a password if the server demands password authentication.
Don't use -W
at all. In your case, it's just confusing.
Also, you need to know that the fact that the server asks for a password or not is not driven by the existence of this password.
It's driven by the server-side pg_hba.conf
file that you need to study and possibly modify according to your needs (don't forget to reload the server after modifying it).
EDIT: reviewing your pg_hba.conf. The relevant lines are:
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
local all all peer
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
The 1st line concerns the postgres
user. It's irrelevant for your pg_dump command since you're using the santa
user with -U santa
The 2nd line concerns any other connection through Unix domain sockets (TYPE column is local
). From the client, it means when you do not use -h localhost
. It says that if the OS user is the same name than the db user, he doesn't need a password.
The 3rd line says that if -h localhost
is used (IPv4 TCP connection), a password will always be asked to the client. The 4th line says the same with IPv6.
Based on this, this command run by the santa
OS user should not ask or need a password:
pg_dump --no-owner myapp_db > myapp_db_backup.sql
-U santa
is optional because the db username is taken as the OS user by default.
Best Answer
This works for me if the user I am logged in as has either the superuser attribute, or the createrole and/or createdb attribute.
When you create a server (probably a poor wording, you are't initializing a server, but connecting to an existing one), pgAdmin4 definitely asks you for more information than just the master password. Like what user you want to connect as.
If you want to connect as different users at different times, you need to "create server..." for each one.