This is a step by step guide to get PostgreSQL + phpPgAdmin running with Nginx on an Ubuntu 14.04 server! There are plenty of references and similar tutorials online and this is just one of many ways to configure things.
1: Installation
First, lets install postgreSQL and phpPgAdmin.
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib phppgadmin php5-fpm
By default, Apache will likely be the webserver. Also phpPgAdmin uses Apache by default. But we want to configure it with Nginx, so lets install Nginx.
sudo apt-get install nginx
This should automatically start Nginx as the web server. You could also type this to start the service.
sudo service nginx start
2. Setup PostgreSQL & phpPgAdmin
PostgreSQL
First login as the postgres user
sudo su postgres
Lets create a new postgres role (user)
createuser -P --interactive
The --interactive
adds some initial permissions and the -P means assign a password. These could also be added in later. It should look something like this
postgres@your_machine_name:~$ createuser -P --interactive
Enter name of role to add: test
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
postgres@your_machine_name:~$
This creates a role called test that can create databases but is not a superuser. It's good practice to create different roles for different tasks and keep the superuser only as postgres. You can verify if the role exists by typing psql
and then typing \du
.
phpPgAdmin
The default installation of phpPgAdmin will automatically connect to postgreSQL server. Unless you have modified any configuration, you can leave this as is.
sudo mkdir /etc/uwsgi
sudo mkdir /etc/uwsgi/apps-available
sudo mkdir /etc/uwsgi/apps-enabled
3. Setup Nginx
First lets add a symbolic link to the directory where you intend to place all your web files in.
sudo ln -s /usr/share/webapps/phppgadmin /path/to/your/www
There are a couple of ways to configure the URL for phpPgAdmin. You could set it up as a location within your main domain like example.com/phppgadmin
. You could also create a subdomain that will look like phppgadmin.example.com
. We will do the latter.
First create an A record called phppgadmin
.This could potentially be anything you choose. Set the IP address for this A record to that of your machine (Same as the IP address for your main domain).
Next, lets create a server block for phpPgAdmin. Create a file /etc/nginx/sites-available/phppgadmin
with the following contents. Note: This will only work if there exists a server block for the main domain.
# Server block for phppgadmin service
server{
server_name phppgadmin.example.com;
root /path/to/your/www/phppgadmin;
access_log /var/log/phppgadmin/access.log;
error_log /var/log/phppgadmin/error.log;
location ~ \.php$ {
try_files $uri =404;
fastcgi_split_path_info ^(.+\.php)(/.+)$;
fastcgi_pass unix:/var/run/php5-fpm.sock;
fastcgi_index index.php;
fastcgi_param SCRIPT_FILENAME /path/to/your/www/postgres$fastcgi_script_name;
include /etc/nginx/fastcgi_params;
}
}
By default Nginx includes all the servers in the folder /etc/nginx/sites-enabled
. So lets add a symbolic link to the new server we created to let nginx know about our new server!
sudo ln -s /etc/nginx/sites-available/phppgadmin /etc/nginx/sites-enabled/
Now all that is left to do is to restart/reload Nginx and our phpPgAdmin is ready to go!
sudo service nginx restart
or
sudo service nginx reload
Make sure you've included these in nginx.conf file
index index.php index.htm index.html;
root /usr/share/nginx/html;
Optional
A simple way to increase security is by being more restrictive with access to the phpPgAdmin page. You could do this by adding the following block within the server block for phppgadmin
shown above.
location / {
allow <your-IP-address / set-of-IP-address/ your-subnet>;
deny all;
}
And that is it! Make sure you run sudo service nginx reload
and your phpPgAdmin is ready to go. If you now go to phppgadmin.<your-domain>.com
, you must find the starting page where you can login with the role you created earlier.
sudo ln -s /etc/nginx/sites-available/grub_club /etc/nginx/sites-enabled/
sudo service nginx reload
