How to Log in to phpPgAdmin Remotely on Ubuntu 14.04 Amazon EC2

postgresql

I created an Ubuntu server instance on Amazon EC2 and installed php, postgreSQL, phppgadmin, and any other required features.

After SSH'ing into the server using command prompt, I executed the following operations:

'sudo apt-get update'
'sudo apt-get install apache2 libapache2-mod-php5 php5'
'sudo apt-get install postgresql postgresql-contrib'
'sudo apt-get update'
'sudo apt-get install phppgadmin'

'sudo nano /etc/apache2/conf.d/phppgadmin'
- uncomment line 'allow from all', save and exit

'sudo nano /etc/apache2/sites-enabled/000-default'
add the following text:

<Directory "/usr/share/phppgadmin">
        AuthUserFile /etc/phppgadmin/.htpasswd
        AuthName "Restricted Area"
        AuthType Basic
        require valid-user
</Directory>

- save and exit

'sudo apt-get install apache2-utils'

'sudo service apache2 restart'

'sudo cp /etc/apache2/conf.d/phppgadmin /etc/apache2/conf-enabled/phppgadmin.conf'
'sudo service apache2 restart

The result:
http://ec2-54-174-161-124.compute-1.amazonaws.com/phppgadmin/

My issue now is that I don't know what to do so that I can access phppgadmin and start creating a database. A tutorial explained how to setup a password to access the page that was linked to above, but this did not work because as you can see, the page is accessible without a password.

My questions:

1) It is an issue (in terms of security) that there is no password to access the page that I linked to? If so, how can I fix this?

2) How can I gain access to phppgadmin after I click on the 'PostgreSQL' link in the sidebar that shows a container with a red X on it? I have searched and searched and I can't figure out how to setup a user or access it as an administrator/root/postgres user

Best Answer

So I received help on this issue from a colleague who is an experienced programmer.

In order to add a password to the page:

sudo nano /etc/apache2/conf-enabled/phppgadmin.conf

Then, modify the .conf file to include the following text within the block (I placed it underneath the 'allow from all' line that I uncommented previously):

AuthUserFile /usr/share/phppgadmin/.htpasswd
AuthName "Restricted Area"
AuthType Basic
require valid-user

Save this file and exit.

Change directory to phppgadmin: 

cd /usr/share/phppgadmin

Create password file (change 'your username' and get rid of triangle brackets):

sudo htpasswd -c .htpasswd <your username>

Enter password twice when prompted.

Restart the apache service and the page will now be password protected.

In order to add a superuser to access phppgadmin(again, (change 'your username' and get rid of triangle brackets):

sudo su - postgres
createuser -P -s <your username>

Again, enter password twice when prompted. This will now allow you to login to to phppgadmin.

Erwin's answer is also helpful too, it just seems as if he takes a different approach to solving the issue. Although I did not use Erwin's method, it should accomplish the same thing. However, I did not need to adjust listen_address in postgresql.conf in order to make it work.

Related Question