# PostgreSQL Permissions

![](/files/bV66cgZUHAhsDBXbIqYV)

## **PostgreSQL Permissions**

Ensure that CopyStorm is connected to a PostgreSQL database. If this is the first time setting it up, follow the recommended best practices for [configuring CopyStorm with PostgreSQL](https://learn.capstorm.com/copystorm/readme/best-practices/postgresql-setup).

CS:Govern for PostgreSQL **requires that the *****pgcrypto***** package be installed**. On most installations of CopyStorm, when a PostgreSQL database schema is created, this library will have been automatically added. However on older CopyStorm installations it is possible that *pgcrypto* was not included. If that is the case then the following command will add this required package:

```
CREATE EXTENSION IF NOT EXISTS pgcrypto;
```

Issue this command as the Database Administrator:

```
REVOKE SELECT ON GuardianKey FROM public;
```

## Required Permissions

| Permission Required            | Why It’s Needed                                                                                                                   |
| ------------------------------ | --------------------------------------------------------------------------------------------------------------------------------- |
| CREATE                         | Allows creation of tables, functions, indexes, and triggers in the connected database.                                            |
| USAGE ON SCHEMA \[schema]      | Allows use of the target schema (typically `public`) for creating objects.                                                        |
| CREATE ON SCHEMA \[schema]     | Required to create new tables and functions in the schema used by Govern.                                                         |
| SELECT                         | Allows querying Govern tables (e.g., for decrypting values, checking access roles).                                               |
| INSERT                         | Used to insert config data like access categories and key records during setup.                                                   |
| UPDATE                         | Needed to modify Govern config data (e.g., version updates, key rotations).                                                       |
| DELETE                         | Used to remove or reset records during setup or upgrades.                                                                         |
| REFERENCES                     | Required to define foreign key constraints between Govern tables.                                                                 |
| TRIGGER                        | Needed to create insert/update triggers used by Govern field enforcement logic.                                                   |
| CREATE FUNCTION                | Required to create user-defined functions like guard\_archive\_get, which implement field decryption and access logic for Govern. |
| SELECT ON pg\_roles / pg\_user | Allows Govern functions to check current user’s database roles for field-level access decisions.                                  |
| CREATE EXTENSION pgcrypto      | Required to use built-in encryption functions like `pgp_sym_encrypt` and `pgp_sym_decrypt` for CS:Govern field protection.        |

## Check If `pgcrypto` Is Installed

This query checks if the `pgcrypto` extension is available in your database. This extension is required for encryption and decryption functions such as `pgp_sym_encrypt` and `pgp_sym_decrypt`.

```
SELECT * FROM pg_extension WHERE extname = 'pgcrypto';
```

## Expected Output

If `pgcrypto` is installed, you’ll see it listed in the results, usually under the `public` schema. If no rows are returned, the extension is not installed.

| extname  | schema |
| -------- | ------ |
| pgcrypto | public |

## Check If the User Has `USAGE,CREATE` On the Current Schema

This checks if the current user has the ability to use and create objects in the given schema. **Note:** Having `CREATE` on a schema allows creating tables, views, and functions.

```
-- Replace 'your_schema' with the target schema name
SELECT has_schema_privilege(current_user, 'your_schema', 'USAGE, CREATE') AS has_required_schema_privileges;
```

## Expected Output

Having `CREATE` privilege on the current schema means the user can:

* **Create tables**, which they automatically own
* **Create functions and procedures**
* **Create triggers on any tables they own**

This level of access satisfies the required permissions for most CS:Govern operations within that schema, including defining DML triggers, custom logic, and support functions.

| has\_required\_schema\_privileges |
| --------------------------------- |
| t                                 |


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://learn.capstorm.com/cs-govern/best-practices/postgresql-permissions.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.