MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘PostgreSQL Developer’ tag

Ubuntu DaaS VM

without comments

Completed the build of my new Ubuntu Virtual Machine (VM) with Oracle 23c installed in a Docker instance, and MySQL and PostgreSQL installed locally. The VM image also provides a LAMP stack with Apache2, PHP 8.1 with MySQLi and PDO modules. Since the original post, I’ve added a number of items to support our program courses and the Data Science degrees.

There are lots of tricks and techniques in the blog associated with creating the build. I took the background photograph of Chapel Bridge early on Sunday morning August 30, 1987 in Lucerne Switzerland (with a Canon A1 and ASA 64 slide film that subsequently digitized well).

Next step: roll it out into an AWS image for the students to use for their courses.

Related blog posts:

As always, I hope the post and information helps others.

Written by maclochlainn

December 13th, 2023 at 1:15 am

Python3 on PostgreSQL

without comments

The necessary Python 3 driver for connections to the PostgreSQL database is python3-psycopg2, as qualified by this earlier post with full test examples for Red Hat distributions. You can install it on Ubuntu with the following command:

sudo apt-get install -y python3-psycopg2

As always, I hope this helps those looking for a solution. Also, remember the referenced post above provides Linux distribution neutral full solutions.

Written by maclochlainn

December 3rd, 2023 at 11:00 pm

PL/pgSQL Test Q?

without comments

Intriguing little PostgreSQL PL/pgSQL test question posed by an interviewer of one of my students. Basically, how many times will this loop and what will it return or will it generate an error. (BTW, they wrapped it into a named function.)

DO
$$
BEGIN
  FOR i IN 0.2..1.5 LOOP
    RAISE NOTICE '%', i;
  END LOOP;
END;
$$;

It’ll loop three times and return 0, 1, and 2 because the double numbers entered as boundaries to the for-loop are implicitly case as integers.

Written by maclochlainn

October 26th, 2023 at 7:46 pm

SQL Developer & PostgreSQL

without comments

I had a request from one of the adjunct professors to connect SQL Developer to the PostgreSQL database. This is in support of our database programming class that teaches students how to write PL/SQL against the Oracle database and pgPL/SQL against the PostgreSQL database. We also demonstrate transactional management through Node.js, Python and Java.

Naturally, this is also a frequent step taken by those required to migrate PostgreSQL data models to an Oracle database. While my final solution requires mimicking Oracle’s database user to schema, it does work for migration purposes. I’ll update this post when I determine how to populate the database drop-down list.

The first step was figuring out where to put the PostgreSQL JDBC Java ARchive (.jar) file on a Linux distribution. You navigate to the end-user student account in a Terminal and change to the .sqldeveloper directory. Then, create a jdbc subdirectory as the student user with the following command:

mkdir /home/student/.sqldeveloper/jdbc

Then, download the most current PostgreSQL JDBC Java ARchive (.jar) file and copy it into the /home/student/.sqldeveloper/jdbc, which you can see afterward with the following command:

ll /home/student/.sqldeveloper/jdbc

It should display:

-rw-r--r--. 1 student student 1041081 Aug  9 13:46 postgresql-42.3.7.jar

The next series of steps are done within SQL Developer. Launch SQL Developer and navigate to Tools and Preferences, like this:

Inside the Preferences dialog, navigate to Database and Third Party JDBC Drivers like shown and click the Add Entry button to proceed:

Inside the Select Path Entry dialog, select the current PostgreSQL JDBC Java ARchive (.jar) file, which is postgresql-42-3.7.jar in this example. Then, click the Select button.

You are returned to the Preferences dialog as shown below. Click the OK button to continue.

After completing the 3rd Party Java Driver setup, you attempt to create a new connection to the PostgreSQL database. You should see that you now have two available Database Type values: Oracle and PostgreSQL, as shown below:

When you click on the PostgreSQL Database Type, the dialog updates to the following view. Unfortunately, I couldn’t discover how to set the values in the list for the Choose Database drop down. Naturally, a sandboxed user can’t connect to the PostgreSQL database without qualifying the database name.

Unless you qualify the PostgreSQL database or connect as the postgres user with a privileged password, SQL Developer translates the absence of a database selection to a database name equivalent to the user’s name. That’s the default behavior for the Oracle database but differs from the behavior for MySQL, PostgreSQL, and Microsoft SQL Server. It returns the following

Status: Failure - Test failed: FATAL: database "student" does not exist

As seen in the diaglog’s result when testing the connection:

Based on my hunch and not knowing how to populate the database field for the connection, I did the following:

  • Created a Linux OS videodb user.
  • Copied the .bashrc file with all the standard Oracle environment variables.
  • Created the /home/videodb/.sqldeveloper/jdbc directory.
  • Copied the postgresql-42.3.7.jar into the new jdbc directory.
  • Connected as the postgres super user and created the PostgreSQL videodb user with this syntax:

    CREATE USER videodb
      WITH ROLE dba
           ENCRYPTED PASSWORD 'cangetin';
  • As the postgres super user, granted the following privileges:

    -- Grant privileges on videodb database videodb user.
    GRANT ALL ON DATABASE "videodb" TO "videodb";
     
    -- Connect to the videodb database.
    \c
     
    -- Grant privileges.
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO videodb;
    GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO videodb;
  • Added the following line to the pg_hba.conf file in the /var/lib/pgsql/15/data directory as the postgres user:

    local   all             videodb                                 peer
  • Connected as the switched from the student to videodb Linux user, and launched SQL Developer. Then, I used the Tools menu to create the 3rd party PostgreSQL JDBC Java ARchive (.jar) file in context of the SQL Developer program. Everything completed correctly.
  • Created a new PostgreSQL connection in SQL Developer and tested it with success as shown:

  • Saving the new PostgreSQL connection, I opened the connection and could run SQL statements and display the catalog information, as shown:

    Connected as the videodb user to the videodb database I can display tables owned by student and videodb users:

    -- List tables.
    \d
     
                       List of relations
     Schema |           Name           |   Type   |  Owner
    --------+--------------------------+----------+---------
     public | new_hire                 | table    | student
     public | new_hire_new_hire_id_seq | sequence | student
     public | oracle_test              | table    | videodb
    (3 rows)

    In SQL Developer, you can also inspect the tables, as shown:

At this point, I’m working on trying to figure out how to populate the database drop-down table. However, I’ve either missed a key document or it’s unfortunate that SQL Developer isn’t as friendly as MySQL Workbench in working with 3rd Party drivers.

Written by maclochlainn

August 8th, 2023 at 11:29 pm

PostgreSQL Java

without comments

The majority of information to write this post comes form knowing how Java works and where to find the PostgreSQL JDBC Java archive (.jar) file and the standard documentation. Here are the URLs:

The rest of the example is simply demonstrating how to create a fully working program to return one or more rows from a static query. After you download the latest PostgreSQL JDBC archive, with a command like:

wget https://jdbc.postgresql.org/download/postgresql-42.3.7.jar

Assuming you put it in test directory, like /home/student/java, you would add it to your Java $CLASSPATH environment variable, like this:

export set CLASSPATH="/home/student/Code/java/postgresql-42.3.7.jar:."

If you’re new to Java and Linux, the . (dot) represents the present working directory and is required in the Java $CLASSPATH to avoid raising a java.lang.ClassNotFoundException when you test your code. For example, the sample program name is PostgreSQLDriver.java and if you failed to include the present working directory in the $CLASSPATH it would raise the following error message when you try to run the compiled class file:

Error: Could not find or load main class PSQL
Caused by: java.lang.ClassNotFoundException: PSQL

Now that you’ve set your Java $CLASSPATH correctly, you can copy or type this PostgreSQLDriver.java Java program into a file.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
// Import classes.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
 
/* You can't include the following on Linux without raising an exception. */
// import com.mysql.jdbc.Driver;
 
public class PostgreSQLDriver {
  public PostgreSQLDriver() {
    /* Declare variables that require explicit assignments because
       they're addressed in the finally block. */
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
 
    /* Declare other variables. */
    String url;
    String username = "student";
    String password = "student";
    String database = "videodb";
    String hostname = "[::1]";
    String port = "5432";
    String sql;
 
    /* Attempt a connection. */
    try {
      // Set URL.
      url = "jdbc:postgresql://" + hostname + ":" + port + "/" + database;
 
      // Create instance of MySQLDriver.
      conn = DriverManager.getConnection (url, username, password);
 
      // Query the version of the database.
      sql = "SELECT version()";
      stmt = conn.createStatement();
      rset = stmt.executeQuery(sql);
 
      System.out.println ("Database connection established");
 
      // Read row returns for one column.
      while (rset.next()) {
        System.out.println("PostgreSQL Connected to the [" + rset.getString(1) + "] database."); }
 
    }
    catch (SQLException e) {
      System.err.println ("Cannot connect to database server:");
      System.out.println(e.getMessage());
    }
    finally {
      if (conn != null) {
        try {
          rset.close();
          stmt.close();
          conn.close();
          System.out.println ("Database connection terminated");
        }
        catch (Exception e) { /* ignore close errors */ }
      }
    }
  }
  /* Unit test. */
  public static void main(String args[]) {
    new PostgreSQLDriver();
  }
}

Now, you compile the program from the present working directory with this syntax:

javac PostgreSQLDriver.java

It creates a PostgreSQLDriver.class file, which you can run with this syntax:

java PostgreSQLDriver

It will return the following, which verifies you’ve connected to a valid database in the PostgreSQL instance. You should note that the IPV6 syntax is used in the example on line #25 but you could substitute localhost, an assigned host name, or an IP address.

Written by maclochlainn

August 8th, 2023 at 8:12 pm

TDE on PostgreSQL

without comments

The scope of Transparent Data Encryption (TDE) in PostgreSQL only applies to columns. It does not encrypt other aspects of the database, like table-level and database-level encryption; and those who deploy PostgreSQL may need to implement additional security measures to protect these database components.

You need to know two key elements before exploring TDE in PostgreSQL: Scheme inside a database and extensions. Unlike many databases, PostgreSQL schemas are not synonymous with a database. You may have multiple scheme (or, alternatively schemas) inside any PostgreSQL database.

Creating an extension is a one time event. Therefore, it’s easier to show you that first. You create a pgcrypto extension with the following command:

CREATE EXTENSION pgcrypto;

The public schema is the one most users deploy but for the purpose of hiding our AES encryption key this example creates a hidden schema. Unless you change the default find setting the hidden schema is not visible when connecting to the database.

You create the hidden schema with the following idimpotent (re-runnable) set of commands:

/* Drop dependent objects before dropping the schema. */
DROP TABLE IF EXISTS hidden.aes_key;
DROP FUNCTION IF EXISTS hidden.get_aes_key;
 
/*
 *  Drop function with cascade to remove the
 *  film_character_t trigger at same time.
 */
DROP FUNCTION IF EXISTS hidden.film_character_dml_f CASCADE;
 
/* Drop the schema conditionally. */
DROP SCHEMA IF EXISTS hidden;
 
/* Create the schema. */
CREATE SCHEMA hidden;

Next, we need to create a aes_key table and get_aes_key function in the hidden schema. The table will store the AES encryption key and the function lets us create an AES encryption key.

/* Create an aes encryption key table. */
CREATE TABLE hidden.aes_key
( aes_key  text );
 
/* Create a hidden function to build an AES encryption key. */
CREATE OR REPLACE
  FUNCTION hidden.get_aes_key() RETURNS text AS
  $$
  BEGIN
    RETURN gen_random_bytes(16)::text;
  END;
  $$
  LANGUAGE plpgsql;

After creating the public get_key() function, you insert a single row to the aes_key table by prefacing it with the hidden schema name, like this:

/* Insert the AES encryption key into a table. */ 
INSERT INTO hidden.aes_key
( aes_key )
VALUES
( hidden.get_aes_key());

Having built the plumbing for our AES encryption key, let’s show you how to encrypt and decrypt string values. This example lets you create an idimpotent film_character table in the public schema, like:

/* Drop the table conditionally. */  
DROP TABLE IF EXISTS film_character;
 
/* Create the demonstration table for encrypting and decrypting strings. */ 
CREATE TABLE film_character
( character_id    serial PRIMARY KEY
, plain_text      text
, encrypted_text  bytea );

After creating the AES encryption key table, function, and inserting a row of data, you need to create a public get_key() function, like:

/* Create a public function to retrieve the AES encryption key. */
CREATE OR REPLACE
  FUNCTION get_key() RETURNS text AS
  $$
  DECLARE
    retval  text;
  BEGIN
    SELECT aes_key INTO retval FROM hidden.aes_key;
    RETURN retval;
  END;
  $$
  LANGUAGE plpgsql;

The following INSERT statement write a plain text column and encrypted text column into the film_character table. The get_key() function hides how the pgp_sym_encrypt function encrypts the string.

/* Insert plain and encrypted text into a table. */
INSERT INTO film_character
( plain_text
, encrypted_text )
VALUES
('Severus Snape'
, pgp_sym_encrypt('Slytherin',get_key()));

The following query displays the plain and encrypted text stored in a row of the film_character table.

/* Query plain and encrypted text from a table. */
SELECT character_id
,      plain_text
,      encrypted_text
FROM   film_character;

It displays:

 character_id |  plain_text   | encrypted_text
--------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------
            1 | Severus Snape | \xc30d04070302fa1c4eebd90204cc7bd23901f1d4fa91b2455c3ef2987a305aebe01a4d94f9ebb467d6cb7a3846342ccd09cb55ac5e82a71cbaef93728fbeb4aaa9bf71b6fb93457758d1
(1 row)

Last, the following query displays the plain and decrypted text with the pgp_sym_decrypt function in a query:

/* Query the plain and decrypted text from a table. */
SELECT character_id
,      plain_text
,      pgp_sym_decrypt(encrypted_text,get_key()) AS encrypted_text
FROM   film_character;

The query returns the plain and decrypted values:

 character_id |  plain_text   | encrypted_text
--------------+---------------+-----------------
            1 | Severus Snape | Slytherin
(1 row)

However, this approach exposes the method for encrypting the encrypted_text column’s string value. You can hide this by creating a film_character_dml_f function in the hidden schema and a film_character_t trigger in the public schema, like:

/* Create trigger function for insert or update. */
CREATE FUNCTION hidden.film_character_dml_f()
  RETURNS trigger AS
$$
DECLARE
  /* Declare local variable. */
  unencrypted_input  VARCHAR(30);
BEGIN
  unencrypted_input := new.encrypted_text::text;
  /* Encrypt the column. */
  new.encrypted_text := pgp_sym_encrypt(unencrypted_input,get_key());
 
  /* Return new record type. */
  RETURN NEW;
END;
$$
LANGUAGE plpgsql;
 
CREATE TRIGGER film_character_t
  BEFORE INSERT OR UPDATE ON film_character
  FOR EACH ROW EXECUTE FUNCTION hidden.film_character_dml_f();

Now, you can insert the plain text data in an INSERT statement and the encryption occurs without disclosing how it happens. Here’s a sample statement:

INSERT INTO film_character
( plain_text
, encrypted_text )
VALUES
('Harry Potter'
,'Gryffindor');

A query of the table shows you that both rows have an encrypted value in the encrypted_text column.

/* Query plain and encrypted text from a table. */
SELECT character_id
,      plain_text
,      encrypted_text
FROM   film_character;

Displayed like:

 character_id |  plain_text   |                                                                                   encrypted_text
--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            1 | Severus Snape | \xc30d040703026716034f140d83e76cd23a01f99168afebe50d760b85c69373e3947c74473115a939843887db8e102cd0b2524378f4d684e0ba91c20afc436a056cd983fc47794eef7d4904
            2 | Harry Potter  | \xc30d040703020d8cc71d1f84e1ef6fd24701fd308f669e28a6135beac130fc51a6ccb5cef3c5005f4f557207fe5c84c4aedbb5b098dc9a882a9b7d801c61e34cd90517b4628b5a18b96b3fc61663b48391146b8c0fa2a858
(2 rows)

As always, I hope this code complete solution helps those trying to work with this technical stack.

Written by maclochlainn

January 8th, 2023 at 10:23 am

AlmaLinux Install & Configuration

without comments

This is a collection of blog posts for installing and configuring AlmaLinux with the Oracle, PostgreSQL, MySQL databases and several programming languages. Sample programs show how to connect PHP and Python to the MySQL database.

I used Oracle Database 11g XE in this instance to keep the footprint as small as possible. It required a few tricks and discovering the missing library that caused folks grief eleven years ago. I build another with a current Oracle Database XE after the new year.

If you see something that I missed or you’d like me to add, let me know. As time allows, I’ll try to do that. Naturally, the post will get updates as things are added later.

AlmaLinux+PostgreSQL

with one comment

This installs PostgreSQL 15 on AlmaLinux 9 (don’t forget the PostgreSQL 15 Documentation site). The executable is available in the script that the postgresql.org provides; however, it seems appropriate to show how to find that script for any platform.

When you launch the postgres.org web site, you will see the following dialog. Click the Download-> button to choose an operating system.

On the next webpage, click on the Linux icon button to proceed.

This page expands for you to choose a Linux distribution. Click on the Red Hat/Rocky/CentOS button to proceed.

This web page lets you choose a platform, which should be Red Hat Enterprise, Rocky, or Oracle version 9.

The selection fills out the web page and provides a setup script. The script installs the PostgreSQL packages, disables the built-in PostgreSQL module, installs PostgreSQL 15 Server, initialize, enable, and start PostgreSQL Server.

Here are the detailed steps:

  1. Install the PostgreSQL by updating dependent packages before installing it with the script provided by the PostgreSQL download web site:

    # Install the repository RPM:
    sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
     
    # Disable the built-in PostgreSQL module:
    sudo dnf -qy module disable postgresql
     
    # Install PostgreSQL:
    sudo dnf install -y postgresql15-server
     
    # Optionally initialize the database and enable automatic start:
    sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
    sudo systemctl enable postgresql-15
    sudo systemctl start postgresql-15

  2. The simpmlest way to verify the installation is to check for the psql executable. You can do that with this command:

    which psql

    It should return:

    /usr/bin/psql
  3. Attempt to login with the following command-line interface (CLI) syntax:

    psql -U postgres -W

    It should fail and return the following:

    psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "postgres"

    This error occurs because you’re not the postgres user, and all other users must designate that they’re connecting to an account with a password. The following steps let you configure the Operating System (OS).

    • You must shell out to the root superuser’s account, and then shell out to the postgres user’s account to test your connection because postgres user’s account disallows direct connection.

      su - root
      su - postgres

      You can verify the current postgres user with this command:

      whoami

      It should return the following:

      postgres

      As the postgres user, you connect to the database without a password. You use the following syntax:

      psql -U postgres

      It should display the following:

      psql (15.1)
      Type "help" for help.
    • At this point, you have some operating system (OS) stuff to setup before configuring a PostgreSQL sandboxed videodb database and student user. Exit psql with the following command:

      postgres=# \q

      Navigate to the PostgreSQL home database directory as the postgres user with this command:

      cd /var/lib/pgsql/15/data

      Edit the pg_hba.conf file to add lines for the postgres and student users:

      # TYPE  DATABASE        USER            ADDRESS                 METHOD
       
      # "local" is for Unix domain socket connections only
      local   all             all                                     peer
      local   all             postgres                                peer
      local   all             student                                 peer
       
      # IPv4 local connections:
      host    all             all             127.0.0.1/32            scram-sha-256
      # IPv6 local connections:
      host    all             all             ::1/128                 scram-sha-256
      # Allow replication connections from localhost, by a user with the
      # replication privilege.
      local   replication     all                                     scram-sha-256
      host    replication     all             127.0.0.1/32            scram-sha-256
      host    replication     all             ::1/128                 scram-sha-256

      Navigate up the directory tree from the /var/lib/pgsql/15/data directory, which is also the data dictionary, to the following /var/lib/pgsql/15 base directory:

      cd /var/lib/pgsql/15

      Create a new video_db directory. This is where you will deploy the video_db tablespace. You create this directory with the following command:

      mkdir video_db

      Change the video_db permissions to read, write, and execute for only the owner with this syntax as the postgres user:

      chmod 700 video_db
    • Exit the postgres user with the exit command and open PostgreSQL’s 5432 listener port as the root user. You can use the following command, as the root user:

      firewall-cmd --zone=public --add-port 5432/tcp --permanent
    • You must shell out from the root user to the postgres user with the following command:

      su - postgres
  4. Connect to the postgres account and perform the following commands:

    • After connecting as the postgres superuser, you can create a video_db tablespace with the following syntax:

      CREATE TABLESPACE video_db
        OWNER postgres
        LOCATION 'C:\Users\username\video_db';

      This will return the following:

      CREATE TABLESPACE

      You can query whether you successfully create the video_db tablespace with the following:

      SELECT * FROM pg_tablespace;

      It should return the following:

        oid  |  spcname   | spcowner | spcacl | spcoptions
      -------+------------+----------+--------+------------
        1663 | pg_default |       10 |        |
        1664 | pg_global  |       10 |        |
       16389 | video_db   |       10 |        | 
      (3 rows)
    • You need to know the PostgreSQL default collation before you create a new database. You can write the following query to determine the default correlation:

      postgres=# SELECT datname, datcollate FROM pg_database WHERE datname = 'postgres';

      It should return something like this:

       datname  | datcollate  
      ----------+-------------
       postgres | en_US.UTF-8
      (1 row)

      The datcollate value of the postgres database needs to the same value for the LC_COLLATE and LC_CTYPE parameters when you create a database. You can create a videodb database with the following syntax provided you’ve made appropriate substitutions for the LC_COLLATE and LC_CTYPE values below:

      CREATE DATABASE videodb
        WITH OWNER = postgres
        ENCODING = 'UTF8'
        TABLESPACE = video_db
        LC_COLLATE = 'en_US.UTF-8'
        LC_CTYPE = 'en_US.UTF-8'
        CONNECTION LIMIT = -1;

      You can verify the creation of the videodb with the following command:

      postgres# \l

      It should show you a display like the following:

                                                       List of databases
         Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges   
      -----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
       postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | 
       template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
                 |          |          |             |             |            |                 | postgres=CTc/postgres
       template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
                 |          |          |             |             |            |                 | postgres=CTc/postgres
       videodb   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | 
      (4 rows)

      Then, you can assign comment to the database with the following syntax:

      COMMENT ON DATABASE videodb IS 'Video Store Database';
  5. Create a Role, Grant, and User:

    In this section you create a dba role, grant privileges on a videodb database to a role, and create a user with the role that you created previously with the following three statements. There are three steps in this sections.

    • The first step creates a dba role:

      CREATE ROLE dba WITH SUPERUSER;
    • The second step grants all privileges on the videodb database to both the postgres superuser and the dba role:

      GRANT TEMPORARY, CONNECT ON DATABASE videodb TO PUBLIC;
      GRANT ALL PRIVILEGES ON DATABASE videodb TO postgres;
      GRANT ALL PRIVILEGES ON DATABASE videodb TO dba;

      Any work in pgAdmin4 requires a grant on the videodb database to the postgres superuser. The grant enables visibility of the videodb database in the pgAdmin4 console as shown in the following image.

    • The third step changes the ownership of the videodb database to the student user:

      ALTER DATABASE videodb OWNER TO student;

      You can verify the change of ownership for the videodb from the postgres user to student user with the following command:

      postgres# \l

      It should show you a display like the following:

                                                       List of databases
         Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges   
      -----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
       postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | 
       template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
                 |          |          |             |             |            |                 | postgres=CTc/postgres
       template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
                 |          |          |             |             |            |                 | postgres=CTc/postgres
       videodb   | student  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =Tc/student          +
                 |          |          |             |             |            |                 | student=CTc/student  +
                 |          |          |             |             |            |                 | dba=CTc/student
      (4 rows)
    • The fourth step creates a student user with the dba role:

      CREATE USER student
        WITH ROLE dba
             ENCRYPTED PASSWORD 'student';

      After this step, you need to disconnect as the postgres superuser with the following command:

      \q
  6. Connect to the videodb database as the student user with the PostgreSQL CLI, create a new_hire table and quit the database.

    The following syntax lets you connect to a videodb database as the student user. You should note that the Linux OS student user name should match the database user name.

    psql -Ustudent -W -dvideodb

    You create the new_hire table in the public schema of the videodb database with the following syntax:

    CREATE TABLE new_hire
    ( new_hire_id  SERIAL        CONSTRAINT new_hire_pk PRIMARY KEY
    , first_name   VARCHAR(20)   NOT NULL
    , middle_name  VARCHAR(20)
    , last_name    VARCHAR(20)   NOT NULL
    , hire_date    DATE          NOT NULL
    , UNIQUE(first_name, middle_name, hire_date));

    You can describe the new_hire table with the following command:

    \d new_hire

    You quit the psql connection with a quit; or \q, like so

    quit;
  7. Installing, configuring, and launching pgadmin4 (don’t forget the pgAdmin 4 Documentation site):

    • You need to install three sets of packages. They’re the pgadmin-server, policycoreutils-python-utils, and pgadmin4-desktop.

      • Apply the pgadmin-server package:

        sudo yum install https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/redhat/rhel-9Server-x86_64/pgadmin4-server-6.16-1.el9.x86_64.rpm

      • Apply or upgrade (which is the default at this point) the policycoreutils-python-utils package:

        sudo dnf install policycoreutils-python-utils

      • Apply the pgadmin4-desktop package:

        sudo dnf install -y https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/redhat/rhel-9Server-x86_64/pgadmin4-desktop-6.16-1.el9.x86_64.rpm

    • You configure your .bashrc file to add the pgadmin4 directory to your $PATH environment variable.

      # Add the pgadmin4 executable to the $PATH.
      export set PATH=$PATH:/usr/pgadmin4/bin

      You also configure your .bashrc file to add a pgadmin4 function, which simplifies how you call the pgadmin4 executable.

      # Function to ensure pgadmin4 call is simplified and without warnings.
      pgadmin4 () 
      {
        # Call the pgadmin4 executable.
        if [[ `type -t pgadmin4` = 'function' ]]; then
          if [ -f "/usr/pgadmin4/bin/pgadmin4" ]; then
            /usr/pgadmin4/bin/pgadmin4 2>/dev/null &
          else
            echo "[/usr/pgadmin4/bin/pgadmin4] is not found."
          fi
        else
          echo "[pgadmin4] is not a function"
        fi
      }

      You can launch your pgadmin4 program file now with the following syntax as the student user:

      pgadmin4

      It takes a couple moments to launch the pgadmin4 desktop. The initial screen will look like:

      After pgadmin4 launches, you’re prompted for a master password. Enter the password and click the OK button to proceed.

      After entering the password, you arrive at the base dialog, as shown.

      Click the Add New Server link, which prompts you to register your database. Enter videodb in the Name field and click the Connection tab to the right of the General tab.

      In the Connection dialog, enter the following values:

      • Host name/address: localhost
      • Port: 5432
      • Maintenance database: postgres
      • Username: student
      • Password: student

      Enter a name for your database. In this example, videodb is the Server Name. Click the Save button to proceed.

This completes the instructions for installing, configuring, and using PostgreSQL on AlmaLinux. As always, I hope it helps those looking for instructions.

Written by maclochlainn

November 24th, 2022 at 11:48 pm

PostgreSQL Trigger 1

without comments

This entry covers how to write a statement logging trigger for PostgreSQL. It creates two tables: avenger and avenger_log; one avenger_t1 trigger, and a testing INSERT statement.

It was written to help newbies know how and what to return from a function written for a statement-level trigger. They often get stuck on the following when they try to return true. The term non-composite is another way to describe the tuple inserted.

psql:basics_postgres.sql:
59: ERROR:  cannot return non-composite value from function returning composite type
CONTEXT:  PL/pgSQL function write_avenger_t1() line 15 at RETURN

The avenger table:

/* Conditionally drop table. */
DROP TABLE IF EXISTS avenger;
 
/* Create table. */
CREATE TABLE avenger
( avenger_id    SERIAL
, avenger_name  VARCHAR(30)
, first_name    VARCHAR(20)
, last_name     VARCHAR(20));

Seed the avenger table:

/* Seed the avenger table with data. */
INSERT INTO avenger
( first_name, last_name, avenger_name )
VALUES
 ('Anthony', 'Stark', 'Iron Man')
,('Thor', 'Odinson', 'God of Thunder')
,('Steven', 'Rogers', 'Captain America')
,('Bruce', 'Banner', 'Hulk')
,('Clinton', 'Barton', 'Hawkeye')
,('Natasha', 'Romanoff', 'Black Widow')
,('Peter', 'Parker', 'Spiderman')
,('Steven', 'Strange', 'Dr. Strange')
,('Scott', 'Lange', 'Ant-man');

The avenger_log table:

/* Conditionally drop table. */
DROP TABLE IF EXISTS avenger_log;
 
/* Create table. */
CREATE TABLE avenger_log
( avenger_log_id  SERIAL
, trigger_name    VARCHAR(30)
, trigger_timing  VARCHAR(6)
, trigger_event   VARCHAR(6)
, trigger_type    VARCHAR(12));

The INSERT statement that tests the trigger:

DROP FUNCTION IF EXISTS avenger_t1_function; 
CREATE FUNCTION avenger_t1_function()
  RETURNS TRIGGER AS
$$
BEGIN
  /* Insert a row into the avenger_log table.
   * Also, see PostrgreSQL 39.9 Trigger Procedures. */
  INSERT INTO avenger_log
  ( trigger_name
  , trigger_timing
  , trigger_event
  , trigger_type )
  VALUES
  ( UPPER(TG_NAME)
  , TG_WHEN
  , TG_OP
  , TG_LEVEL );
 
  /* A statement trigger doesn't use a composite type or tuple,
   * it should simply return an empty composite type or void. */
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

The avenger_t1 statement trigger:

CREATE TRIGGER avenger_t1
  BEFORE INSERT ON avenger
  EXECUTE FUNCTION avenger_t1_function();

The INSERT statement:

INSERT INTO avenger
( first_name, last_name, avenger_name )
VALUES
 ('Hope', 'van Dyne', 'Wasp');

The results logged to the avenger_log table from a query:

 avenger_log_id | trigger_name | trigger_timing | trigger_event | trigger_type
----------------+--------------+----------------+---------------+--------------
              1 | AVENGER_T1   | BEFORE         | INSERT        | STATEMENT
(1 row)

As always, I hope this helps those looking for a solution.

Written by maclochlainn

July 11th, 2022 at 3:12 pm

PL/pgSQL Transactions

without comments

There are many nuances that I show students about PL/pgSQL because first I teach them how to use PL/SQL. These are some of the differences:

  • PL/SQL declares the function or procedure and then uses the IS keyword; whereas, PL/pgSQL uses the AS keyword.
  • PL/SQL uses the RETURN keyword for functions declarations, like:

    RETURN [data_type} IS

    Whereas, PL/pgSQL uses the plural RETURNS keyword in the function declaration, like:

    RETURNS [data_type] AS
  • PL/SQL considers everything after the function or procedure header as the implicit declaration section; whereas, PL/pgSQL requires you block the code with something like $$ (double dollar symbols) and explicitly use the DECLARE keyword.
  • PL/SQL supports local functions (inside the DECLARE block of a function or procedure); whereas, PL/pgSQL doesn’t.
  • PL/SQL puts the variable modes (IN, INOUT, OUT) between the parameter name and type; whereas, PL/pgSQL puts them before the variable name.
  • PL/SQL declares cursors like:

    CURSOR cursor_name (parameter_list) IS

    Whereas, PL/pgSQL declares them like

    cursor_name CURSOR (parameter_list) FOR
  • PL/SQL terminates and runs the block by using an END keyword, an optional module name, a semicolon to terminate the END; statement, and a forward slash to dispatch the program to PL/SQL statement engine:

    END [module_name];
    /

    Whereas, PL/pgSQL terminates and runs the block by using an END keyword, a semicolon to terminate the END; statement, two dollar signs to end the PL/pgSQL block, and a semicolon to dispatch the program.

    END LANGUAGE plpgsql;
    $$;

After all that basic syntax discussion, we try to create a sample set of tables, a function, a procedure, and a test case in PL/pgSQL. They’ve already done a virtually equivalent set of tasks in PL/SQL.

Here are the steps:

  1. Create the grandma and tweetie_bird tables:

    /* Conditionally drop grandma table and grandma_s sequence. */
    DROP TABLE IF EXISTS grandma CASCADE;
     
    /* Create the table. */
    CREATE TABLE GRANDMA
    ( grandma_id     SERIAL
    , grandma_house  VARCHAR(30)  NOT NULL
    , PRIMARY KEY (grandma_id)
    );
     
    /* Conditionally drop a table and sequence. */
    DROP TABLE IF EXISTS tweetie_bird CASCADE;
     
    /* Create the table with primary and foreign key out-of-line constraints. */
    SELECT 'CREATE TABLE tweetie_bird' AS command;
    CREATE TABLE TWEETIE_BIRD
    ( tweetie_bird_id     SERIAL
    , tweetie_bird_house  VARCHAR(30)   NOT NULL
    , grandma_id          INTEGER       NOT NULL
    , PRIMARY KEY (tweetie_bird_id)
    , CONSTRAINT tweetie_bird_fk        FOREIGN KEY (grandma_id)
      REFERENCES grandma (grandma_id)
    );
  2. Create a get_grandma_id function that returns a number, which should be a valid primary key value from the grandma_id column of the grandma table.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    
    CREATE OR REPLACE
      FUNCTION get_grandma_id
      ( IN pv_grandma_house  VARCHAR ) RETURNS INTEGER AS
    $$
      /* Required for PL/pgSQL programs. */
      DECLARE
     
        /* Local return variable. */
        lv_retval  INTEGER := 0;  -- Default value is 0.
     
        /* Use a cursor, which will not raise an exception at runtime. */
        find_grandma_id CURSOR 
        ( cv_grandma_house  VARCHAR ) FOR
          SELECT grandma_id
          FROM   grandma
          WHERE  grandma_house = cv_grandma_house;
     
      BEGIN  
     
        /* Assign a value when a row exists. */
        FOR i IN find_grandma_id(pv_grandma_house) LOOP
          lv_retval := i.grandma_id;
        END LOOP;
     
        /* Return 0 when no row found and the ID # when row found. */
        RETURN lv_retval;
      END;
    $$ LANGUAGE plpgsql;
  3. Create a Warner_brother procedure that writes data across two tables as a transaction. You con’t include any of the following in your functions or procedures because all PostgreSQL PL/pgSQL functions and procedures are transaction by default:

    • SET TRANSACTION
    • START TRANSACTION
    • SAVEPOINT
    • COMMIT

    A ROLLBACK should be placed in your exception handler as qualified on lines #33 thru #36. The warner_brother procedure inserts rows into the grandma and tweetie_bird tables.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    
    /* Create or replace procedure warner_brother. */
    CREATE OR REPLACE
      PROCEDURE warner_brother
      ( pv_grandma_house       VARCHAR
      , pv_tweetie_bird_house  VARCHAR ) AS
    $$ 
      /* Required for PL/pgSQL programs. */
      DECLARE
     
      /* Declare a local variable for an existing grandma_id. */
      lv_grandma_id   INTEGER;
     
    BEGIN  
      /* Check for existing grandma row. */
      lv_grandma_id := get_grandma_id(pv_grandma_house);
      IF lv_grandma_id = 0 THEN 
        /* Insert grandma. */
        INSERT INTO grandma
        ( grandma_house )
        VALUES
        ( pv_grandma_house )
        RETURNING grandma_id INTO lv_grandma_id;
      END IF;
     
      /* Insert tweetie bird. */
      INSERT INTO tweetie_bird
      ( tweetie_bird_house 
      , grandma_id )
      VALUES
      ( pv_tweetie_bird_house
      , lv_grandma_id );
     
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK;
        RAISE NOTICE '[%] [%]', SQLERRM, SQLSTATE;  
    END;
    $$ LANGUAGE plpgsql;

    You should take note of the RETURNING-INTO statement on line #22. The alternative to this clause isn’t pretty if you know that PostgreSQL uses a table name, column name, and the literal seq value separated by underscores (that is, snake case), like:

        /* Assign current value to local variable. */
        lv_grandma_id := CURRVAL('grandma_grandma_id_seq');

    It would be even uglier if you had to look up the sequence name, like:

        /* Assign current value to local variable. */
        lv_grandma_id := CURRVAL(pg_get_serial_sequence('grandma','grandma_id'));
  4. You can test the combination of these two stored procedures with the following DO-block:

    /* Test the warner_brother procedure. */
    DO
    $$
    BEGIN
      /* Insert the yellow house. */
      CALL warner_brother( 'Yellow House', 'Cage');
      CALL warner_brother( 'Yellow House', 'Tree House');
     
      /* Insert the red house. */
      CALL warner_brother( 'Red House', 'Cage');
      CALL warner_brother( 'Red House', 'Tree House');
    END;
    $$ LANGUAGE plpgsql;

    Then, query the results:

    SELECT *
    FROM   grandma g INNER JOIN tweetie_bird tb
    ON.    g.grandma_id = tb.grandma_id;

    It should return:

     grandma_id | grandma_house | tweetie_bird_id | tweetie_bird_house | grandma_id
    ------------+---------------+-----------------+--------------------+------------
              1 | Red House     |               1 | Cage               |          1
              1 | Red House     |               2 | Tree House         |          1
              2 | Yellow House  |               3 | Cage               |          2
              2 | Yellow House  |               4 | Tree House         |          2
    (4 rows)
  5. As always, I hope writing a clear and simple examples helps those looking for sample code.

Written by maclochlainn

June 16th, 2022 at 9:38 pm