Blog Posts

Want to see your pgAdmin blog post here? Email webmaster@pgadmin.org.

pgAdmin User Survey 2022

Author: Dave Page, date: Aug. 2, 2022

On Monday 11th July the pgAdmin Development Team opened the first pgAdmin user survey which we then ran for three weeks, closing it on Monday 1st August. The aim of the survey was to help us understand how users are using pgAdmin to help us shape and focus our future development efforts. We had a fantastic response with 278 people taking the time to complete the survey - far exceeding our expectations. Responses were generally positive as well, with a number of people expressing their appreciation for the work of the development team, which is always nice to hear. In this blog post I'll go through the high level topics of the survey and attempt to summarise what has been reported, and draw some initial conclusions. If you would like to take a look at the results yourself...

Read more

Autocomplete feature in Query Tool

Author: Akshay Joshi, date: June 10, 2022

To use autocomplete, begin typing your query; when you would like the query editor to suggest object names or commands that might be next in your query, press the Control + Space key combination. For example, type "SELECT * FROM " (without quotes, but with a trailing space), and then press the Control + Space key combination to select from a popup menu of autocomplete options.

Read more

pgAdmin 4 Architecture

Author: Yogesh Mahajan, date: May 27, 2022

pgAdmin 4 is the leading Open Source management tool for Postgres. pgAdmin is designed to monitor and manage multiple PostgreSQL and EDB Advanced Server database servers, both local and remote, through a single graphical interface that allows the easy creation and management of database objects, as well as a number of other tools for managing your databases.

pgAdmin can be installed in two modes: Desktop & Server mode. Desktop mode is installed as a standalone application being used by the same operating system user while server mode can be accessed over the network, allowing it to be used by multiple users.

Both mode implementations follow a 3 tier architecture approach. This blog explains the pgAdmin architecture and technologies used in both modes.

Read more

Query Tool vs View/Edit Data

Author: Nikhil Mohite, date: May 10, 2022

Ever wondered what the differences are between the Query Tool and View/Edit Data tool in pgAdmin? In this blog Nikhil explains those differences and when each tool should be used.

Read more

How to Configure OAuth 2.0 with Azure AD in pgAdmin4

Author: Asmita Thapliyal, date: April 29, 2022

pgAdmin user Asmita Thapliyal writes:

I struggled for few days to get OAuth 2.0 configuration works for Azure AD in pgAdmin4, which leads me to writing this blog post.

We were looking for a hosted web interface for running SELECT queries in AWS Aurora database from on premise environment. This interface would allow developers for database interaction and to perform initial troubleshooting without sharing the database credentials and whitelisting the connection from their laptops and vms.

Read more

Use of the Master Password in pgAdmin 4

Author: Akshay Joshi, date: April 22, 2022

pgAdmin 4 introduced the Master Password in order to secure and later unlock saved server passwords. Any password that is saved in the SQLite DB file - used for storing user preferences and other data - through the save password or save SSH tunnel password option will be encrypted and decrypted using the master password.

The feature was introduced in pgAdmin 4 version 4.7 and above. This is applicable to Desktop mode users as well as to authentication methods like OAuth, Kerberos, or Web Server where pgAdmin doesn’t have access to anything long-lived to form the encryption key whereas with pgAdmin internal or LDAP authentication methods, the user’s login password is used as an encryption key.

Read more

pgAdmin 4 Browser Tree

Author: Khushboo Vashi, date: April 6, 2022

This blog from pgAdmin developer Khushboo Vashi discusses the technical changes made to the rendering of the treeview in pgAdmin.

Read more

Feature Test Automation in pgAdmin 4

Author: Yogesh Mahajan, date: Feb. 23, 2022

pgAdmin follows an Agile development process with an iterative and incremental approach to development. Testing is a critical part of the development process. pgAdmin is regularly tested with a subset of the available versions, ensuring a high-quality product. The test suite includes API testing, UI Testing (referred as Feature Testing), unit testing, and "resql" testing, a pgAdmin-specific test framework for testing the reverse engineering of SQL.

To achieve better quality in the short release cycles, test automation is required. The Selenium WebDriver with Python is used for feature test automation. Feature test frameworks currently support parallel testing/cross-browser testing. The new era of continuous testing requires faster test execution. Parallel testing involves running independent tests in parallel to reduce overall test execution time by allowing multiple OS/browser combinations to be tested simultaneously. An extended test coverage minimizes risk and reduces the chances of releasing a product with regressions or defects.

Read more

Dashboards in pgAdmin 4

Author: Nikhil Mohite, date: Feb. 3, 2022

The dashboard tab in pgAdmin shows an analysis of the activity statistics for the selected server or database.

It also shows quick links to Add New Server, Configure pgAdmin, and Documentation if a Server Group node is selected from the browser tree.

Read more

SSH Tunneling in pgAdmin 4

Author: Rahul Shirsat, date: Jan. 6, 2022

SSH tunneling is a method of transporting arbitrary networking data over an encrypted SSH connection. It can be used to add encryption to legacy applications or implement VPNs (Virtual Private Networks). It is a way to secure the data traffic of any given application using port forwarding, basically tunneling any TCP/IP port over SSH.

pgAdmin 4 supports SSH Tunneling, i.e. it - through an intermediary proxy host - can be connected to a PostgreSQL server that resides on a network to which the client may not be able to connect directly.

This blog will guide you to setup SSH tunneling with pgAdmin 4.

Read more

Shared Servers in pgAdmin 4

Author: Pradip Parkale, date: Jan. 4, 2022

Shared servers allow the admin user to share servers with other users so that they can use them directly without adding them themselves.

Shared servers only apply when pgAdmin is running in server mode. Server mode is a multi-user mode whereas Desktop mode is single user mode. In server mode users must login before they can use pgAdmin. An initial superuser account is created when server mode is initialised, and this user can add additional superusers and non-superusers as required.

Read more

Reverse Proxying to pgAdmin with uWSGI and NGINX

Author: Yogesh Mahajan, date: Dec. 16, 2021

pgAdmin 4 is a free open source graphical management tool for PostgreSQL and derivative relational databases such as EnterpriseDB's EDB Advanced Server. pgAdmin can be installed in two modes: Server mode and Desktop mode. pgAdmin in server mode can be deployed behind different web servers like apache2, Nnginx etc.

A reverse proxy is a server that sits in front of web servers and forwards client (e.g. web browser) requests to those web servers. Reverse proxies are typically implemented to help increase security and performance.

This blog will guide you to reverse proxy to pgAdmin using Nginx and uWSGI on Debian or Ubuntu Linux. The process is the same on other Linux distributions, but file, directory, and service names may differ.

Read more

How To Configure Webserver Authentication in pgAdmin 4

Author: Yogesh Mahajan, date: Nov. 30, 2021

pgAdmin 4 supports multiple authentication methods through its pluggable architecture. In addition to the four existing authentication methods; Kerberos, LDAP, OAuth 2.0, and internal, pgAdmin4 now supports webserver authentication.

Web server authentication (HTTP authentication) is the most common application of third-party authentication. With web server authentication, the web server performs the authentication and the application trusts the web server.

To enable web server authentication, the web server must be configured for any authentication mechanism (such as HTTP Basic auth or Shibboleth) which sets either headers or environment variables which will be used in pgAdmin to identify the user.

This blog will guide you to set up the apache2 webserver authentication with HTTP BASIC auth in pgAdmin 4, on Debian or Ubuntu Linux. The process is the same on other Linux distributions, but file, directory, and service names may differ.

Read more

How to Use Logical Replication in pgAdmin4

Author: Pradip Parkale, date: Oct. 7, 2021

Logical replication follows a publish and subscribe model. In a publisher node, a publication is created, which is a set of changes from a table or a group of tables. In a subscriber node, a subscription is created, which can subscribe to one or more publications. But why logical replication? Well in traditional replication, the standby server is a bit for bit copy of the leader server but what if you want to copy only some data? Logical replication gives you that flexibility of what should be sent and where to send the data.

Read more

pgAdmin and Porting the Node Properties Dialogs to ReactJS

Author: Aditya Toshniwal, date: Oct. 6, 2021

In a previous blog post, The Reasons Why pgAdmin Chose ReactJS, I had discussed the rationale behind choosing ReactJS for porting away from Backbone/jQuery. Following the post, I want to share the experience and course of actions EDB's pgAdmin team followed to achieve our goal. To date, the pgAdmin team has successfully ported all the properties dialog of the browser nodes to React based forms and has reached the testing phase.

Read more

How To Configure OAuth 2.0 in pgAdmin 4

Author: Khushboo Vashi, date: Sept. 30, 2021

pgAdmin 4 supports multiple authentication methods through its pluggable architecture. Currently four methods are supported:

  • Password based pgAdmin internal authentication (default)
  • Kerberos
  • LDAP
  • OAuth 2.0
We are also going to support authentication at web server level, ex: HTTP Basic Auth very soon, keep an eye on the pgAdmin release notes for more information. This blog will guide you to set up the OAuth 2.0 authentication in pgAdmin 4.

Read more

How to Get the Most Out of the Schema Diff Tool in pgAdmin 4

Author: Akshay Joshi, date: Sept. 6, 2021

The Schema Diff Tool in pgAdmin 4 allows you to compare objects between two databases or two schemas. It allows you to compare tables, views, functions, sequences, packages, procedures and other database objects between two schemas/databases. It will report any discrepancies between schemas such as missing or mismatching procedures, tables, triggers, columns, indexes and constraints. It also will detect column discrepancies such as data type, nullability and defaults.

Read more

How to Deploy pgAdmin in Kubernetes

Author: Dave Page, date: June 28, 2021

pgAdmin has long had a container distribution; however the development team rarely used it, except when testing releases. So virtually all of our experience has been using Docker. Recently, a user ran into an issue when running under Kubernetes that I was unable to reproduce in Docker, so I spent some time learning how a pgAdmin deployment would work in that environment—and ironically it worked just fine; I couldn't reproduce the bug! Regardless, I gained an understanding of how to deploy pgAdmin in Kubernetes, so here's how it works.

Read more

The Reasons Why pgAdmin Chose ReactJS

Author: Aditya Toshniwal, date: March 10, 2021

pgAdmin 4 is a management tool for PostgreSQL and EDB Postgres Advanced Server. pgAdmin 4 was re-written from pgAdmin 3 (a native desktop application). The application is designed to work on both the desktop and a web server. It is written in Python using Flask with the user interface written in JS/jQuery/Backbone, and can be deployed on any network. It is 2021, and one might argue about why pgAdmin chose jQuery/Backbone. The development work of pgAdmin 4 started in around 2014, and back then, Backbone.js was one of the most popular frameworks. Backbone.js was even more popular than React at the time.

Read more

Kerberos Support in pgAdmin 4

Author: Khushboo Vashi, date: March 1, 2021

The pgAdmin team has been receiving requests from users to support Kerberos Authentication for quite some time and as a result, we have decided to implement it. The work has been divided into 2 phases. The first phase adds pluggable Kerberos authentication in the pgAdmin Server (multi user) mode using GSSAPI and SPNEGO, similar to the existing LDAP support. The web browser and the pgAdmin web server negotiate Kerberos as a security mechanism through SPNEGO and exchange tickets as SPNEGO tokens over HTTPS and which will bypass the pgAdmin login page entirely if the Kerberos authentication succeeds.

Read more

Testing pgAdmin

Author: Dave Page, date: Aug. 25, 2020

Like any software, pgAdmin has bugs. At the time of writing there are 119 new or in-progress issues in the bug tracker, which is pretty good for a piece of software with so many moving parts, and regular-as-clockwork monthly releases. Over 10% of these issues are currently assigned to the reporter to gather further information, plus there will undoubtedly be some duplicates and support issues/feature requests in that number that haven’t yet been picked up in our weekly backlog refinement meeting and either closed or moved to the feature or support trackers.

Read more

pgAdmin, a comparable tool to PL/SQL Developer for PostgreSQL

Author: Shivam Dhapatkar, date: Feb. 9, 2020

This article explains how to get started using pgAdmin 4 as a management tool for PostgreSQL.

  1. How to download pgAdmin 4
  2. How to get started with pgAdmin 4
  3. Query Tool
  4. How to execute a query in pgAdmin 4
  5. Creating functions, procedures, and triggers using Query Tool

Read more

Reverse Proxying to pgAdmin

Author: Dave Page, date: July 10, 2019

Reverse proxying requests to a pgAdmin server is becoming more and more popular if posts to the mailing lists are to be taken as an indicative measure; more often than not when using pgAdmin in a container (of which there have now been over 10 million pulls)! Typically users will deploy a reverse proxy for a couple of reasons; to host multiple applications in different subdirectories under the same domain, or to add SSL/TLS support independently of the application. Because of the number of questions asked, I spent a little time over the last couple of days doing some testing and updating the documentation with some examples. Here's a blog-ified version of that work.

Read more