PostgreSQL

Preparation

To allow Shaped to connect to your Postgres database, you need to create a read-only user and pass the details into your Create Model request. You can create a read-only user on your Postgres db with the following commands:

# 1. Create a new user. 
CREATE USER read_only_user WITH PASSWORD 'secure_password1!';

# 2. Grant the user connection to the database.
GRANT CONNECT ON DATABASE database_name TO read_only_user;

# 3. Grant the user usage of the schema.
GRANT USAGE ON SCHEMA public TO read_only_user;

# 4. Grant the user read access to all the tables in the schema. Note you can also 
# restrict this to your specific user, item and interaction views.
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_user;

# 5. Grant the group access to future tables in the schema.
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only_user;

Connectors Definition

Below are the fields required for the Postgres connectors

connectors:
    type: Postgres
    id: postgres
    user: database_readonly_username
    password: database_readonly_password
    host: your.pg.db.hostname.com
    port: 5432,
    database: database_name

Field	Example	Description
type	Postgres	Specifies the connector type, in this case “Postgres”.
id	postgres	Specifies the connector id, in this case “postgres”.
user	your_user	Access account username.
password	pAssw0rd1!	Access account password.
host	pg-test-db.XXXXXXXXX.us-east-2.rds.amazonaws.com	Database hostname.
port	5432	Database port (the default for Postgres is 5432).
database	movielens	The name of the database that contains your user, item, and interactions views.
query	select * from table	An optional query to retrieve the data source.

PostgreSQL

Preparation​

Preparation