PostgreSQL Database Setup

A PostgreSQL database is required for the Credo AI platform. The following information describes how this instance will need to be configured.

Create the database and user for the Credo AI platform

warning

The CredoAI application uses the public database schema for governance asset management. The configured database user must have write access to this schema.

note

The platform requires that the database user has the ability to create schemas. The schema creation happens at the time a new tenant is created which will occur after installation and configuration of the platform.

CREATE DATABASE credoai;
CREATE ROLE credoai WITH PASSWORD 'SecurePassword' LOGIN;
REVOKE ALL PRIVILEGES ON DATABASE postgres FROM credoai;
GRANT ALL PRIVILEGES ON DATABASE credoai TO credoai;

\c credoai credoai

Install the required extensions

note

The extensions will be installed into the public schema by default.

CREATE EXTENSION btree_gin;
CREATE EXTENSION pg_trgm;
CREATE EXTENSION citext;

Sanity Check

To ensure that your user is configured correctly and the extensions have been installed, you can test one of the extensions.

$ psql -h <HOST> -p 5432 -U postgres

credoai=# \c credoai credoai


-- Check the user privileges to the public schema
credoai=# SELECT has_schema_privilege('credoai', 'public', 'USAGE')
    AND has_schema_privilege('credoai', 'public', 'CREATE')
    AS has_write_access;
 has_write_access
------------------
 t
(1 row)

-- Verify that the extensions are installed
credoai=# \dx
Table 2 List of installed extensions
Name | Version | Schema | Description
-----------+---------+------------+-------------------------------------------------------------------
btree_gin | 1.3 | public | support for indexing common datatypes in GIN
citext | 1.6 | public | data type for case-insensitive character strings
pg_trgm | 1.5 | public | text similarity measurement and index searching based on trigrams
[...]

-- Verify that the types created by the extensions are available
credoai=# \dT
public | citext |
public | gtrgm |

-- Validate extensions and types with a temporary table
credoai=# CREATE TABLE test_trgm (t text);
CREATE TABLE

credoai=# CREATE INDEX trgm_idx ON test_trgm USING GIST (t gist_trgm_ops);
CREATE INDEX

credoai=# SELECT t, similarity(t, 'word') AS sml
FROM test_trgm
WHERE t % 'word'
ORDER BY sml DESC, t;
(0 rows)

credoai=# drop table test_trgm;
DROP TABLE

Encryption

By default, the applications within the platform will use TLS to connect to your instance if it's available.

Platform Configuration

You will need the following information during the installation process to configure the platform to use this database instance.

Config	Example
Host	prod-credoai.cluster-abcdefghijkl.us-west-2.rds.amazonaws.com
Port	5432 (default)
Database	credoai
User	credoai
Password	SecurePassword

note

A Postgres instance can have one or more named databases. Each database can have one or more named schemas. A database user (or role) can only access the objects within the database it is connected.

Reference https://www.postgresql.org/docs/13/ddl-schemas.html