Techcrunch – Installing Postgres on EC2 Ubuntu Server

Most of this section if cribbed directly from an article at Digital Ocean

sudo apt-get install postgresql
sudo apt-get install postgresql-contrib
sudo apt-get install phppgadmin

Change Apache access for phppgadmin we just installed

sudo vim /etc/apache2/conf.d/phppgadmin

Change so it looks like ( remove # from 4th line )

order deny,allow
deny from all
allow from 127.0.0.0/255.0.0.0 ::1/128
allow from all

Restart Apache

sudo /etc/init.d/apache2 restart

If you now head over to http://localhost/phppgadmin you’ll see PHPAdmin for Postgres

Now we add some more security

sudo vim /etc/apache2/sites-enabled/000-default

Add the following to Virtual Host at the bottom

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

Save and exit, n ow lets create a user id and password for access

sudo htpasswd -c /etc/phppgadmin/.htpasswd username

Enter userid and password, then restart Apache

sudo /etc/init.d/apache2 restart

Next we set the Postgres login credentials

sudo vim /etc/postgresql/9.1/main/pg_hba.conf

Look for the line that looks like

local all all peer

And change the peer to md5 so it looks like

local all all md5

Now we can su to postgres and create a database

sudo su - postgres

Then create a new database
createdb <newdbname>
And then create credentials for that database
createuser -P
Enter the name and password of the database user, making sure to set the user to be superuser

Now we can access the database via the Postgres command line

psql newdbname

You can now perform SQL against the databse. If you need to quit type \q

Head back over to PHPAdmin and we can now login with the credentials we created and access our new database

http://localhost/phppgadmin

Your email address will not be published. Required fields are marked *