I’m trying to create a PostgreSQL database from the command line on a CentOS system (Postgres version 10.9). I’m running the following command:
sudo -u postgres psql createdb test
But it prompts me with:
Password for user test:
I expected the command to run without asking for a password, especially since I’m using the postgres system user. Am I missing something in the authentication setup or command structure?
What’s the correct way to use createdb for this kind of setup, and how can I avoid the password prompt when doing a PostgreSQL create database operation via the command line?
I get where you’re coming from! I ran into the same issue when I was first setting things up. So, when you try to run psql createdb test, you’re actually telling psql to execute createdb as an SQL command. This ends up prompting for the ‘test’ user’s password because psql is treating ‘test’ as the login you’re trying to access.
What you want to do instead is simply run createdb directly, like this:
sudo -u postgres createdb test
This way, you’re calling the createdb utility directly as the postgres system user, which bypasses the password prompt entirely. PostgreSQL is configured to allow system users like postgres to run this command locally without needing a password, thanks to the peer authentication method in pg_hba.conf. Give it a try, and it should work without a hitch!
Exactly, @netra.agarwal ! So adding onto that, what’s happening in your original command is that psql is assuming you’re trying to run a SQL command to create the database instead of using the createdb utility directly. It treats the ‘test’ user as the target login, which triggers the password prompt.
To fix it, you can either invoke createdb directly as Netra pointed out, or you could pass the command explicitly with psql like this:
sudo -u postgres psql -c "CREATE DATABASE test;"
This is the PostgreSQL equivalent of saying, ‘Hey, create a database as postgres, no password needed.’ Both approaches will keep you from being prompted for a password. It’s all about how you invoke the command!
Totally on point, @raimavaswani ! And one more thing to consider, if your pg_hba.conf file is set to use md5 or password authentication for local connections instead of peer, even running sudo -u postgres might still ask for a password unexpectedly.
Here’s how you can check:
Look for the relevant section in your pg_hba.conf file (usually found in /var/lib/pgsql/10/data/pg_hba.conf):
# TYPE DATABASE USER METHOD
local all postgres peer
If it’s set to md5 or password, switch it to peer for local users, which allows them to authenticate without a password. After making the change, reload PostgreSQL to apply it:
sudo systemctl reload postgresql
Once that’s set up, any postgres create database command run with sudo -u postgres will bypass the password prompt without issue. Hope this clears things up!