MacLochlainns Weblog

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';

  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;

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

  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)

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