PostgreSQL Permissions

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.

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