By default, PostgreSQL only has one user account, "postgres", which is a database superuser.
Database users with the same name as OS users automatically authenticate from the OS, this is called peer authentication. The other main authentication is by password, which may be by md5 or better by scram-sha-256.
By default, Ubuntu uses peer authentication for local Unix domain sockets and password authentication for local IP connections. So if you want to use a password, just add -U <username> -h localhost
as an option to psql
, createdb
, etc.
A "postgres" OS account may need to be created with sudo useradd postgres
.
sudo -u postgres createuser -ld $USER
A password won't be needed for connections from this account, and you don't need to specify -h localhost
.
sudo -u postgres createuser -Pld <username>
creates a new database user that can login, create databases, and can do password authentication.
createdb -U <username> -h localhost <dbname>
creates a new database using the given user, owned by that (non-OS user) user. If you are an OS user with a matching postgres account name, you don't need the -U and -h options. You can also use the "postgres" superuser account like:
sudo -u postgres createdb -O <username> <dbname>