CODEWITHSUNDEEP
postgresql
Mayank
Mayank

Reputation: 5728

PostgreSql in win32: postgres password

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?

Upvotes: 3

Views: 1751

Answers (2)

Grzegorz Szpetkowski
Grzegorz Szpetkowski

Reputation: 37924

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.

Upvotes: 3

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95562

IIRC, the Windows installer creates two "users". One of them is the PostgreSQL service account, which usually has a random string for a password. The other is the user "postgres", which appears in the "User Accounts" applet of the Windows Control Panel.

The two most likely possibilities . . .

  1. You're not quite getting the right password for the Windows user "postgres".

  2. You're actually trying to create the database using the default login for PostgreSQL, which is your Windows network username.

The psql utility has a -U option that lets you specify the username you want.

Upvotes: 1

Related Questions