How to Manage PostgreSQL Databases

Some of the features discussed in this article are in beta testing and will be deployed very soon. If you do not yet have PostgreSQL in N0C, please refer to the article How to manage databases with Maria DB.

Introduction

The MG panel provides direct access to interfaces for PostgreSQL website database management. You can create and delete databases, and much more.

In what follows, we will confine ourselves to information on using the PostgreSQL interfaces for beginner users.

If you are working with a PostgreSQL database (usable with phpPgAdmin), the hostname to specify is 127.0.0.1.

Note : The Glossary contains explanations on multiple topics and can be consulted to clarify certain terms.

Prerequisites

Access to https://mg.n0c.com/en/.

Concepts to be Known

Details on Databases Users Privileges

In the case of MySQL, specific privileges can be granted via the interface, which is not the case with PostgreSQL. This is why we only offer the possibility of associating a user with the PostgreSQL database.

Icons for Managing Passwords

When you have to create or change passwords, there are some icons that will be very useful. The table below explains their meaning.

Access to PostgreSQL Interfaces

To open PostgreSQL, choose Databases -> PostgreSQL in the contextual menu:

These interfaces are very similar. In both cases, you will notice that CURRENT DATABASES are displayed at the top of the window and CURRENT USERS are listed at the bottom.

Database Creation

Create a PostgreSQL Database with a New User

1. Access to PostgreSQL databases interface and click on the Create button:

2. Fill-in the information required in the Database and User Creation dialog box :

• Enter the name of the DATABASE.
• It is recommended to create a database user at this point. Check CREATE DATABASE USER checkbox.
• Provide the USERNAME.
• Provide his PASSWORD.

3. Click on the CREATE button.

Create a Database User

Access the PostgreSQL database interface.

1. In the CURRENT USERS section, click on the Create button:

2. Provide the information required in the Create Database User dialog box:

• Fill in the USERNAME field.
• Provide a PASSWORD.
• Choose the ASSOCIATED DATABASE from the drop-down list.

3. Click on the CREATE button.
4. Confirmation messages are displayed and the user is added to the list of CURRENT USERS.

Modification of a Database User’s Username or Password

Access the PostgreSQL database interface and locate the user whose name or password you wish to change in the CURRENT USERS section.

1. Click on the Rename or change password icon :

2. Enter the new USERNAME and/or PASSWORD in the appropriate field:

3. Click on the SAVE button.
4. Confirmation messages are displayed.

Adding a User to a Database

Typically, several users will need to connect to the database. You need to add them one by one.

Access to the PostgreSQL database interface and locate the database to which users are to be added in the CURRENT DATABASES section.

1. Click on the Add another user icon:

2. Select the USER name from the drop-down list:

3. Click on the ADD button.
4. A confirmation message is displayed.
5. By clicking on See more next to the corresponding database, you will see that the user has been added to the other users already displayed:

Deleting a User

It is advisable to delete users who no longer use your databases.

Access the PostgreSQL database interface and locate the user to be deleted in the CURRENT USERS section.

1. Click on the Delete icon:

2. Approve the deletion at the command prompt.
3. A confirmation message is displayed.

Remove User Access to a Database

It is essential to keep your database users’ access rights up to date. It is both pointless and risky to neglect to remove rights from a user you do not expect to work with.

Access the PostgreSQL database interface and locate the database and user whose privileges are to be withdrawn.

1. Click on the Delete icon :

2. Approve the deletion at the command prompt.
3. A confirmation message is displayed.

Deleting a Database

It is advisable to delete your databases if they are no longer in use.

1. Access the PostgreSQL database interface and locate the database in the CURRENT DATABASES section.
2. Click on the Delete icon:

PostgreSQL Processes

PostgreSQL processes are operations currently running on your databases.

Access to PostgreSQL Processes

To access to PostgreSQL processes:

1. Click on the button PostgreSQL Processes :

2. The operations are then displayed in the PostgreSQL Processes interface:

How to Synchronize the Grants

When initiated, grants synchronisation automatically applies and updates a user’s privileges and access rights across all objects (tables, sequences, functions) in a database.

This is because, unlike MySQL – where the global assignment of rights applies immediately to future tables – PostgreSQL manages permissions in a very strict and object-specific manner. Thus, in PostgreSQL, if you grant a user full rights to a database and then create new tables (for example, by updating a CMS), that user will not automatically have rights to these new tables. This causes access errors.

In N0C, you can work around this issue by clicking the Synchronise permissions button:

References

How to Manage Databases with PhpMyAdmin.

How to Import/Export Database in Command Line (User Mode).

Permissions (Database Engine).