Distribution, Installation, and Connectivity of PostgreSQL

Question

Have not been able to find an answer to this yet, but if this a duplicate, please mark accordingly!

Trying to understand how PostgreSQL can be distributed to an end-user via silent installation and the respective actions if:

1. PostgreSQL is already installed on the computer system
2. How to connect to PostgreSQL as a superuser, if it is already installed
3. Create a database (relating to #2, since we would not know the credentials of postgresql user when PostgreSQL was initially installed)

For #1, from my research and understanding, there are two methods:

1. Determining if a postgresql-[version] service is installed (per this QA)
2. Determining if HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\ registry exists, and if it does, seeing the versions and service names under the \Installations\ and \Services\ subs

However, I am more concerned about the connectivity part. If my application (to be written in C#) is dependent on a database name "MyProgram," how would it be possible to create a database in PostgreSQL and with what credentials?

From my understanding, the way to go would be to attempt to log in as the default superuser, postgres, to the default database, postgres, and create a new user and database from that connection. But, the password for postgres user is set during by the user/program that is initially is installing PostgreSQL.

How to go about this? Any help is much appreciated!

Answer 1

IMPORTANT NOTE: This may not be the most ethical/proper way of doing this, but it appears to get the job done.

After numerous hours of digging, using the registry key on Windows is the best methodology, it seems, to determine if the version of PostgreSQL you intend to install, if it is already installed.

Assuming that I am running on the Windows system with Administrative rights, in theory, I should be able to change the login credential requirements of the pg_hba.conf file in the data directory (i.e. cluster) that already exists to allow myself to create the database(s) and user(s) I need to, before reverting the credential requirement settings to what they originally were.

So, the answer I have come to the conclusion with is:

1. Determine if PostgreSQL is already installed or not. Look at the HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installation\[version] registry, where [version] is formatted as postgresql-[32 or 64 bit]-[PostgreSQL version], e.g. postgresql-x64-12). If the registry exists, then data should exist for the Data Directory value... obtain that data, and that's where the cluster is located.

Step [2] and on are obviously for when PostgreSQL is already installed.

2. Make a copy of the pg_hba.conf file in the cluster directory provided by the Data Directory value from step [1].

This will be the file we restore to after we are done. Save file to a temporary directory, such as Desktop.

3. In the pg_hba.conf file in the cluster directory, change all connection types' methods to trust

Example:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            trust

4. Restart Postgres' Windows service with the following command: pg_ctl.exe restart -D <cluster directory>

NOTE: pg_ctl.exe is located under the \bin\ folder of Postgres' installation directory.

Example: C:\Program Files\PostgreSQL\12\bin\pg_ctl.exe restart -D "C:\Program Files\PostgreSQL\12\data\"

5. Connect to the cluster and issue the command to create a superuser role for your needs with the following command: psql -h 127.0.0.1 -p 5432 -d postgres -c "CREATE ROLE <role name> LOGIN SUPERUSER PASSWORD '<password>';

In the above command, I have the cluster running on the local computer (i.e. localhost, IP address 127.0.0.1) on port # 5432 (default), connecting to the default database postgres and issuing the command to create a role with whatever role name provided in place of <role name>, with SUPERUSER rights and the password provided in place of <password>.

Since one HAS to connect to a database, I am connecting to the default one postgres, otherwise template0 and template1 are default databases that could also be utilized.

6. Connect to the cluster and issue the command to create the needed database for your needs with the following command: psql -h 127.0.0.1 -p 5432 -d postgres -c "CREATE DATABASE <database name>;
7. Replace the pg_hba.conf file with the original
8. Restart Postgres' Windows service with the following command: pg_ctl.exe restart -D <cluster directory>