Windows: how can I set my PostgreSQL user to the superuser?

Question

I am trying to create a database using PostgreSQL 9.4. I type "psql" in the command prompt, and then it asks for a password. I provide the password I set during the installation, but it says the authentication failed. After checking online, I concluded that I need to be using the superuser, named "postgres", which is the system user whose password is the one I set during the installation.

I am now trying to set PostgreSQL to this superuser. I spent a lot of time surfing the internet for a solution but wasn't able to solve the problem. I tried postgres ALTER USER myuser WITH SUPERUSER (I wrote that in the Windows command prompt), but it said that "alter" isn't recognized. Now, when I try to use PostgreSQL, my main problem is that I get the error: "role MYUSERNAME does not exist". (this is after I edited pg_hba.conf to make it not ask for a password)

Answer 1

By default, psql uses the name of the operating system to log in, to a database of the same name. If you want to log in as user postgres you should do:

psql -u postgres <any other options>

If a password is asked for, you give the password of the postgres user. You are now connected to the postgres database, where you really shouldn't be doing anything, except create new users (which are global to the installation) and other databases.

Once in the console, you can create new users like:

CREATE ROLE myusername LOGIN PASSWORD secret;

And new databases like:

CREATE DATABASE myowndb;
ALTER DATABASE myowndb OWNER TO myusername;

Then you log out from the console with \q.

In order to be able to access PostgreSQL using the new database, you have to edit the pg_hba.conf file (sample, modify to match your network settings):

host myowndb myusername 192.168.0.0/16 md5

Now you restart the PostgreSQL server from the Services tab in Administrative tools on the Control Panel.

Then you can log in to your new database:

psql -u myusername -d myowndb

Or use other clients like pgAdminIII.

Answer 2

• Under Windows. The Postgres bin directory ships with the user commands createuser.exe and dropuser.exe.

• Say, if running initdb (effective fresh install) or for some other reason there is no superuser (like the question).

• Can also manage the users and superusers (-s option) with the above two commands. ie.

  Create the superuser called postgres:

  C:"Program Files"\PostgreSQL\15\bin\createuser.exe -s postgres

  Drop a user:

  C:"Program Files"\PostgreSQL\15\bin\dropuser.exe postgres