Federation using AD

https://console.aws.amazon.com/cloudformation/home?region=us-east-1#/stacks?filteringText=&filteringStatus=active&viewNested=true&hideStacks=false

dsadd user "cn=Bob,cn=Users,dc=adfsredshift,dc=com" -samid bob -upn bob@adfsredshift.com -email bob@adfsredshift.com -fn Bob -ln Smith -display "Bob Smith" -disabled no -pwd "YourOwnStrongPassword"

dsadd group "cn=AWS-Production, cn=Users, dc=adfsredshift, dc=com" -members "cn=Bob, cn=Users, dc=adfsredshift, dc=com"

dsadd group "cn=AWS-Dev, cn=Users, dc=adfsredshift, dc=com" -members "cn=Bob, cn=Users, dc=adfsredshift, dc=com"

dsadd user "cn=ADFSSVC,cn=Users, dc=adfsredshift,dc=com" -display "ADFS Service" -disabled no -pwd " YourOwnStrongPassword “

dsadd group "cn=RSDB-marketing, cn=Users, dc=adfsredshift, dc=com" -members "cn=Bob, cn=Users, dc=adfsredshift, dc=com"

PS C:\Users\Administrator> Install-windowsfeature adfs-federation –IncludeManagementTools

PS C:\Users\Administrator> Install-WindowsFeature -name Web-Server -IncludeManagementTools

	$LocalTempDir = $env:TEMP; $ChromeInstaller = "ChromeInstaller.exe"; (new-object System.Net.WebClient).DownloadFile('http://dl.google.com/chrome/install/375.126/chrome_installer.exe', "$LocalTempDir\$ChromeInstaller"); & "$LocalTempDir\$ChromeInstaller" /silent /install; $Process2Monitor =  "ChromeInstaller"; Do { $ProcessesFound = Get-Process | ?{$Process2Monitor -contains $_.Name} | Select-Object -ExpandProperty Name; If ($ProcessesFound) { "Still running: $($ProcessesFound -join ', ')" | Write-Host; Start-Sleep -Seconds 2 } else { rm "$LocalTempDir\$ChromeInstaller" -ErrorAction SilentlyContinue -Verbose } } Until (!$ProcessesFound)

https://<<YourInstanceHostname.adfsredshift.com>>/FederationMetadata/2007-06/FederationMetadata.xml

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "redshift:ListSchemas",
                "redshift:DescribeQuery",
                "redshift:ListDatabases",
                "redshift:DescribeClusters",
                "redshift:ExecuteQuery",
                "redshift:GetClusterCredentials",
                "redshift:FetchResults",
                "redshift:DescribeTable",
                "redshift:CreateClusterUser",
                "redshift:ListTables",
                "redshift:CancelQuery"
            ],
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": "redshift:JoinGroup",
            "Resource": "*"
        }
    ]
}

c:[Type == "http://schemas.microsoft.com/ws/2008/06/identity/claims/windowsaccountname", Issuer == "AD AUTHORITY"] => add(store = "Active Directory", types = ("http://temp/variable"), query = ";tokenGroups;{0}", param = c.Value);

c:[Type == "http://temp/variable", Value =~ "(?i)^AWS-"] => issue(Type = "https://aws.amazon.com/SAML/Attributes/Role", Value = RegExReplace(c.Value, "AWS-", "arn:aws:iam::<InfrastructureAccount>:saml-provider/ADFZ,arn:aws:iam::<InfrastructureAccount>:role/ADFZ-"));

c:[Type == "http://temp/variable", Value =~ "(?i)^RSDB-"]=> issue(Type = "https://redshift.amazon.com/SAML/Attributes/DbGroups", Value = RegExReplace(c.Value, "RSDB-", ""));

Set-AdfsProperties -EnableIdpInitiatedSignonPage $true

CREATE GROUP sales;
CREATE GROUP marketing;

CREATE SCHEMA sales;
CREATE SCHEMA marketing;

CREATE TABLE IF NOT EXISTS marketing.employee
(
	n_empkey INTEGER   
	,n_name CHAR(25)   
	,n_regionkey INTEGER   
	,n_comment VARCHAR(152)   
)
DISTSTYLE AUTO
 SORTKEY (n_empkey);

CREATE TABLE IF NOT EXISTS sales.employee_sales
(
	n_empkey INTEGER   
	,n_name CHAR(25)   
	,n_regionkey INTEGER   
	,n_comment VARCHAR(152)   
)
DISTSTYLE AUTO
 SORTKEY (n_empkey);

INSERT INTO marketing.employee
VALUES(1, 'Bob', 0, 'Marketing');

INSERT INTO sales.employee_sales
VALUES(1, 'John', 0, 'Sales');

Select * from marketing.employee;

Select * from sales.employee_sales;

ALTER DEFAULT PRIVILEGES IN SCHEMA sales;
GRANT SELECT on TABLES to GROUP sales;
GRANT USAGE on SCHEMA sales to GROUP sales;
GRANT SELECT on ALL TABLES in SCHEMA sales to GROUP sales;

ALTER DEFAULT PRIVILEGES IN SCHEMA marketing;
GRANT SELECT on TABLES to GROUP marketing;
GRANT USAGE on SCHEMA marketing to GROUP marketing;
GRANT SELECT on ALL TABLES in SCHEMA marketing to GROUP marketing;

select * from stv_sessions;

select * from marketing.employee;

select * from sales.employee_sales;