Grant to a group defined in Windows AD

[Hans Schou]

Hi

Can I have a advise on how to handle groups?

In my Windows AD (Active Directory) I have two groups named:
  readers
  writers

In Postgresql I have these databases:
  d1
  d2

The "writers" should have NOCREATEDB etc but ALL PRIVILEGES to d1 and d2.

The "readers" should have SELECT to all tables in d1 and no access at all
to d2.

It seems like I can either use a group ROLE or a SCHEME to accomplish my
goal. Which one will be the most best/simple to administer for me and my
colleagues? Or is there another approach we can use?

Here is my draft for a daily-run Powershell script which will get all the
users in the group "readers" in the Windows AD and create them in
Postgresql:

$LdapCon = "LDAP://CN=readers,OU=specimen,DC=example,DC=org"
Write-Host "-- Get group members from: $($LdapCon)"
$Group = [ADSI]$LdapCon
$Group.Member | ForEach-Object {
    $Searcher = [adsisearcher]"(distinguishedname=$_)"
    $u = $($searcher.FindOne().Properties.samaccountname).ToLower()
    Write-Host "CREATE ROLE `"$u`";"
    Write-Host " ALTER ROLE `"$u`" WITH LOGIN;"
    Write-Host " GRANT SELECT ... readers ...;"
}

And then I pipe the output to psql.exe.
The output looks like:
  CREATE ROLE "joe";
   ALTER ROLE "joe" WITH LOGIN;
   GRANT SELECT ... readers ...;

PS: To get a list of your own groups use Powershell:

([ADSISEARCHER]"samaccountname=$($env:USERNAME)").Findone().Properties.memberof