In this article, we will show how to connect to instances of Azure Database for PostgreSQL servers using the pgAdmin tool installed locally on a user’s workstation.
Introduction
Every type of database has a community of data tools that facilitates management and operations on the database instances. For example, one of the most popular management and operational tool for SQL Server is SQL Server Management Studio (SSMS). Almost every type of user persona connects to SQL Server instances on-premises or on the cloud using SSMS to perform operations suited for their role. Likewise, the PostgreSQL database is one such open-source database type and one of the most popular management tools to operate on this database is pgAdmin. Azure’s offering of PostgreSQL database on the Azure platform is Azure Database for PostgreSQL server. But there’s no specific tooling offering from Azure to operate on this database. One of the reasons possibly is since the most popular tool that is already used by PostgreSQL users for on-premise or cloud-hosted deployment is pgAdmin, users would prefer to continue using the same familiar tool to operate on instances of Azure Database for PostgreSQL on the Azure platform too. Let’s go ahead and see how to connect to instances of Azure Database for PostgreSQL using the pgAdmin tool.
Pre-requisite
We intend to connect to an instance of Azure Database for PostgreSQL using pgAdmin, so the obvious pre-requisite to perform this exercise is creating an instance of the database as well as installing pgAdmin on a local workstation. It is assumed that one has access to an Azure account with the required privileges to administer Azure Database for PostgreSQL server service. Navigate to this service on the Azure portal and create an instance of the database using the Single Server deployment model. Though this method of connecting would work with other modes of deployment as well, for simplicity purpose and to avoid and complexities in connecting to the database instance that may arise from using a different mode of deployment, we are using the Single Server mode for creating a new instance of Azure Database for PostgreSQL. Once the instance is created, you would be able to explore and administer this instance from the dashboard page of the instance, as shown below.
The next part of our pre-requisite is installing the pgAdmin tool. As of the draft of this article, the latest version of this tool is pgAdmin 4. This tool can be freely installed on the local machine from here. As shown below, one can select the version of the tool based on the Operating System or platform on which the tool is expected to be installed. In this article, we would be using the version of the tool that is installed on a Windows operating system.
Connecting to Azure Database for PostgreSQL with pgAdmin 4
After pgAdmin 4 is installed, it now time to connect to the Azure Database for the Postgres instance that we created earlier. Once pgAdmin 4 is installed, open the same and it would look as shown below. When the tool is opened the very first time, it will prompt to configure a master password, since this tool can save connections and credentials that may readily connect to a database instance. Generally, administrators use this tool, so unauthorized users who may attempt to access this tool can get undesired access to the database instance using the administrator’s credential (if the same is saved in the tool). Setting up a master password provides one level of defense against such a situation. Set up this password and then you would be able to access the home layout of this tool.
Click on the Add New Server option from the quick links as shown above, which would bring up a dialog box as shown below. To access the Azure Database for the Postgres instance, we need to register the instance first. In the General tab, we need to provide a name for the server, and we can make it part of any logical group, which allows administrators to administer multiple database instances as a group.
In the Connection tab, we need to provide the hostname or address. This is the server name of the instance that we created earlier, as shown below. The default port of Azure Database for PostgreSQL server is 5432. The default maintenance database is Postgres. Fill up these details as shown below. Provide the administrator credentials that you would have configured while creating the database instance.
Open the SSL tab and you would find that the SSL mode is set to a value of “Prefer”, which means that it is not mandated. This would mean that one can open connections from a local workstation to the Azure Database for PostgreSQL instance hosted on Azure cloud, which are not encrypted by SSL, which are not secured and hence not recommended. Change the value of SSL mode to “Require”. Typically, in production environments, one may also have certificates set up to establish the unique authenticity of the database. In those cases, one can configure the certificate-related settings too. Once done, click on the Save button.
Once we click on the Save button, pgAdmin 4 would immediately attempt to connect to the database instance and it would fail with an error as shown below. The reason is that we did not specify the username in the format expected by pgAdmin 4, which is username@hostname format. Click Ok and navigate to the Connection tab.
Change the format of the username as shown below. Sqladmin is the username in our case and the portion after “@” is the hostname, which can be easily found from the dashboard page of the database instance.
After changing the database username format, click on the Save button, and that would re-initiate connection to the instance. The connection would again fail with an error, as shown below. The reason for the same is that we have not configured the firewall settings of the Azure Database for the Postgres instance to allow incoming connections from the workstation on which pgAdmin 4 is installed.
Navigate to the dashboard of the database instance and click on the Connection Security menu item, and you should be able to see a screen as shown below. Click on the Add current client IP address to add the IP of the workstation from which we are trying to initiate a connection. Once the IP has been added, click on the Save button to save the configuration so that it can come into effect.
Once the configuration has been saved, switch back to pgAdmin 4 and attempt the save the server configuration. This time the server should get added to the pgAdmin console and the connection should be successful. Once connected, the server would get listed on the browser pane as shown below.
By default, the database to which pgAdmin connects is the Postgres database. The dashboard would instantly start showing the activity on the server in the different charts as shown above. On the server activity pane at the bottom, it would list all the connections from various client tools. Click on the properties tab and select the server name as shown below. It would show that we are connected to the database instance. It would also list all the configuration details that are in effect to connect to the Azure Database for PostgreSQL instance. If you carefully analyze the properties, you would also find that SSL mode is set to Require, which means our connection is securely encrypted with SSL.
In this way, we can use the pgAdmin 4 utility to connect to Azure Database for the PostgreSQL instance on the Azure cloud.
Conclusion
In this article, we started with an instance of Azure Database for PostgreSQL in Single Server deployment mode. We installed the pgAdmin 4 utility on a local workstation and navigated through the various configuration errors that one may encounter to connect to the database instance. We learned the configuration required to securely connect to the database instance and successfully connected to the database instance.
Table of contents
| Accessing Azure Database for PostgreSQL using pgAdmin |
| Azure Database for PostgreSQL Cost Optimization |
| Getting started with Azure Database for PostgreSQL |
She has a deep experience in designing data and analytics solutions and ensuring its stability, reliability, and performance. She is also certified in SQL Server and have passed certifications like 70-463: Implementing Data Warehouses with Microsoft SQL Server.
View all posts by Gauri Mahajan
- Introduction to the SQL Standard Deviation function - April 21, 2023
- A quick overview of MySQL foreign key with examples - February 7, 2023
- Overview of the SQL Median function - January 4, 2023