> For the complete documentation index, see [llms.txt](https://learn.capstorm.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://learn.capstorm.com/cs-govern/best-practices/postgresql-permissions.md).

# PostgreSQL Permissions

![](https://869259762-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FyE2QoOSmKpot88Q3kHGR%2Fuploads%2Fgit-blob-3236c0be7548c6fa1a53dd74f8584140f5e6ccbb%2F5fcf6d03-Govern-Banner.png?alt=media)

## **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                                 |