pgAdmin 4 Ubuntu

Introduction to pgAdmin 4

pgAdmin 4 is the dominant tool of open-source management for Postgres. pgAdmin is developed to manage and monitor multiple EDB Postgres Advanced Server and PostgreSQL database servers, both remote and local, using one graphical interface that permits the easy management and creation of database objects and several other tools to manage our database.

pgAdmin can be downloaded in two modes: Server and Desktop mode. Desktop mode is downloaded as a standalone app being utilized by the same OS user, while server mode can be used on the network, permitting it to be used by more than one user. The implementations of both modes follow a 3-tier architecture method.

• Pre-configured and pre-compiled installation packages are available for several desktop platforms for pgAdmin 4.
• The pgAdmin app is deployed with the WSGI interface or behind a web server in a Server Deployment.
• We will be promoted to give a pgAdmin password and role name when we initially link to pgAdmin if we install pgAdmin in a server mode.
• The pgAdmin app is configured to utilize the desktop runtime environment for hosting the program on any supported platform in a Desktop Deployment.
• Users will typically install a pre-built package to execute pgAdmin in a desktop mode.
• However, a manual desktop deployment can be installed, and it is much harder to set up.
• It may be helpful for developers concerned about knowing how pgAdmin works.

Functions in pgAdmin 4

Login Dialog

We can use the Login Dialog for logging in to pgAdmin. We can use many fields inside the Login Dialog to access our connections. There are two different ways to access our connection:

• From the 4.21 version of pgAdmin onwards, supports LDAP authentication has been included. If LDAP authentication has been started for our pgAdmin app, we can use our LDAP credentials for logging into pgAdmin:
  • Give the LDAP username inside the Username/Email Address field.
  • Give the LDAP password inside the Password field.
• We can use the below information to alternatively log into pgAdmin:
  • Give the email address related to our account inside the Username/Email Address field.
  • Give the password inside the Password field.
  • Select the Login button to login to pgAdmin securely.

Note: We will be logged out if the pgAdmin server is rebooted. We need to login again to continue.

Recover a lost password

If we can't supply our password, select the 'Forgotten your password' option to launch the password recovery utility.

• Give the email address related to our account inside the Email Address field.
• Select the Recover Password option to start recovery. An email will be sent to the email address entered inside the Email Address field with directions on how we can reset a password.

Enabling 2FA

Two-factor authentication is an additional security layer used when logging into applications of websites. We need to login with our password and username and give another authentication form that only we know or can authenticate with 2FA.

User management dialog

A password is generated randomly and then avoided when invoking pgAdmin in a desktop mode. We will be asked for an administrator password and email for the pgAdmin client if we install pgAdmin in a server mode.

When we access pgAdmin, the server definitions related to that login role are present in the tree control. The administrative users can utilize the User Management dialog to:

• Handle the password related to a role
• Assign privileges
• Add or remove pgAdmin roles.

Modify user password dialog

It's a good policy to modify our passwords routinely to protect our data, even in what we may consider a protected environment. Failure to use a proper password policy can leave us in breach of data protection laws in the workplace. We can consider the below guidelines when choosing our password:

• Ensure that our password is a proper length; 6 characters must be the absolute minimum characters for the password.
• Ensure that our password isn't open to a dictionary attack. We can use a combination of lower and uppercase letters and numerics and ignore names or words. Consider using the initial letter from all words in a phrase that we will easily remember but is an unusual acronym.
• Ensure that our password is regularly changed.

Setup LDAP Authentication

We must construct the LDAP settings inside the config_system.py or config_local.py file on the system in which pgAdmin is already installed in server mode to set up LDAP authentication. We can also copy the settings through the config.py file and change the values for the below parameters.

There are three ways to construct LDAP:

• Dedicated user bind
• Anonymous bind
• Bind as pgAdmin user

• AUTHENTICATION_SOURCES
  This parameter has, by default, the internal value. We must add LDAP to the value list for this parameter to set up LDAP authentication.
• LDAP_AUTO_CREATE_USER
  It specifies if we wish to automatically make a pgAdmin user related to the LDAP user credentials.

Note: The password of LDAP is not saved in the pgAdmin database.

• LDAP_CONNECTION_TIMEOUT
  It describes the connection timeout for LDAP authentication.

Setup Kerberos Authentication

We must construct the Kerberos settings inside the config_system.py or config_local.py file on the system in which pgAdmin is already installed in server mode to set up Kerberos authentication. We can also copy the settings through the config.py file and change the values for the below parameters.

• AUTHENTICATION_SOURCES
  This parameter has, by default, the internal value. We must add kerberos to the value list for this parameter to set up Kerberos authentication.
• KERBEROS_AUTO_CREATE_USER
  It sets the 'True' value if we wish to automatically make a pgAdmin user related to an authenticated Kerberos user.

Note: The password is not saved in the pgAdmin database.

• KRB_APP_HOST_NAME
  It specifies the title of the pgAdmin webserver hostname.

Note: It will grab the default_server parameter value if it's not set.

Setup OAUTH2 Authentication

We must construct the OAUTH2 settings inside the config_system.py or config_local.py file on the system in which pgAdmin is already installed in server mode to set up LDAP authentication. We can also copy the settings through the config.py file and change the values for the below parameters.

• AUTHENTICATION_SOURCES
  This parameter has, by default, the internal value. We must add oauth2 to the value list for this parameter to set up OAUTH2 authentication.
• OAUTH2_NAME
  It specifies the Oauth2 provider name, e.g., GitHub, Gothe ogle
• OAUTH2_DISPLAY_NAME
  pgAdmin is the name of Oauth2 display.
• OAUTH2_CLIENT_ID
  It specifies the Client ID of Oauth2.
• OAUTH2_CLIENT_SECRET
  It specifies the Client Secret of Oauth2.
• OAUTH2_TOKEN_URL
  It specifies the Access Token endpoint of Oauth2.

Setup Webserver Authentication

We must setup our webserver using any authentication plug-in (like HTTP BASIC and Shibboleth auth) as long as it fixes the variable, i.e., REMOTE_USER environment.

We must construct our Webserver settings inside the config_system.py or config_local.py file on the system in which pgAdmin is already installed in server mode to set up Webserver authentication. We can also copy the settings through the config.py file and change the values for the below parameters.

• AUTHENTICATION_SOURCES
  This parameter has, by default, the internal value. We must add webserver to the value list for this parameter to set up Webserver authentication.
• WEBSERVER_AUTO_CREATE_USER
  It sets the 'True' value if we wish to automatically make a pgAdmin user related to an authenticated Webserver user.

Note: The password is not saved in the pgAdmin database.

• WEBSERVER_REMOTE_USER
  To get the details of a remote webserver user, set this variable to environment or header variable name, which is received from the webserver. By default, it has the REMOTE_USER value, and its possible values are X-Forwarded-User, HTTP_X_FORWARDED_USER, and REMOTE_USER.

User Interface

pgAdmin 4 supports every feature of PostgreSQL, from writing basic SQL queries to integrating typical databases. It is developed to query a database, permitting us to stay current with implementations and modifications.

Features

• Online information and help about using pgAdmin tools and dialogs
• Helpful hints
• Supportive error messages
• Context-sensitive and responsive behavior
• Powerful management tools and dialogs for common tasks
• Syntax-highlighting SQL editor
• Supports administrative queries
• An active query tool along with direct data editing
• Support and auto-detection for objects found at run-time.

Menu Bar

The menu bar of pgAdmin offers drop-down menus to access the utilities, commands, and options. The menu bar shows the below sections:

• Help
• Tools
• File
• Object

Selections might be greyed out, which means they're disabled for the recently chosen object in the pgAdmin tree control.

Toolbar

It offers shortcut buttons for mostly used features such as Query Tool and View Data which are mostly used in pgAdmin. It is clear on the Browser panel, and buttons get disabled/enabled based on the chosen browser mode.

• We can use the button, i.e., Query Tool, to open it in the database context (current).
• We can use the button, i.e., View Data, to edit/view the data stored in a chosen table.
• We can use the button, i.e., Filtered Rows, to use the Data Filter popup to access a filter to a group of data for editing/viewing.
• We can use the button, i.e., Search Objects, to use the search object dialogs. It will help us find any database object.
• We can use the button, i.e., PSQL Tool, to start the PSQL in the database context (current).

Tabbed Browser

The pgAdmin window's right pane offers a group of tabs that show information about the currently selected object in the pgAdmin tree control inside the left window. We can choose a tab to use the information of the highlighted object inside the tree control.

The Statistics tab shows the statistics gathered for all objects in the tree control; the below statistics vary by the selected object type. A few of the statistics available are as follows:

• PID: It is the process ID related to the row.
• Database: It shows the database name.
• User: The user name that owns the project.
• Backends: It shows the number of connections to the database currently.
• Backend start: It displays the start period of the backend process.
• Size: It shows the size (in MB) of the chosen database.

Tree Control

The main Window's left pane shows a tree control that offers access to the objects that position on a server. We can expand nodes inside the tree control to see the database objects that position on a selected server. This tree control expands to show a hierarchical view:

• Press the minus (-) sign to the node's left side to close a node.
• Use the plus (+) sign to the node's left side to expand a part of the tree control.

Also, we can drag and drop many objects for the Query Tool, saving time in entering long names of the objects. Text having the object name will be completely qualified with schema. If required, double quotes will be included. For procedures and functions, the function name with parameter names will be fixed in the query tool.

Preferences Dialog

On the Preferences Dialog, we can use many options to customize the client's behavior. We need to select Preferences through the File menu to open it. The Preferences Dialog's left pane shows a tree control; all nodes of the tree control offer access to options that correspond to the node in which they are shown.

• Press the minus (-) sign to the node name left side to close a node.
• Use the plus (+) sign to the node name left side to expand a part of the tree control.

Keyboard Shortcuts

Keyboard shortcuts are offered in pgAdmin to permit easy access to particular functions. Alternate shortcuts could be configured from File > Preferences if required.

• Main Browser Window
  Below keyboard shortcuts are present if we use the main browser window:
  • Alt+Shift+F: It opens the File menu.
  • Alt+Shift+L: It opens the Tools menu.
  • Alt+Shift+O: It opens the Object menu.
  • Alt+Shift+B: It concentrates on the browser tree.
  • Alt+Shift+H: It opens the Help menu.
  • Alt+Shift+C: It opens the context menu.
• Dialog Tabs
  We can use the following shortcuts to operate the tabsets in dialogs:
  • Control+Shift+[: It is used for the dialog tab backward.
  • Control+Shift+]: It is used for the dialog tab forward.

Search Objects

We can find almost every kind of object inside a database using this dialog. We can use it by right-clicking on any database or any of its children nodes and choosing "Select objects". Also, we can use it by entering the shortcut (ALT+SHIFT+S).

The output is shown in the grid with the object tree path, object type, and object name in the browser. We can double-click a result row to choose the object inside the browser.

It means that we have not activated those object types if the object is greyed out in the preferences, so we cannot double-click it. We can choose the ellipses appended to the procedure and function names to check their arguments. We can filter based on a specific object type by choosing from the dropdown of the object type.

When an object type is chosen, only those types will be obtained from the database if the search button is clicked. An object type will not be clear in the dropdown when the database server doesn't support it or if it's not activated from the preferences.

Handling Cluster Objects

A few object definitions remain on the cluster level; pgAdmin 4 offers dialogs that permit us to make the objects, handle them, and control their connections to each other. To use a dialog that permits us to make a database object, we need to right-click on the object type inside the pgAdmin tree control, and choose the option, i.e., Create for that object.

Handling Database Objects

pgAdmin 4 offers simple but strong dialogs that we can use to create and design database objects. All dialogs contain a tab series that we use to define the object that will be made by the dialog; the SQL tab shows the SQL command that the server will run when making the object.

To access any dialog that permits us to establish a database object, we need to right-click on the object type inside the pgAdmin tree control, and choose the option, i.e., Create for that object.

Creating or Changing a Table

pgAdmin 4 offers dialogs that permit us to change every table attribute and property. To access any dialog that permits us to establish a database object, we need to right-click on the object type inside the pgAdmin tree control, and choose the option, i.e., Create for that object.

Management Basics

pgAdmin offers the point & click dialogs that support us in performing many functions of server management. Dialogs simplify operations, such as granting user privileges, handling named restore points, and performing REINDEX, ANALYZE, and VACCUM functions.

Backup and Restore

A strong but convenient Backup and Restore tool offers a simple way to use pg_restore, pg_dumpall, and pg_dump to take backups and make database objects or database copies for use in the development environments.

Developer Tools

The pgAdmin menu, i.e., Tools, shows a list of strong developer tools that we can use to run and analyze typical SQL commands, debug SQL/PL code, and manage data.

• Debugger
  • Using Debugger
  • Direct Debugging
  • In-context Debugging
• Edit/View Data
  • Filter/Sort options dialog
  • Data Grid
  • View/Edit Data Toolbar
• Query Tool
  • Macros
  • Change connection
  • Connection status
  • Query history panel
  • Notifications panel
  • Messages panel
  • Explain panel
  • Data output panel
  • SQL Editor panel
  • Toolbar

Installing pgAdmin 4 in Ubuntu

pgAdmin is a famous open-source development and management tool which is published under the Artistic/PostgreSQL license. Different database type options can be easily done from the browser with this software. It can be used on almost every popular operating system.

pgAdmin 4 is currently published, and it is developed using JQuery and Python. Several improvements are implemented in pgAdmin 4 than in pgAdmin 3. Anyone can download the binary or source file of this software based on a specific OS for installation.

The user will be needed to have the technical knowledge to download pgAdmin by compiling the source file so that the installation processes are only for advanced users. From a binary file, installing pgAdmin is a great way for new users. In this article, we will explain how to download and use pgAdmin in Ubuntu.

Steps for installing pgAdmin 4

• Updating the system
  Before beginning the installation process, we need to update our system by running the below command:

• Installing needed packages
  Before installing pgAdmin, three packages need to install, which are virtualenv, pip, and python. Execute the below command for installing these packages:

We need to press 'y' to finish the installation process of the needed packages when the below prompt occurs:

• Establish virtual environment
  Execute the below commands to establish a new folder called pgAdmin 4 in the recent location, visit the newly established folder and make the virtual environment.

• Enable virtual environment
  We will visit the pgAdmin4 folder in pgAdmin4 and execute the below commands to enable the virtual environment:

• Downloading pgAdmin 4
  We will execute the below command for downloading the latest pgAdmin 4 version:

After finishing the download process of pgAdmin, the terminal will appear like the below image:

• Installing pgAdmin 4
  Execute the below command to finish the pgAdmin 4 installation process:

After the installation process, the below screen will occur: