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. |