Overview
By default, KORE provides a single Redshift account for organizations to use with DWA. That account is typically shared by various users within your organization. For organizations that desire more granular control, we can now provide per-user Redshift accounts.
Setup
Contact your KORE Success Manager and, for each user to provision, provide:
- user's name
- for individuals, the user's first (given) and last (family) name
- for a vendor or service, the entity name (such as Acme Partners Inc.)
- list of permissions and access to grant
- IP address(es) they will be allowed to connect from
- for individuals, you can omit this if they do not have a static IP address
KORE will provide you with the username(s) and password(s) via our secure information portal. The user can connect to the data warehouse using these credentials.
We strongly recommend that users change their passwords upon first sign in. They can do this by running the following SQL command, replacing the values in lowercase:
ALTER USER my_user_name PASSWORD 'new_password';
Amazon's passwords requirements:
- 8 to 64 character length
- at least one upper case letter, one lower case letter, and one number
- special characters (English-based) allowed, except:
' " \ / @
Access privileges (permissions)
Because you are no longer using a single account shared by all users, appropriate access privileges must be set for the database objects your users create in the custom
schema. Amazon uses a restrictive default, so that only a superuser or the user that creates an object may access it. KORE tightly restricts superuser access to Redshift databases.
When we first provision individual Redshift users for you, we will change the default privileges so that all users will have all privileges for new objects within the custom
schema. We will also grant all privileges to all users for existing objects within the custom
schema. For other schemas, we will grant read-only privileges to each user.
Upon request, KORE can create additional schemas for your organization that you can manage. This allows you to set more granular restrictions on Redshift users than using the custom
schema alone. For example, you might work with a vendor that writes data directly into your data warehouse but who shouldn't be allowed to read any other data.
Customizing privileges
When a user creates a database object, they may optionally override the default privileges set by KORE. For example, a user could create a table named private_information
within the custom
schema and revoke access for anyone else. They could then grant access to specific users or groups.
When we first provision individual Redshift users for you, KORE configures three groups. "orgname
" is replaced with the name of your organization as used in the hostname and database name:
orgnamedwausers
—standard users in your organization with read-write access to thecustom
andkorebi
schemas, and read-only access to other schemas.orgnamekoreusers
—KORE employees with read-write access to thecustom
andkorebi
schemas, access to admin schemas and views, and read-only access to other schemas.orgnameadminusers
—KORE accounts with extensive access, primarily used for automated management of the data pipeline.
KORE can also configure additional groups as needed. To view the list of groups, run:
SELECT * FROM pg_catalog.pg_group;
Examples
Suppose you have a schema set up for a vendor to write data into. To grant them write access to the full schema:
--set default for future database objects
ALTER DEFAULT PRIVILEGES IN SCHEMA vendorname_schema
GRANT ALL TO USER vendorname;
--change access to existing database objects
GRANT ALL IN SCHEMA vendorname_schema
TO USER vendorname;
To give all users in your organization read-only access to that schema, provide the SELECT
privilege for its tables:
--set default for future database objects
ALTER DEFAULT PRIVILEGES IN SCHEMA vendorname_schema
GRANT SELECT ON ALL TABLES TO GROUP orgnamedwausers;
--change access to existing database objects
GRANT SELECT ON ALL TABLES IN SCHEMA vendorname_schema
TO GROUP orgnamedwausers;
Or suppose that a particular user should be able to alter a specific table within vendorname_schema
, but no others:
GRANT ALL ON TABLE vendorname_schema.table_name TO USER username;
If you use stored procedures, you can allow all members of a group to run any of them:
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA custom
TO GROUP groupname;
Or alternatively, allow a specific user to execute a specific stored procedure:
GRANT EXECUTE ON PROCEDURE procedure_name IN SCHEMA custom
TO USER username;
Troubleshooting
To reset privileges on existing objects a user created, they can run these commands:
GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO GROUP clientdwausers;
GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO GROUP clientkoreusers;
GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO GROUP clientadminusers;
To reset the default privileges for objects a user will create later, they can run these commands:
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT ALL ON TABLES TO GROUP clientdwausers;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT ALL ON TABLES TO GROUP clientkoreusers;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT ALL ON TABLES TO GROUP clientadminusers;
A user can only grant privileges or change the default privileges for their own objects, not for any other user's. If you need to change the privileges for an existing object without using the owner's Redshift account (for example, after an employee leaves your organization), open a support ticket.