Computing: Website and Database Programming

Web development environment setup on MS Windows.

7.3.	PostgreSQL database server.
7.3.0.	PostgreSQL distributions.
	"PostgreSQL is a powerful, open source object-relational database system with over 35 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.", they write on the PostgreSQL website. It offers similar functionalities as MySQL and MariaDB. More performent thanks to advanced features such as materialized views and partial indexes, PostgreSQL is normally preferred over MySQL/MariaDB for the management of large databases. Also, operating systems like Unix and HP-UX do only support PostgreSQL. Globally, PostgreSQL is ranked less popular as RDBMS than MySQL, but more popular than MariaDB. If you want to learn more about the differences between PostgreSQL and MariaDB, you might want to have a look at PostgreSQL vs. MariaDB on the OpenLogic website. PostgreSQL is open source and may be downloaded free of charge from the PostgreSQL Download page. There are binary packages available for Windows, macOS, Linux, BSD and Solaris. The source code may be downloaded from GitHub. This tutorial describes the installation and usage of PostgreSQL 15.2.2 64bit on Windows 10. No guarantee that all, that is told here, applies to other versions of the database (or of Windows).
7.3.1.	Installing PostgreSQL.
	Double-click the installer (in my case: postgresql-15.2-2-windows-x64.exe) to launch the setup wizard. After the Welcome window (screenshot on the left) and the choice of the installation directory (default for v15: C:\Program Files\PostgreSQL\15), you come to the Components selection window (screenshot on the right). It is obvious that the server is required, you should also install pgAdmin (similar to MySQL Workbench and HeidiSQL), even if you use another way to administer your sever and manage your databases (in fact, there is a web interface, called phpPgAdmin, similar to phpMyAdmin for MySQL/MariaDB, and not described in this tutorial, as I did not install it so far). Stack Builder is an application that allows to add further features to your installation. The command line tools are only needed if you want to be able to access the database server from command Prompt (we will see an example of this further down in the text). Installation continues with the setup wizard asking for the data directory (default for v15: C:\Program Files\PostgreSQL\15\data), the superuser password, the port, the server will listen to (default: port 5432), and the locale to be used by the database server (I set it to English_United States.1252; the default, depending on the locale defined in Windows, would have been in my case Luxembourgish_Luxembourg.1252). Before the installation is started, a pre-installation summary is displayed, with the possibility to go back and change installation options. When installation is finished, you are asked if you want to launch Stack Builder to download and install additional components. I did not do it... Automatic server startup (default = automatic) can be configured in Control Panel > Administrative Tools > Services. The screenshot shows, how I set startup to "manual" (no reason for me to start the server with Windows, because if I work with databases, I normally use MySQL).
7.3.2.	Accessing PostgreSQL with pgAdmin.
	If you work with MySQL on Windows, you probably know MySQL Workbench, a graphical user interface that you can use for the administration of the MySQL server, the management of your databases and their tables, as well as the manipulation of the database data. There is a similar application for PostgreSQL: PgAdmin. PgAdmin can be launched from the Windows Start menu. When it starts up for the first time, you are asked for a master password. Then, PgAdmin starts, showing the server (or servers) that it knows about, one single one, called "PostgreSQL 15" in our case. To connect to the server, click on the corresponding item in the left pane of the window. You have to enter the password of superuser postgres to connect. The PgAdmin window is divided into two panes: on the left, a hierarchical list of the server objects, on the right, several tabs, that display specific information about the object selected in the left pane. When PgAdmin starts up, the server object itself is selected, and the Dashboard tab shows server activity in real time. The most important tab in the right pane is the Properties tab, that shows the details concerning the object actually selected. The screenshot shows the properties of the PostgreSQL 15 server. You can, in particular, see the connection information (host name and port), and the name of the maintenance database ("postgres"), the only database so far created. Selecting the "postgres" database in the left pane, we can view this database's properties. On the screenshot, you can, in particular, see that the owner of the database is superuser "postgres", that the encoding is UTF8 and that collation and character type are "English_United States.1252", as set during PostgreSQL installation. If, with the "postgres" database selected in the left pane, you open the SQL tab, the SQL statement used to create this database is displayed.
7.3.3.	Installing the "world" sample database.
	The MySQL "world" sample database has been ported to PostgreSQL and may be downloaded from the database samples page of the PostgreSQL website. Installing the database, using the SQL of the download file will not work (in particular table names must be prefixed with a schema name in PostgreSQL, what is not the case in the SQL of the download file). Please, follow these instruction to install the database using PgAdmin (details in the following paragraphs): Create the database using the menu commands. Create the tables, entering the (corrected) SQL in a Query tab. Copy the table data to three text files and load it into the database tables using the PostgreSQL "Copy from file" feature. Add the constraints, entering the SQL in a Query tab. Creating the database. Select Databases in the left pane of the PgAdmin window, then choose Object > Create > Database. Fill in the database properties, as shown on the two screenshots below. Creating the tables. As the download file is ANSI (and not UTF-8) encoded, you should tell the PostgreSQL server that the client uses LATIN1 encoding. This can be done by executing the statement     SET client_encoding = 'LATIN1'; Be sure that the "world§ database is selected in the left pane of the PgAdmin window. From the menu bar, choose Tools > Query Tool. This adds a Query tab to the right window. This is exactly the same as the Query tab in MySQL Workbench: Just enter your SQL statement(s) and push the Execute/Refresh button. Concerning the CREATE TABLE statements of the download file, you have to correct them by prefixing all table names with "public" (without the quotes)! The screenshot shows the successful creation of the "country" table. Loading the data. Find the data parts for each of the 3 tables in the download file and use it to create 3 text files that I called country.txt, city.txt and countrylanguages.txt. These files should be ANSI (not UTF-8) encoded (in Notepad++, choose Encoding > Convert to ANSI). Then use PostgreSQL COPY statements to load the data into the tables. The screenshot shows how I filled the "city" table with the data of the file city.txt, located at X:\TEMP\Archives\world. Applying the constraints. Just enter the corresponding SQL statements of the download file into a Query tab in PgAdmin and execute them. Testing the database. You can use this simple query, that returns the number of cities in the "city" table, as a quick test of the new database:     SELECT COUNT(*) FROM public.city; The result should normally be 4079. The screenshot below shows the execution of a somewhat more complex query: display of the Mexican cities with a population greater than 1 million.
7.3.4.	Creating database users.
	Database user creation seems more complicated in PostgreSQL than in MySQL and I did not find a way to simply do what I wanted in PgAdmin. In fact, I wanted to create a read-only user called "nemo" who is allowed to select the data from the "world" database (without being allowed to modify it) and a read-write user named "allu", who is allowed to modify the data of the "world" database (without being allowed to modify the database structure). The simplest way to create these users is (probably) to write the SQL and execute the statements using the Query Tool in PgAdmin. One important thing, that we must consider here, is that all PostgreSQL objects inherit some properties from the objects they depend on. For our database users this means that whatever privileges we give them in schema "public", they also inherit the default privileges for all users on this schema. Thus, the first step to take is to revoke the CREATE privilege from the schema "public" and to revoke all privileges on the databases "postgres" and "world" from this schema. In other words: making sure that the new users can't do anything on any database without becoming an explicit grant. To realize this, execute the following statements in a Query tab of PgAdmin:     -- Making sure roles can't do anything on the database without explicit grant     REVOKE CREATE ON SCHEMA public FROM public;     REVOKE ALL ON DATABASE postgres FROM public;     REVOKE ALL ON DATABASE world FROM public; Now let's create the new role "read-only". This role must have the right to connect to the "world" database and be able to select data from all its tables (what implies that he must have the right to use the schema "public" - that after the queries of the previous paragraph does no longer includes the CREATE privilege). Here the SQL:     -- Creating read-only role     CREATE ROLE read_only;     GRANT CONNECT ON DATABASE world TO read_only;     GRANT USAGE ON SCHEMA public TO read_only;     GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;     ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only; where the last of these statements modifies the default privileges in schema "public", giving the role "read_only" the SELECT privilege (on any new tables created in this schema). And similarly let's create the new role "read-write". This role must have the right to connect to the "world" database and be able to select, insert, update and delete data of all its tables (what implies that he must have the right to use the schema "public"). He should also be allowed to use all sequences and to execute all functions. You can create this role using the following SQL statements:     -- Creating read-write role     CREATE ROLE read_write;     GRANT CONNECT ON DATABASE world TO read_write;     GRANT USAGE ON SCHEMA public TO read_write;     GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write;     ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO read_write;     GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO read_write;     ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO read_write;     GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO read_write;     ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO read_write; Again, we modify the default privileges in schema "public", giving the role "read_write" the SELECT, INSERT, UPDATE and DELETE privilege (on any new tables created in this schema), as well as the usage of new sequences and the execution of new functions in this schema. Roles may be seen as user groups, thus to be able to connect to the "world" database, we have to create two users, who are granted the privilege of the read_only and the read_write role respectively. SQL:     -- Creating users     CREATE USER allu WITH PASSWORD 'MyPassword';     GRANT read_write TO allu;     CREATE USER nemo WITH PASSWORD 'nemo';     GRANT read_only TO nemo; If we select user "allu" in the left pane of PgAdmin and open the SQL tab in the right pane, we get what is shown on the screenshot below. The first statement creates the user (for PostgreSQL, a user is a role with CONNECT privilege), with given properties enabled, and others disabled. The second statement grants the user the privileges of role "read_write" (you could also call it: makes allu member of the group "read_write"). Note: Executing the SQL statements above, we have made several changes to the default properties of the "public" schema. A better practice would have (probably) been to create a new custom schema and make the default modifications to that schema (to do this, we would have had to create the tables of the "world" database in that custom schema and not in "public", of course). Testing the users. The PostgreSQL setup program does not change the Windows environment variables, in particular it does not add the path to the PostgreSQL command line tools to the PATH variable. We will do this now (manually) in order to be able to comfortably use the psql utility to test the two new database users. To change the environment variables in Windows 10, open Settings and choose System > About; click the Advanced system settings link to open System Properties. Push the Environment variables button. In the list of the System variables, select Path and push the Edit button. In the Edit environment variable window, push the New button and in the new row of the path table, add C:\Program Files\PostgreSQL\15\bin. As said above, we'll use psql to test the newly created users. This utility is a PostgreSQL shell, where you can, among other, execute SQL statements just the way you do in the Query tabs of PgAdmin. The general format to connect to a database using psql is     psql -U {user-name} -d {database name} the password being asked after the command has been entered. So, open command prompt and enter     psql -U allu -d world (to connect as read-write user "allu"), resp.     psql -U nemo -d world (to connect as read-only user "nemo"). The screenshots show how I connected to "World" as "allu" (at the left) and as "nemo" (at the right). You can see that "allu" may view (SELECT), as well as change (UPDATE) the population of a city, whereas "nemo" too may view it, but if, connected as "nemo", you try to update it, you get the error message "permission denied for table city".
7.3.5.	Accessing PostgreSQL with PHP.
	Here is the source code of a simple PHP script (I called it pgsql.php and placed it in the /php directory on my Apache webserver), that reads and displays the number of records in the "city" table of the "world" database.     <html>         <head>             <title>PHP-PostgreSQL test</title>         </head>         <body><p>             <?php                 $host= '127.0.0.1'; $database='world'; $user = 'nemo'; $passwd = 'nemo';                 $dbcon = pg_connect("host=$host dbname=$database user=$user password=$passwd");                 $sql = "SELECT count(*) AS _count FROM public.city";                 $uresult = pg_query($dbcon, $sql);                 $count = 0;                 if ($uresult) {                     $row = pg_fetch_assoc($uresult); $count = $row['_count'];                 }                 echo "Number of cities in database 'world' = $count";             ?>         </p></body>     </html> When trying to run the script (by entering localhost/php/pgsql.php in the web browser address field), I got the error message Call to undefined function pg_connect(), as shown on the screenshot below. If you are familiar with accessing MySQL from a PHP script, you probably know what we have to do: enabling the PostgreSQL extension(s) in the PHP configuration file. To do this, uncomment the following two lines in php.ini (and restart the Apache service):     extension=pdo_pgsql     extension=pgsql However, as a difference with MySQL this is not enough: Even after enabling the PostgreSQL extensions, the database functions do not work! The exactly same error message as above is displayed. What happens here is that Apache, despite the fact that we enabled the PostgreSQL extensions in php.ini, does not load the corresponding DLL. No idea if this is a bug, or why it doesn't work... Anyway, it's quite easy to solve the problem: telling Apache explicitly to load the libpq DLL. This is done by inserting the following line into Apache's httpd.conf file:     LoadFile "C:/Program Files/PostgreSQL/15/bin/libpq.dll" Change httpd.conf, restart Apache and retry localhost/php/pgsql.php. The number of cities in the "world" database should now be correctly displayed.
7.3.6.	Accessing PostgreSQL with Perl.
	Here is the source code of a simple Perl command line script (I called it pgsql.pl), that, as the PHP script, displays the number of cities in the "world" database.     use strict; use warnings;     use DBI;     my $dsn = "dbi:Pg:dbname=world";     my $username = 'nemo'; my $password = 'nemo';     my $dbh = DBI->connect($dsn, $username, $password, {RaiseError => 1},)         or die $DBI::errstr;     my $sql = "SELECT COUNT(*) FROM public.city";     my $sth = $dbh->prepare($sql);     $sth->execute();     my ($count) = $sth->fetchrow();     print "\nNumber of cities in the world.city table = $count\n\n";     $sth->finish();     $dbh->disconnect(); Perl access to all major databases is done using Perl DBI, a global interface for database connection, the given database specific code being taken from the database specific driver module. In the case of MySQL, we use dbi:mysql, in the case of PostgreSQL, we use dbi:Pg (as you can see in the code above). The module dbi:Pg is included by default with Strawberry Perl. Thus, we have nothing to install, and nothing to configure; just run the script!
7.3.7.	Accessing PostgreSQL with Python.
	There are several ways to connect to PostgreSQL from Python; the one described here uses the psycopg2 database adapter. You can install it using pip:     python -m pip install -U pip     python -m pip install psycopg2-binary where the first of these commands updates pip itself and the second one installs the database adapter (psycopg2-binary-2.9.6 in my case). Here is the source code of a simple Python CGI script that displays the number of cities in the "world" database.    #!C:/Users/allu/AppData/Local/Programs/Python/Python310/python.exe    import psycopg2    print("Content-type: text/html")    print()    print("<html>")    print("<head><title>Python PostgreSQL test</title></head>")    print("<body>")    db = psycopg2.connect(host='localhost', port='5432', user='nemo', password='nemo', database='world')    cursor = db.cursor()    cursor.execute("SELECT count(*) FROM public.city")    count = cursor.fetchone()[0]    print("<p>Number of cities in database 'world' = ", count, "</p>")    print("</body>")    print("</html>")    db.close() Save the script as pgsql.py into the /cgi-bin directory of your webserver and run it, typing localhost/cgi-bin/pgsql.py.
7.3.8.	Accessing PostgreSQL with Lazarus/Free Pascal.
	To connect to a PostgreSQL database with Free Pascal applications using a TPQConnection, please have a look at my Using PostgreSQL databases with Lazarus/Free Pascal tutorial in the Lazarus/Free Pascal Programming section.

---

If you find this text helpful, please, support me and this website by signing my guestbook.