---
title: "PostgreSQL user management"
sidebar_label: "User management"
sidebar_position: 8
description: "How to create a user, change their password, configure database access, and manage privileges"
---

import Formbricks from '@theme/MDXComponents/Formbricks'
import MoreVerticalIcon from 'docs-kit/icons/more-vertical'

# PostgreSQL user management

Users are created to access databases in a PostgreSQL cluster.

To create a database in a cluster, you must first [create a user](/managed-databases/postgresql/manage-users.mdx).

Users only have access to the cluster itself; they cannot access cluster nodes because they are managed by Selectel. By default, all users in a cluster have identical permissions.

You can grant access to a single PostgreSQL database to multiple users, but there can be only one [database owner](#database-owner). You can [grant privileges](#grant-privileges) to users for database objects.

## Database owner \{#database-owner}

When creating a PostgreSQL database, you must select an owner user.

A PostgreSQL database owner is a user who inherits ownership of objects created by deleted users. After a user is deleted, you will not lose access to the objects they created; instead, you can manage them through the owner. Unlike a standard user, the database owner has access to all its objects and can perform operations on them.

## Create a user \{#create-user}

1. In the [Dashboard](https://my.selectel.ru/vpc/default/dbaas/), from the top menu, click **Products** and select **Managed Databases**.
2. Open the **Active** tab.
3. Open the cluster page → **Users** tab.
4. Click **Create user**.
5. Enter the name and password. Save the password—it will not be stored in the dashboard.
6. Click **Save**.

## Change user password \{#change-user-password}

After a cluster is created, you can change the user password. Do not forget to update the password in your application.

1. In the [Dashboard](https://my.selectel.ru/vpc/default/dbaas/), from the top menu, click **Products** and select **Managed Databases**.
2. Open the **Active** tab.
3. Open the cluster page → **Users** tab.
4. In the user’s <MoreVerticalIcon /> menu, select **Change password**.
5. Enter or generate a new password and save your changes.

## Configure database access \{#configure-access-to-database}

### Grant access to a user \{#grant-user-access}

You can grant access to a single PostgreSQL database to multiple users.

1. In the [Dashboard](https://my.selectel.ru/vpc/default/dbaas/), from the top menu, click **Products** and select **Managed Databases**.
2. Open the **Active** tab.
3. Open the database cluster page → **Databases** tab → database page.
4. In the **Have access** block, click **Add** and select a user.

The user can only connect to the database (`CONNECT`) and cannot perform object operations. To grant the user access to objects, [assign the necessary privileges to them](#grant-privileges).

### Change database owner \{#change-database-owner}

A PostgreSQL database owner is assigned during creation. You cannot delete an owner (every database must have one), but you can change the owner to a different user.

1. In the [Dashboard](https://my.selectel.ru/vpc/default/dbaas/), from the top menu, click **Products** and select **Managed Databases**.
2. Open the **Active** tab.
3. Open the database cluster page → **Databases** tab → database page.
4. In the **Database owner** list, select a different owner.

### Revoke user access \{#revoke-access-for-user}

1. In the [Dashboard](https://my.selectel.ru/vpc/default/dbaas/), from the top menu, click **Products** and select **Managed Databases**.
2. Open the **Active** tab.
3. Open the database cluster page → **Databases** tab → database page.
4. In the **Have access** block, remove the user.

## Configure user privileges \{#configure-user-privileges}

The database owner has access to operations on any database objects (schemas, tables, functions). By default, object owners have full access and all rights to an object. You can grant privileges (access rights) to an object to other users.

In PostgreSQL versions 14 and lower, users are granted the `CREATE` privilege on the `public` schema by default. In PostgreSQL 15, only the `CONNECT` privilege is granted; usage patterns are described in the [Usage Patterns](https://www.postgresql.org/docs/15/ddl-schemas.html#DDL-SCHEMAS-PATTERNS) section of the PostgreSQL documentation.

### Grant privileges \{#grant-privileges}

You can grant privileges on database objects to users using the [GRANT](https://www.postgresql.org/docs/current/sql-grant.html) command. Available privileges include: `SELECT`, `INSERT`, `DELETE`, `USAGE`.

Example of granting read access (`SELECT`) to table `table` for user `user`:

```bash
GRANT SELECT ON table TO user;
```

### Create a schema user with read-only permissions \{#create-read-only-user}

You can create a user with access to a cluster database, a table in the default schema, and all tables in a schema.

Automatically, all new tables will be created with read-only access (read-only) for this user.

1. [Create a user](#create-user).

2. [Connect to the database](/managed-databases/postgresql/connect-to-cluster.mdx).

3. Create a schema `schema` and a table `table`:

   ```bash
   CREATE SCHEMA schema;
   CREATE TABLE schema.table(i int);
   INSERT INTO schema.table(i) values(1);
   ```

4. Grant privileges to user `user`:

   ```bash
   GRANT USAGE ON SCHEMA schema TO user;
   GRANT SELECT ON ALL TABLES IN SCHEMA schema TO user;
   ALTER DEFAULT PRIVILEGES IN SCHEMA schema GRANT SELECT ON TABLES TO user;
   ```

### Revoke privileges \{#revoke-privileges}

You can revoke user privileges using the [REVOKE](https://www.postgresql.org/docs/current/sql-revoke.html) command.

Example of revoking a privilege from user `user` for schema `schema`:

```bash
REVOKE USAGE ON SCHEMA schema FROM user;
```

<Formbricks />