PostgreSQL Management

PostgreSQL is an open-source object-relational database management system. It is known for its reliability, scalability, and extensive functionality, including JSON support, full-text search, stored procedures, extensions, and more.

With Hostman managed PostgreSQL databases, you get a fully managed PostgreSQL cluster without manual setup: everything is handled automatically.

When creating a cluster, you can select the PostgreSQL version: 14, 15, 16, 17, 18.

Creating Databases

When you create a new cluster, a database named default_db is created automatically. 

You can create additional databases:

1. Go to the Databases section of your Hostman control panel.

2. Click on the cluster.

3. In the Databases tab, click Add.

4. Provide a name for your database: 3–64 characters. Letters, numbers, and special characters are allowed.

5. Add a description, if necessary.

6. Click Create.

Users and Privileges

In your control panel, you can manage PostgreSQL users and their privileges.

Creating Users

1. Go to the Databases section and click on the cluster.

2. Open the Users tab and click Add.

3. Set the username and password:

• Username: 3–64 characters. Letters, numbers, and special characters are allowed.

• Password: 8–30 characters. Letters, numbers, and special characters are allowed.

4. (Optional) Configure the user’s privileges. You can also leave the default parameters and change them later at any time.

5. Click Create.

Modifying Privileges

To update the privileges of an existing user:

1. Go to the Databases section and click on the cluster.

2. Open the Users tab.

3. Click the three dots next to the user.

4. Select Privileges.

5. Choose the required privileges. You can grant access to a specific database or grant the same privileges for all databases by enabling Identical for all databases in the top right.

Available Privileges

Changing User Password

You can change the user password:

1. Go to the Database section and click on the cluster.
2. Go to the Users tab.
3. Click the three dots next to the user.
4. Select Change password.
5. Enter a new value and save changes.

Extensions

PostgreSQL extensions allow you to expand the functionality of PostgreSQL databases by adding additional capabilities to solve certain tasks. You can read more about extensions in the PostgreSQL documentation.

Enabling Extensions

To enable or disable extensions:

1. Go to the Databases section and click on the cluster.

2. Go to the Configuration tab.

3. Click Modify next to Extensions.

4. Select the database and the required extensions. To apply the same extensions to all databases in the cluster, enable the Use the same extensions for all databases option.

Extensions List

The following extensions are available:

Database Parameters

You can change database settings to optimize its performance. 

By default, the database is created with the parameters that are suitable for most tasks and will ensure the performance of the selected configuration. However, if your project requires specific settings, you can change them as you wish. 

Be careful and change parameters only if you are sure of their purpose. Incorrect configuration can negatively affect the operation of the database and its performance.

Changing Parameters

1. Go to the Databases section and click on the cluster.

2. Go to the Configuration tab.

3. Click Modify parameters next to Database parameters.

4. Adjust the parameters. To read more about each parameter, click Open description next to it.

5. Click Apply at the bottom of the page.

Parameters List

Resetting Parameters

You can easily restore the default parameters:

1. Go to the Databases section and click on the cluster.

2. Go to the Configuration tab.

3. Click Modify parameters next to Database parameters.

4. Scroll down to the bottom of the page and click Restore default settings.

Be careful: the parameters will be reset immediately, without additional confirmation.

Connecting to the Database

You can connect to your databases using Adminer or via terminal. Connecting via Adminer is possible only if the cluster has a public IP address.

Adminer

You can connect to the database via the built-in web interface Adminer. 

1. Go to the Databases section and click on the cluster.

2. Сlick the Web Interface button and select Adminer.

Terminal

Install the PostgreSQL client:

• Ubuntu/Debian

sudo apt install postgresql-client

• Arch

sudo pacman -S postgresql-libs

• Fedora

sudo dnf install postgresql

There are two ways to connect to a PostgreSQL cluster:

• via public IP;
• via private IP.

You can copy the connection command from:

• the Dashboard tab:

• the Connection tab.

Role Management

A user with the CREATE ROLE privilege can:

• create new users (CREATE USER);
• create roles (CREATE ROLE);
• manage role memberships (GRANT ... TO ...).

Creating a User

A user is a role that is allowed to log in to the database. Such a role can connect with a username and password.

To create a user with a password, run:

CREATE USER someuser WITH PASSWORD 'password';

Here, someuser is the username and password is the password.

The new user will appear in the Users tab shortly after creation.

Creating a Role and Assigning It to a User

Instead of granting permissions directly to each user, it is better to create roles with the required privileges and then assign users to these roles.

Let’s create two roles: one with read-only access, and another with read and write access.

Read-only role:

CREATE ROLE app_reader;

Read/write role:

CREATE ROLE app_writer;

Assign a role to a user:

GRANT app_writer TO someuser;

Schema Configuration

By default, all PostgreSQL tables are created in the public schema. To grant permissions, create a new schema:

CREATE SCHEMA app_data;

Create tables in the new schema:

CREATE TABLE app_data.users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE app_data.orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES app_data.users(id),
  order_date TIMESTAMP DEFAULT now()
);

Grant access to the schema for the previously created roles:

GRANT USAGE ON SCHEMA app_data TO app_reader;
GRANT USAGE ON SCHEMA app_data TO app_writer;

Grant the right to create objects in the schema:

GRANT CREATE ON SCHEMA app_data TO app_writer;

Table Permissions

Grant read access to all existing tables in the schema:

GRANT SELECT ON ALL TABLES IN SCHEMA app_data TO app_reader;

Grant read/write access:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_data TO app_writer;

Set default permissions for new tables:

ALTER DEFAULT PRIVILEGES IN SCHEMA app_data 
GRANT SELECT ON TABLES TO app_reader;

ALTER DEFAULT PRIVILEGES IN SCHEMA app_data 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writer;

And default permissions for new sequences:

ALTER DEFAULT PRIVILEGES IN SCHEMA app_data 
GRANT USAGE, SELECT ON SEQUENCES TO app_writer;

Creating a Dump

To create a dump, run:

pg_dump -x 'postgresql://<user>:<password>@<host>:5432/<db>' | gzip > default_db.sql.gz

The -x (--no-privileges) flag excludes GRANT and REVOKE commands from the dump, since permission management is handled via the control panel.

Without this flag, errors may occur when loading the dump, though they won’t affect data restoration.

Loading a Dump

To load a dump, run:

zcat default_db.sql.gz | psql 'postgresql://<user>:<password>@<host>:5432/<db>'

This command decompresses the dump and passes it to the PostgreSQL client for execution.