# Manage PostgreSQL Users, Databases and Plugins in Zerops


This guide covers how to manage your PostgreSQL databases in Zerops, including default setup, database management tools, plugins, and best practices.

## Default Database and User

Zerops creates a default database and user automatically when a new PostgreSQL service is [created](/postgresql/how-to/create).

### Database

- **Name**: Identical to the service hostname
- **Encoding**: `utf8mb4`

### DB User

- **Username**: Identical to the service hostname
- **Password**: Generated randomly

:::info
For connection methods and environment variables, see the [Connect to PostgreSQL in Zerops](/postgresql/how-to/connect) page.
:::

:::caution Important notes
- When changing passwords, update both the database user password and the environment variable separately - they don't automatically synchronize.
- While both `postgresql://` and `postgres://` URI formats are valid, Zerops uses the `postgresql://` format. If your software requires `postgres://`, create a custom environment variable with this format.
- Do not use SSL/TLS protocols for internal connections. Security is assured by the project's private network.
:::

## Database Management Tools

You can use any PostgreSQL management tool of your choice to administer your databases in Zerops. For convenience, Zerops provides ready-to-use recipes for two popular web-based database management tools:

* [AdminerEvo](https://github.com/adminerevo/adminerevo) - developed by the AdminerEvo community and is a continuation of the [Adminer](https://www.adminer.org) project by Jakub Vrána
* [phpMyAdmin](https://www.phpmyadmin.net) - a popular free database administration tool that works with both MySQL and PostgreSQL databases

### Installing Management Tools

You can install these tools with a simple one-click import in Zerops:

1. In Zerops GUI, open your project and select **Import services** from the left menu
2. Copy and paste one of the following YAML configurations:

### Accessing Management Tools

After installation, you can access these tools via VPN:

1. [Start the Zerops VPN](/references/networking/vpn)
2. Type `http://adminerevo` or `http://phpmyadmin` in your browser

:::tip
Try `http://adminerevo.zerops` or `http://phpmyadmin.zerops` if you encounter any connection issues.
:::

:::caution
Do not use https when connecting to management tools via VPN.
:::

## Database Tools on Your Workstation

You can use various database management tools from your local workstation to connect to your PostgreSQL database in Zerops:

1. **Establish a secure tunnel** using the [Zerops VPN](/references/networking/vpn) to create an encrypted connection to your Zerops project
2. **Obtain the [connection details](/postgresql/how-to/connect#connection-details)** from Zerops GUI
    - Environment variables are not available through VPN connections
3. Connect with your **preferred database tool**
    - Do not use SSL/TLS (security is provided by the VPN)
    - **Desktop Database Tools** - popular GUI tools like pgAdmin, DBeaver, DataGrip, or any other PostgreSQL-compatible client will work with Zerops
    - **Command Line with psql** - connect using the standard PostgreSQL command-line client with the credential obtained above:
        ```sh
        psql -h [hostname] -U [user] -d [database_name]
        ```

:::tip
    Try `{hostname}.zerops` instead of just `{hostname}` if you encounter any connection issues.
:::

## How to install and manage PostgreSQL plugins

### Viewing available plugins
You can list all available PostgreSQL plugins by running the following query *(superuser privileges not required)*:

```sql
SELECT * FROM pg_available_extensions ORDER BY name;
```

### Installing plugins (requires superuser)

1. **Connect with superuser credentials**:
   - Use the `superUser` (user `postgres`) and `superUserPassword` environment variables from your PostgreSQL service

2. **Switch to your service database**:
   When logging in as the superuser, you're initially in the `postgres` database, not your service database.

3. **Install required extensions**:
   ```sql
   CREATE EXTENSION pg_stat_statements;
   CREATE EXTENSION vector;
   CREATE EXTENSION postgis;
   ```

:::warning
Currently, it is not possible to add new plugins that are not already listed in `pg_available_extensions`.
:::

When working with text search functionality, you'll need to reference the correct `stop`, `dict`, and `affix` files when creating dictionaries in your database. These files are essential for proper text search configuration.

Zerops PostgreSQL includes the following dictionary files:

</Dropdown>
For more information on text search dictionaries, refer to the [PostgreSQL documentation](https://www.postgresql.org/docs/16/textsearch-dictionaries.html).