[
https://issues.apache.org/jira/browse/AIRAVATA-3296?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17079963#comment-17079963
]
Marcus Christie edited comment on AIRAVATA-3296 at 4/15/20, 12:19 AM:
----------------------------------------------------------------------
SQL script for creating missing entries:
{code:sql}
-- create table to store GRP tokens that don't have sharing_catalog ENTITY
records
CREATE TABLE sharing_catalog.TEMP_TOKENS
SELECT
DISTINCT RESOURCE_CS_TOKEN AS TOKEN
FROM
app_catalog.GROUP_COMPUTE_RESOURCE_PREFERENCE
WHERE
RESOURCE_CS_TOKEN IS NOT NULL
AND RESOURCE_CS_TOKEN != ''
AND RESOURCE_CS_TOKEN NOT IN (
SELECT
ENTITY_ID
FROM
sharing_catalog.ENTITY
)
AND EXISTS (
SELECT
1
FROM
credential_store.CREDENTIALS
WHERE
TOKEN_ID = RESOURCE_CS_TOKEN
)
UNION
DISTINCT
SELECT
DISTINCT DEFAULT_CREDENTIAL_STORE_TOKEN AS TOKEN
FROM
app_catalog.GROUP_RESOURCE_PROFILE
WHERE
DEFAULT_CREDENTIAL_STORE_TOKEN IS NOT NULL
AND DEFAULT_CREDENTIAL_STORE_TOKEN != ''
AND DEFAULT_CREDENTIAL_STORE_TOKEN NOT IN (
SELECT
ENTITY_ID
FROM
sharing_catalog.ENTITY
)
AND EXISTS (
SELECT
1
FROM
credential_store.CREDENTIALS
WHERE
TOKEN_ID = DEFAULT_CREDENTIAL_STORE_TOKEN
);
-- Create ENTITY records
INSERT INTO
sharing_catalog.ENTITY (
ENTITY_ID,
DOMAIN_ID,
ENTITY_TYPE_ID,
OWNER_ID,
NAME,
DESCRIPTION,
ORIGINAL_ENTITY_CREATION_TIME,
CREATED_TIME,
UPDATED_TIME
)
SELECT
TOKEN_ID,
GATEWAY_ID,
concat(GATEWAY_ID, ':CREDENTIAL_TOKEN') AS ENTITY_TYPE_ID,
concat(PORTAL_USER_ID, '@', GATEWAY_ID) AS OWNER_ID,
TOKEN_ID,
DESCRIPTION,
UNIX_TIMESTAMP(TIME_PERSISTED) * 1000 AS ORIGINAL_ENTITY_CREATION_TIME,
UNIX_TIMESTAMP(TIME_PERSISTED) * 1000 AS CREATED_TIME,
UNIX_TIMESTAMP(TIME_PERSISTED) * 1000 AS UPDATED_TIME
FROM
credential_store.CREDENTIALS
WHERE
TOKEN_ID IN (
SELECT
TOKEN
FROM
sharing_catalog.TEMP_TOKENS
);
-- Create SHARING records: 1) OWNER permission, 2) For Admins group, READ and
-- WRITE permission, and 3) for Read Only Admins group, READ permission
INSERT INTO
sharing_catalog.SHARING (
PERMISSION_TYPE_ID,
ENTITY_ID,
DOMAIN_ID,
GROUP_ID,
SHARING_TYPE,
INHERITED_PARENT_ID,
CREATED_TIME,
UPDATED_TIME
)
SELECT
concat(GATEWAY_ID, ':OWNER'),
TOKEN_ID,
GATEWAY_ID,
concat(PORTAL_USER_ID, '@', GATEWAY_ID) AS GROUP_ID,
'DIRECT_CASCADING',
TOKEN_ID,
UNIX_TIMESTAMP(TIME_PERSISTED) * 1000 AS CREATED_TIME,
UNIX_TIMESTAMP(TIME_PERSISTED) * 1000 AS UPDATED_TIME
FROM
credential_store.CREDENTIALS
WHERE
TOKEN_ID IN (
SELECT
TOKEN
FROM
sharing_catalog.TEMP_TOKENS
)
UNION
ALL
SELECT
concat(c.GATEWAY_ID, ':READ'),
c.TOKEN_ID,
c.GATEWAY_ID,
g.ADMINS_GROUP_ID AS GROUP_ID,
'DIRECT_NON_CASCADING',
c.TOKEN_ID,
UNIX_TIMESTAMP(c.TIME_PERSISTED) * 1000 AS CREATED_TIME,
UNIX_TIMESTAMP(c.TIME_PERSISTED) * 1000 AS UPDATED_TIME
FROM
credential_store.CREDENTIALS c
INNER JOIN app_catalog.GATEWAY_GROUPS g ON g.GATEWAY_ID = c.GATEWAY_ID
WHERE
TOKEN_ID IN (
SELECT
TOKEN
FROM
sharing_catalog.TEMP_TOKENS
)
UNION
ALL
SELECT
concat(c.GATEWAY_ID, ':WRITE'),
c.TOKEN_ID,
c.GATEWAY_ID,
g.ADMINS_GROUP_ID AS GROUP_ID,
'DIRECT_NON_CASCADING',
c.TOKEN_ID,
UNIX_TIMESTAMP(c.TIME_PERSISTED) * 1000 AS CREATED_TIME,
UNIX_TIMESTAMP(c.TIME_PERSISTED) * 1000 AS UPDATED_TIME
FROM
credential_store.CREDENTIALS c
INNER JOIN app_catalog.GATEWAY_GROUPS g ON g.GATEWAY_ID = c.GATEWAY_ID
WHERE
TOKEN_ID IN (
SELECT
TOKEN
FROM
sharing_catalog.TEMP_TOKENS
)
UNION
ALL
SELECT
concat(c.GATEWAY_ID, ':READ'),
c.TOKEN_ID,
c.GATEWAY_ID,
g.READ_ONLY_ADMINS_GROUP_ID AS GROUP_ID,
'DIRECT_NON_CASCADING',
c.TOKEN_ID,
UNIX_TIMESTAMP(c.TIME_PERSISTED) * 1000 AS CREATED_TIME,
UNIX_TIMESTAMP(c.TIME_PERSISTED) * 1000 AS UPDATED_TIME
FROM
credential_store.CREDENTIALS c
INNER JOIN app_catalog.GATEWAY_GROUPS g ON g.GATEWAY_ID = c.GATEWAY_ID
WHERE
TOKEN_ID IN (
SELECT
TOKEN
FROM
sharing_catalog.TEMP_TOKENS
);
DROP TABLE sharing_catalog.TEMP_TOKENS;
{code}
was (Author: marcuschristie):
Work in progress scripts for creating missing entries:
{code:sql}
SELECT
DISTINCT RESOURCE_CS_TOKEN
FROM
app_catalog.GROUP_COMPUTE_RESOURCE_PREFERENCE
WHERE
RESOURCE_CS_TOKEN IS NOT NULL
AND RESOURCE_CS_TOKEN != ''
AND RESOURCE_CS_TOKEN NOT IN (
SELECT
ENTITY_ID
FROM
sharing_catalog.ENTITY
)
AND EXISTS (
SELECT
1
FROM
credential_store.CREDENTIALS
WHERE
TOKEN_ID = RESOURCE_CS_TOKEN
);
SELECT
DISTINCT DEFAULT_CREDENTIAL_STORE_TOKEN
FROM
app_catalog.GROUP_RESOURCE_PROFILE
WHERE
DEFAULT_CREDENTIAL_STORE_TOKEN IS NOT NULL
AND DEFAULT_CREDENTIAL_STORE_TOKEN != ''
AND DEFAULT_CREDENTIAL_STORE_TOKEN NOT IN (
SELECT
ENTITY_ID
FROM
sharing_catalog.ENTITY
)
AND EXISTS (
SELECT
1
FROM
credential_store.CREDENTIALS
WHERE
TOKEN_ID = DEFAULT_CREDENTIAL_STORE_TOKEN
);
INSERT INTO
sharing_catalog.ENTITY (
ENTITY_ID,
DOMAIN_ID,
ENTITY_TYPE_ID,
OWNER_ID,
NAME,
DESCRIPTION,
ORIGINAL_ENTITY_CREATION_TIME,
CREATED_TIME,
UPDATED_TIME
)
SELECT
TOKEN_ID,
GATEWAY_ID,
concat(GATEWAY_ID, ':CREDENTIAL_TOKEN') AS ENTITY_TYPE_ID,
concat(PORTAL_USER_ID, '@', GATEWAY_ID) AS OWNER_ID,
TOKEN_ID,
DESCRIPTION,
UNIX_TIMESTAMP(TIME_PERSISTED) * 1000 AS ORIGINAL_ENTITY_CREATION_TIME,
UNIX_TIMESTAMP(TIME_PERSISTED) * 1000 AS CREATED_TIME,
UNIX_TIMESTAMP(TIME_PERSISTED) * 1000 AS UPDATED_TIME
FROM
credential_store.CREDENTIALS
WHERE
TOKEN_ID IN (
SELECT
DISTINCT RESOURCE_CS_TOKEN
FROM
app_catalog.GROUP_COMPUTE_RESOURCE_PREFERENCE
WHERE
RESOURCE_CS_TOKEN IS NOT NULL
AND RESOURCE_CS_TOKEN != ''
AND RESOURCE_CS_TOKEN NOT IN (
SELECT
ENTITY_ID
FROM
sharing_catalog.ENTITY
)
AND EXISTS (
SELECT
1
FROM
credential_store.CREDENTIALS
WHERE
TOKEN_ID = RESOURCE_CS_TOKEN
)
UNION
DISTINCT
SELECT
DISTINCT DEFAULT_CREDENTIAL_STORE_TOKEN
FROM
app_catalog.GROUP_RESOURCE_PROFILE
WHERE
DEFAULT_CREDENTIAL_STORE_TOKEN IS NOT NULL
AND DEFAULT_CREDENTIAL_STORE_TOKEN != ''
AND DEFAULT_CREDENTIAL_STORE_TOKEN NOT IN (
SELECT
ENTITY_ID
FROM
sharing_catalog.ENTITY
)
AND EXISTS (
SELECT
1
FROM
credential_store.CREDENTIALS
WHERE
TOKEN_ID = DEFAULT_CREDENTIAL_STORE_TOKEN
)
);
{code}
> Missing sharing_catalog records for credential
> ----------------------------------------------
>
> Key: AIRAVATA-3296
> URL: https://issues.apache.org/jira/browse/AIRAVATA-3296
> Project: Airavata
> Issue Type: Bug
> Reporter: Marcus Christie
> Assignee: Marcus Christie
> Priority: Major
>
> SEAGrid credentials in Default group resource profile are missing
> sharing_catalog records. Perhaps because the owning user is not in the
> sharing catalog.
> h5. TODO
> - [x] Check if there are any other instances of this problem and fix those up
> too
> - [ ] create query to find other instances and write them to temporary table
> - [ ] create query to create ENTITY records
> - [ ] create query to create SHARING records for OWNER
> - [ ] create query to create SHARING records for Admins and Read Only Admins
> groups
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
