PostgreSql in win32: postgres password

Question

I have a user account mayank. I did an initdb to create a new cluster.
I have PGDATA=C:\pgdata. Now I want to create a new db.

When I do a createdb db_test it asks for a password. What's the password required there.

I have the following entry in pg_hba.conf

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

I installed PostgreSql using windows installer (double click and install :-) ). It asked for a password that is not working here :(. What should I be missing?

Answer 1

Using One-Click Installer two different things are created:

• Windows account and "service account" (that one owns postgresql.exe process)
• PostgreSQL (database) superuser role

Default name for both accounts is (traditionally) postgres. After installation you can check newly created Windows account using net user command:

net user            postgres
User name           postgres
Full name           postgres
Comment             PostgreSQL service account
...
Active              Yes
Password required   Yes
...

You can use psql to connect with one of initial databases with superuser account and check cluster-defined all roles in default cluster:

postgres=# \du
                       List of roles
 Role name |            Attributes             | Member of
-----------+-----------------------------------+-----------
 postgres  | Superuser, Create role, Create DB | {}

If you want create new database cluster as superuser mayank (and cluster owner mayank), then you can use (assuming that you have PostgreSQL\version\bin in PATH already and you're logged as mayank):

cmd>initdb -U mayank -D C:\pgdata
cmd>pg_ctl -D C:\pgdata -o "-p 5440" -l C:\pgdata\log start
cmd>createdb -p 5440 test

It works for me just well without prompting for password. I just checked C:\pgdata\pg_hba.conf and indeed it contains:

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

That's well, but not safe in multiuser environment. You can use pg_ctl register to running postmaster as Windows Service (just like default 5432 DB cluster), so you can have mayank as "service account" too.