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