PostgreSQL 15 | MacLochlainns Weblog

Archive for the ‘PostgreSQL 15’ Category

PostgreSQL User Password

Miraculous events happen on my students’ computers almost weekly. For example, one couldn’t remember their student user’s password, which I set by default. How did they change it? They don’t recall.

Assuming you have sudoer authority on a Linux operating system, you can change a PostgreSQL user’s password as follows:

Assume the root user’s role with the following command:
sudo sh
sudo sh
As the root user, assume the postgres user’s role with the following command:
su - postgres
su - postgres
As the postgres user, connect to the PostgreSQL database with the following command:
psql -U postgres
psql -U postgres
Assuming the user’s name is student and you want to set the password to a trivial value like student, use the following command:
ALTER USER student WITH ENCRYPTED PASSWORD 'student';
ALTER USER student WITH ENCRYPTED PASSWORD 'student';

As always, I hope this helps somebody trying to sort out the syntax and workflow.

Written by maclochlainn

November 7th, 2023 at 9:41 pm

Posted in AlmaLinux,PostgreSQL,PostgreSQL 15,Ubuntu

Tagged with PostgreSQL DBA

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

Posted in PL/pgSQL,Postgres,PostgreSQL,PostgreSQL 15

Tagged with PostgreSQL DBA, PostgreSQL Developer

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.

// 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

Posted in Java,Java 17,Linux,Postgres,PostgreSQL,PostgreSQL 15

Tagged with Java Development, PostgreSQL Developer

GROUP BY Quirk

without comments

It’s always interesting to see how others teach SQL courses. It can be revealing as to whether they understand SQL or only understand a dialect of SQL. In this case, one of my old students was taking a graduate course in SQL and the teacher was using MySQL. The teacher made an issue of using ANSI SQL:1999 or SQL3 and asked the following question, which I suspect is a quiz bank question from a textbook:

“How would you get all students’ names and for each student the number of courses that the
student has registered for?”

They referenced the MySQL 5.7 documentation for the GROUP BY and SQL:1999 as if MySQL implemented the ANSI SQL:1999 specification defined the standard. I didn’t know whether to laugh or cry because they were referring to MySQL 5.7 when we’re all using MySQL 8 and anybody who’s worked in more than MySQL knows that the behavior for a GROUP BY in MySQL can work without listing the necessary non-aggregated columns in the SELECT-list.

For example, their working solution, which is from the instructor and the author of their MySQL textbook the correct perspective of ANSI:1999 behavior. It doesn’t matter that their solution is actually based on ANSI:1992 not ANSI:1999 because it will only succeed because of a quirk of MySQL:

SELECT   a.studentname
,        COUNT(b.courseid)
FROM     students a INNER JOIN registeredcourses b
ON       a.studentid = b.studentid
GROUP BY a.studentid;

While it works in MySQL, it doesn’t work because it conforms to an ANSI standard. It works in MySQL, notwithstanding that standard because it violates the standard.

In Oracle, PostgreSQL, and SQL Server, it raises an exception. For example, Oracle raises the following exception:

SELECT   a.studentname
         *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

The correct way to write the GROUP BY is:

SELECT   a.studentname
,        COUNT(b.courseid)
FROM     students a INNER JOIN registeredcourses b
ON       a.studentid = b.studentid INNER JOIN courses c
ON       b.courseid = c.courseid
GROUP BY a.studentname;

Then, it would return:

Student Name                   Course IDs
------------------------------ ----------
Montgomery Scott                        1
Leonard McCoy                           2
James Tiberus Kirk                      3

For reference, here’s a complete test case for MySQL:

/* Drop table conditionally. */
DROP TABLE IF EXISTS students;
 
/* Create table. */
CREATE TABLE students
( studentID    int unsigned primary key auto_increment
, studentName  varchar(30));
 
/* Drop table conditionally. */
DROP TABLE IF EXISTS courses;
 
/* Create table. */
CREATE TABLE courses
( courseid    int unsigned primary key auto_increment
, coursename  varchar(40));
 
/* Drop table conditionally. */
DROP TABLE IF EXISTS registeredcourses;
 
/* Create table. */
CREATE TABLE registeredcourses
( courseid    int unsigned
, studentid   int unsigned );
 
/* Insert into students. */
INSERT INTO students
( studentName )
VALUES
 ('James Tiberus Kirk')
,('Leonard McCoy')
,('Montgomery Scott');
 
/* Insert into courses. */
INSERT INTO courses
( coursename )
VALUES
 ('English Literature')
,('Physics')
,('English Composition')
,('Botany')
,('Mechanical Engineering');
 
/* Insert into registeredcourses. */
INSERT INTO registeredcourses
( studentid
, courseid )
VALUES
 (1,1)
,(1,3)
,(1,4)
,(2,2)
,(2,5)
,(3,4); 
 
/* Check global sql_mode to ensure only_full_group_by is set. */
SELECT @@GLOBAL.SQL_MODE;
 
/* Query with a column not found in the SELECT-list. */
SELECT   a.studentname
,        COUNT(b.courseid)
FROM     students a INNER JOIN registeredcourses b
ON       a.studentid = b.studentid
GROUP BY a.studentid;
 
/* Query consistent with ANSI SQL:1992 */
SELECT   a.studentname
,        COUNT(b.courseid)
FROM     students a INNER JOIN registeredcourses b
ON       a.studentid = b.studentid INNER JOIN courses c
ON       b.courseid = c.courseid
GROUP BY a.studentname;

and, another complete test case for Oracle:

/* Drop tabhe unconditionallly. */
DROP TABLE students;
 
/* Create table. */
CREATE TABLE students
( studentID    NUMBER PRIMARY KEY
, studentName  VARCHAR(30));
 
/* Drop table unconditionally. */
DROP TABLE courses;
 
/* Create table. */
CREATE TABLE courses
( courseid    NUMBER PRIMARY KEY
, coursename  VARCHAR(40));
 
/* Drop table unconditionally. */
DROP TABLE registeredcourses;
 
/* Create table. */
CREATE TABLE registeredcourses
( courseid    NUMBER
, studentid   NUMBER );
 
/* Insert values in student. */
INSERT INTO students ( studentid, studentName ) VALUES (1,'James Tiberus Kirk');
INSERT INTO students ( studentid, studentName ) VALUES (2,'Leonard McCoy');
INSERT INTO students ( studentid, studentName ) VALUES (3,'Montgomery Scott');
 
/* Insert values in courses. */
INSERT INTO courses ( courseid, coursename ) VALUES (1,'English Literature');
INSERT INTO courses ( courseid, coursename ) VALUES (2,'Physics');
INSERT INTO courses ( courseid, coursename ) VALUES (3,'English Composition');
INSERT INTO courses ( courseid, coursename ) VALUES (4,'Botany');
INSERT INTO courses ( courseid, coursename ) VALUES (5,'Mechanical Engineering');
 
/* Insert values into registeredcourses. */
INSERT INTO registeredcourses ( studentid, courseid ) VALUES (1,1);
INSERT INTO registeredcourses ( studentid, courseid ) VALUES (1,3);
INSERT INTO registeredcourses ( studentid, courseid ) VALUES (1,4);
INSERT INTO registeredcourses ( studentid, courseid ) VALUES (2,2);
INSERT INTO registeredcourses ( studentid, courseid ) VALUES (2,5);
INSERT INTO registeredcourses ( studentid, courseid ) VALUES (3,4); 
 
/* Non-ANSI SQL GROUP BY statement. */
SELECT   a.studentname
,        COUNT(b.courseid)
FROM     students a INNER JOIN registeredcourses b
ON       a.studentid = b.studentid
GROUP BY a.studentid;
 
/* ANSI SQL GROUP BY statement. */
SELECT   a.studentname AS "Student Name"
,        COUNT(b.courseid) AS "Course IDs"
FROM     students a INNER JOIN registeredcourses b
ON       a.studentid = b.studentid INNER JOIN courses c
ON       b.courseid = c.courseid
GROUP BY a.studentname;

/* Drop tabhe unconditionallly. */ DROP TABLE students; /* Create table. */ CREATE TABLE students ( studentID number primary key , studentName varchar(30)); /* Drop table unconditionally. */ DROP TABLE courses; /* Create table. */ CREATE TABLE courses ( courseid number primary key , coursename varchar(40)); /* Drop table unconditionally. */ DROP TABLE registeredcourses; /* Create table. */ CREATE TABLE registeredcourses ( courseid number , studentid number ); /* Insert values in student. */ INSERT INTO students ( studentid, studentName ) VALUES (1,'James Tiberus Kirk'); INSERT INTO students ( studentid, studentName ) VALUES (2,'Leonard McCoy'); INSERT INTO students ( studentid, studentName ) VALUES (3,'Montgomery Scott'); /* Insert values in courses. */ INSERT INTO courses ( courseid, coursename ) VALUES (1,'English Literature'); INSERT INTO courses ( courseid, coursename ) VALUES (2,'Physics'); INSERT INTO courses ( courseid, coursename ) VALUES (3,'English Composition'); INSERT INTO courses ( courseid, coursename ) VALUES (4,'Botany'); INSERT INTO courses ( courseid, coursename ) VALUES (5,'Mechanical Engineering'); /* Insert values into registeredcourses. */ INSERT INTO registeredcourses ( studentid, courseid ) VALUES (1,1); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (1,3); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (1,4); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (2,2); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (2,5); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (3,4); /* Non-ANSI SQL GROUP BY statement. */ SELECT a.studentname , COUNT(b.courseid) FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid GROUP BY a.studentid; /* ANSI SQL GROUP BY statement. */ SELECT a.studentname AS "Student Name" , COUNT(b.courseid) AS "Course IDs" FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid INNER JOIN courses c ON b.courseid = c.courseid GROUP BY a.studentname;

I hope this helps those learning the correct way to write SQL.

Written by maclochlainn

January 12th, 2023 at 11:30 pm

Posted in MySQL,MySQL 8,Oracle,Oracle 18c,Oracle 21c,PostgreSQL,PostgreSQL 15,SQL Server,SQL Server 2014

Tagged with MySQL DBA, MySQL Developer, Oracle DBA, Oracle Developer

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

Posted in Postgres,PostgreSQL,PostgreSQL 14,PostgreSQL 15

Tagged with PostgreSQL DBA, PostgreSQL Developer

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.

Written by maclochlainn

December 21st, 2022 at 5:29 pm

MacLochlainns Weblog

Archive for the ‘PostgreSQL 15’ Category

PostgreSQL User Password

PL/pgSQL Test Q?

PostgreSQL Java

GROUP BY Quirk

TDE on PostgreSQL

AlmaLinux Install & Configuration

Recent Posts

Things Written About

Pages

Blogroll

Archives