0

I prepared a schema and tables using AWS SCT tool so my DMS job will have a landing place for data. Even thought access to the database and schema has been granted:

GRANT ALL ON DATABASE my_db TO "dms_user";
GRANT ALL ON SCHEMA my_schema TO "dms_user";
GRANT ALL ON ALL TABLES IN SCHEMA my_schema TO "dms_user";
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT ALL ON TABLES TO "dms_user";

I'm getting error:

2022-03-25T22:26:48 [TARGET_LOAD     ]E:  RetCode: SQL_ERROR  SqlState: XX000 NativeError: 30 Message: [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState XX000] ERROR:  Load into table 'table_test' failed.  Check 'stl_load_errors' system table for details. [1022502]  (ar_odbc_stmt.c:4815)

2022-03-25T22:26:48 [TARGET_LOAD     ]E:  Failed to load schema.table_testfrom S3, file name: LOAD00000001.csv [1022509]  (cloud_imp.c:2386)

2022-03-25T22:26:48 [TARGET_LOAD     ]E:  Failed to load ims_suretyradm_publish.dimaccount from S3, file name: LOAD00000001.csv [1022509]  (cloud_imp.c:2386)

stl_load_errors table is empty...

I'll greatly appreciate any help/guidance on this.

marcin2x4
  • 1,321
  • 2
  • 18
  • 44

1 Answers1

1

I hope it is not the difference between "my_schema" and "my_schema_name" as these are likely different due to obfuscation error.

There are a number of places things can go sideways. Have you checked the permissions after the grant?

select HAS_SCHEMA_PRIVILEGE('dms_user', 'my_schema', 'create'); 
select HAS_SCHEMA_PRIVILEGE('dms_user', 'my_schema', 'usage');

Another resource is awslabs' Redshift github repo - https://github.com/awslabs/amazon-redshift-utils - there are a number of admin views there that explore permissions. Knowing which step in the process is not doing what you expect will narrow things down.

Also, remember that you will want to change the default ACL for the schema so the new objects created will be useable by the correct people. For example:

ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT ALL ON TABLES TO dms_user;
Bill Weiner
  • 8,835
  • 2
  • 7
  • 18
  • There was a typo in schema name - thanks for noticing it! Both HAS_SCHEMA_PRIVILEGE queries return `false` in my case. – marcin2x4 Mar 18 '22 at 17:14
  • What do mean to using `ACL` ? – marcin2x4 Mar 18 '22 at 17:29
  • 1
    Did your grants complete successfully? Did you run these as a superuser or as the owner? Try reapplying the grants and checking the privileges directly after – Bill Weiner Mar 18 '22 at 18:54
  • 1
    ACL (access control list) is the postgres name for the ability to set default privileges and shows up in Redshift in places like the system table that contains these descriptions - PG_DEFAULT_ACL. Default privileges / ACL sets the rights that new objects created in the database will have. If not set users other than the owner will have zero rights to new objects (except in public schema). A common error when people make new schemas is to not set the default privileges for the schema. – Bill Weiner Mar 18 '22 at 18:59
  • Created the schema and ran `ALTER DEFAULT PRIVI...` as schema owner (we don't have superuser access). Still getting `false` when executing `HAS_SCHEMA_PRIVILEGE`. `GRANT ALL ON SCHEMA` seems to do the trick along with other `ALTER`s. – marcin2x4 Mar 18 '22 at 19:11
  • Now I'm getting `Table 'my_schema'.'my_table' does not exist, insufficient privileges or no supported columns`. I ran another `GRANT ALL ON TABLE` but didn't help even though `select has_table_privilege(...` shows `true`. – marcin2x4 Mar 18 '22 at 20:12
  • 1
    has_schema_privilege is true for usage as well? – Bill Weiner Mar 18 '22 at 23:43
  • I see your other question / info now. Are you sure DMS is getting into Redshift as dms_user? Could it be using a role instead? – Bill Weiner Mar 18 '22 at 23:46
  • DMS uses db-admin role created by Cloudforge break-glass mechanism thus it is not IAM Role. – marcin2x4 Mar 19 '22 at 19:21
  • 1
    Yes, a Redshift role. If DMS is using a role for rights in Redshift you may need to grant to the role. Like this example - GRANT CREATE USER TO ROLE sample_role1; – Bill Weiner Mar 19 '22 at 23:15
  • Will check and let you know a bit later! – marcin2x4 Mar 20 '22 at 00:06
  • `GRANT CREATE USER TO ROLE` syntax seems to be unsupported by Redshift. – marcin2x4 Mar 21 '22 at 09:47
  • This is straight from AWS docs - https://docs.aws.amazon.com/redshift/latest/dg/r_GRANT-examples.html – Bill Weiner Mar 21 '22 at 15:56
  • But my dms_user is USER, not ROLE in redshift. Sorry, I made a typo in earlier comment. – marcin2x4 Mar 21 '22 at 16:53
  • "DMS users db-admin role" is not referring to a Redshift role? After you apply the schema and table grants you are getting what from has_schema_privilege on db_user? When you see "does not exist, insufficient privileges" but the table does exist it may mean that the user doesn't have schema privileges and cannot find the table by name. – Bill Weiner Mar 21 '22 at 17:28
  • We don't currently use roles but single created users in Redshift. Our Ci/CD process uses break-glass function in cloudforge platform so DMS gets db-admin user to interact with Redshift. Before executing DMS I `GRANT ALL ON DATABASE XYZ to dms_user` so it can operate on the db. – marcin2x4 Mar 22 '22 at 10:30
  • I updated my post with current issue. – marcin2x4 Mar 25 '22 at 22:30