Problem scenario
You want a web page that asks for a username and password (e.g., to log in). The users will not know if the authentication is done via Apache or Postgres. You happen want the authentication to be challenged against a Postgres database. This way if you have backend access to the Postgres database, you can use "CREATE ROLE" or "CREATE ROLE jane PASSWORD 'veryfun' LOGIN" to create users. You will be able to provide the users with the credentials and web URL. They will gain access to another page other than the landing page if authentication is successful. This second page may or may not invoke SQL commands. The users will not need to have knowledge of SQL. They will not need special software installed on their computers.
How do you deploy LAPP (Linux, Apache, Postgres, and PHP) with a web page frontend to challenge credentials?
Solution
Make sure you understand the security risks of this before implementing it. This example solution assumes you have installed Apache web server, PHP, php-pgsql, and Postgres on a Linux server (i.e., you have deployed LAPP and need to install php-pgsql). To install LAPP see this link. To install php-pgsql, see this link. This solution also assumes the default location for the web files is /var/www/html/. Standard deployments of Apache (without special configuration) on Debian/Ubuntu distributions of Linux use this location.
#1 Put this file (<html> tag to </html> tag below) in /var/www/html/ under the name gooda.php:
<html>
<body>
<form action="goodb.php" method="post">
UserName: <input type="text" name="username"><br>
Password: <input type="password" name="password"><br>
<input type="submit">
</form>
</body>
</html>
#2 Put this file (<html> tag to "end of file") in /var/www/html/ under the name goodb.php:
<html>
<body>
Username entered: <?php echo $_POST["username"]; ?><br>
<?php $part1 = $_POST["username"]; ?>
<?php $part2 = $_POST["password"]; ?>
<?php $comp = 'user='.$part1.' password='.$part2 ; ?>
</body>
</html>
<?php // echo "Hello world!";
$host = "host=127.0.0.1";
$port = "port=5432";
$dbname = "dbname=cooldb";
$credentials = $comp;
$db = pg_connect( "$host $port $dbname $credentials" );
pg_query("CREATE DATABASE niftydb");
?> //end of file
3. Change the "cooldb" to the name of an existing database. Change the "niftydb" to the name of the database you want the SQL DDL command to create. You may want to change the pg_query command altogether. It is up to you as this is just an example.
4. Know that this is a proof-of-concept for educational purposes. For sensitive passwords over the internet, you may want other authentication and security in place. These PHP files would be appropriate behind a firewall in an enterprise's development or QA environment. If you browse to the gooda.php page from a web browser (assuming the postgres and Apache web services are running on the Linux server itself), you will be able to type in a username and password for Postgres. The pg_query command will run if the authentication was successful.