How to prevent asking for password when creating new database in PostgreSQL 10?

Question

I use the following batch file to create a new database in PostgreSQL 10.

echo off
cd "C:\Program Files (x86)\PostgreSQL\10\bin"
createdb -h localhost -p 5432 -U postgres myDB

But it asks to enter the password. How to prevent asking for the password? I tried the -w option explained in this link to not ask for password prompt. But this did not work. How to prevent asking for password?

I tried the pgpass file method but it did not work. But how does "manage pg_hba.conf" file method work? — Codename K, Jun 08 '18 at 21:20
The pgpass file solution does work. See also here and here — , Jun 08 '18 at 21:38

score 11 · Accepted Answer · answered Jun 08 '18 at 23:25

11

use the PGPASSWORD environment variable,

@SET PGPASSWORD=something_secret
psql -c "CREATE DATABASE mydb" -U postgres postgres

or just use a connection string

psql -c "CREATE DATABASE mydb" "user=postgres dbname=postgres password=something_secret"

answered Jun 08 '18 at 23:25

Jasen

3,563
1
13
17

2

I see that you have given two database names, mydb and postgres (dbname=postgres). Does this mean the database mydb comes under postgres? – Codename K Jun 09 '18 at 16:34
3

@CodenameK: to create a database (mydb) you must first be connected to another database (postgres in the above example). It does not imply a hierarchy of databases. – Daniel Vérité Jun 09 '18 at 18:20
Here we can read about PGPASSWORD: "Use of this environment variable is not recommended for security reasons, as some operating systems allow non-root users to see process environment variables via ps; instead consider using a password file". – Boolean_Type Aug 02 '23 at 22:13
true: service or password files are a more secure solution - https://www.postgresql.org/docs/15/libpq-pgpass.html and https://www.postgresql.org/docs/15/libpq-pgservice.html – Jasen Aug 03 '23 at 02:29

score 1 · Answer 2 · answered Jun 09 '18 at 09:16

1

You may enter this line in the pg_hba.conf file like this:

# TYPE DATABASE USER ADDRESS METHOD local all postgres peer

This is for running commands from the machine. This passwordless login from local computers only If you are on Windows any user can put a switch -U postgres and connect to the machine as superuser. Do that only for development purposes.

answered Jun 09 '18 at 09:16

Mladen Uzelac

829
5
8

2

peer is documented as not supported on Windows as of PG10. See https://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-PEER – Daniel Vérité Jun 09 '18 at 18:17
1

I need to check my windows instalation hiw it works. I am running linux lately. – Mladen Uzelac Jun 09 '18 at 21:17
2

peer requires local, which implies unix domain sockets a feature not available on windows SFAIK – Jasen Jun 10 '18 at 11:33

score 1 · Answer 3 · answered Jun 10 '18 at 16:20

Your problem is that you are using TCP connection and not socket connection.

I just tried on Windows 7 this command and work normally: createdb -U postgres test I hope that you notice that there is no -h localhost Since -p 5432 is default you can omit that also. I checked that pg_hba.conf doesn't have the configuration using peer but it works anyway.

How to prevent asking for password when creating new database in PostgreSQL 10?

3 Answers3