PGConf APAC 2018 - Lightening Talk #2 - Centralizing Authorization in PostgreSQL

PGConf APAC 2018 - Lightening Talk #2 - Centralizing Authorization in PostgreSQL

• 1.
  Centralising Authorization inPostgreSQL Experimenting with LDAP synchronization
• 2.
  Structure of thistalk…  A summary of my experience with implementing a simple form of Centralized Authorisation  A step by step approach
• 3.
  Authentication versus Authorisation PostgreSQLLDAP Authentication ldap ldapserver=servername.ad.company.au ldaptls=1 ldapbasedn="ou=AU,dc=ad,dc=company,dc=au" ldapbinddn="cn=Gary Evans,ou=consultant,ou=ThirdParty Contractors,ou=Users, ou=AU,dc=ad,dc=corelogic,dc=asia" ldapbindpasswd="<the password>" ldapsearchattribute=sAMAccountName
• 4.
  Benefits of acentralized approach  Single point of control of database users  Adding a user can be done by help desk  Less error prone
• 5.
  Synchronisation Approach StarOne DBROLES postgres Andrew Smith BobBrown Jenny Reid Julie Benson privileges
• 6.
  Synchronisation Approach Server StarOne DBROLES postgres Andrew Smith BobBrown Jenny Reid Julie Benson privileges ldapsearch -Z -LLL -h ldapservername -D gevans@ldapservername -w password -b dc=ad,dc=companyname,dc=aus '(&(objectClass=user)(memberOf=CN='"${ADRow[0]}"' ,OU=SQL,OU=Groups,OU=AU,DC=ad,DC=ccompany name,DC=aus))' sAMAccountName | sed -e 's/sAMAccountName: ([a-zA-Z]*)/1/;tx;d;:x'
• 7.
  Synchronisation Approach Database DB_Report DB_Admin DB_Analysts Andrew Smith BobBrown Jenny Reid Andrew Smith Julie Benson Annette Huntly Paul Brown Andrew Smith Bob Brown Jenny Reid Members Groups ROLES postgres Andrew Smith Bob Brown Jenny Reid Julie Benson Annette Huntly Paul Brown AppOne DB StarOne DB
• 8.
  Synchronisation Approach Database DB_Report DB_Admin DB_Analysts Andrew Smith BobBrown Jenny Reid Andrew Smith Julie Benson Annette Huntly Paul Brown Andrew Smith Bob Brown Jenny Reid Members Groups ROLES postgres Andrew Smith Bob Brown Jenny Reid Julie Benson Annette Huntly Paul Brown AppOne DB StarOne DB
• 9.
  Synchronisation Approach Database DB_Report DB_Admin DB_Analysts Andrew Smith BobBrown Jenny Reid Andrew Smith Julie Benson Annette Huntly Paul Brown Andrew Smith Bob Brown Jenny Reid Server Members Groups ROLES postgres Andrew Smith Bob Brown Jenny Reid Julie Benson Annette Huntly Paul Brown AppOne DB StarOne DB ID Hostname Dbname ADGroup Dbrole Enabled CRUD 1 Serverone AppOne DB_Report Read_only True F,T,F,F 2 Serverone StarOne DB_Report Reporting True F,T,T,F 3 Serverone AppOne DB_Admin Admin_user True T,T,T,T 4 Serverone StarOne DB_Analysts Analyst True F,T,F,F 5
• 10.
  Synchronisation Approach Database DB_Report DB_Admin DB_Analysts Andrew Smith BobBrown Jenny Reid Andrew Smith Julie Benson Annette Huntly Paul Brown Andrew Smith Bob Brown Jenny Reid Server Members Groups ROLES postgres Andrew Smith Bob Brown Jenny Reid Julie Benson Annette Huntly Paul Brown AppOne DB StarOne DB ID Hostname Dbname ADGroup Dbrole Enabled CRUD 1 Serverone AppOne DB_Report Read_only True F,T,F,F 2 Serverone StarOne DB_Report Reporting True F,T,T,F 3 Serverone AppOne DB_Admin Admin_user True T,T,T,T 4 Serverone StarOne DB_Analysts Analyst True F,T,F,F 5 ${PSQL} ${DBMONITOR//HOST/localhost}/schemaname -qtAX --field-separator ' ' -c "SELECT distinct adgroup FROM mapping WHERE enabled = true and hostname='${hostname}'" | while read -a ADRow ; do for adname in $(ldapsearch -Z -LLL -h ldapservername -D gevans@ldapservername -w password -b dc=ad,dc=companyname,dc=aus '(&(objectClass=user)(memberOf=CN='"${ADRow[0]}"',OU=SQL,OU=Groups,OU=AU,DC=ad,DC=companyna me,DC=aus))' sAMAccountName | sed -e 's/sAMAccountName: ([a-zA-Z]*)/1/;tx;d;:x') do ${PSQL} ${PGMONITOR//HOST/$hostname}/ schemaname -qtAX --field-separator ' ' -c "CREATE ROLE ${adname} with LOGIN;“ ${PSQL} ${PGMONITOR//HOST/localhost}/ schemaname -qtAX --field-separator ' ' -c "SELECT distinct dbrole FROM mapping where enabled = true and hostname='${hostname}'" and adgroup = '${adname}' | while read -a DBRRow ; do ${PSQL} ${PGMONITOR//HOST/$hostname}/performance -qtAX --field-separator ' ' -c "GRANT ${DBRRow} TO ${adname};" done
• 11.
  Synchronisation Approach Database DB_Report DB_Admin DB_Analysts Andrew Smith BobBrown Jenny Reid Andrew Smith Julie Benson Annette Huntly Paul Brown Andrew Smith Bob Brown Jenny Reid Server Members Groups ROLES postgres Andrew Smith Bob Brown Jenny Reid Julie Benson Annette Huntly Paul Brown AppOne DB StarOne DB ID Hostname Dbname ADGroup Dbrole Enabled CRUD 1 Serverone AppOne DB_Report Read_only True F,T,F,F 2 Serverone StarOne DB_Report Reporting True F,T,T,F 3 Serverone AppOne DB_Admin Admin_user True T,T,T,T 4 Serverone StarOne DB_Analysts Analyst True F,T,F,F 5 ${PSQL} ${DBMONITOR//HOST/localhost}/schemaname -qtAX --field-separator ' ' -c "SELECT distinct adgroup FROM mapping WHERE enabled = true and hostname='${hostname}'" | while read -a ADRow ; do for adname in $(ldapsearch -Z -LLL -h ldapservername -D gevans@ldapservername -w password -b dc=ad,dc=companyname,dc=aus '(&(objectClass=user)(memberOf=CN='"${ADRow[0]}"',OU=SQL,OU=Groups,OU=AU,DC=ad,DC=ccompanyn ame,DC=aus))' sAMAccountName | sed -e 's/sAMAccountName: ([a-zA-Z]*)/1/;tx;d;:x') do ${PSQL} ${PGMONITOR//HOST/$hostname}/ schemaname -qtAX --field-separator ' ' -c "CREATE ROLE ${adname} with LOGIN;“ ${PSQL} ${PGMONITOR//HOST/localhost}/performance -qtAX --field-separator ' ' -c "SELECT distinct dbrole FROM mapping where enabled = true and hostname='${hostname}'" and adgroup = '${adname}' | while read -a DBRRow ; do ${PSQL} ${PGMONITOR//HOST/$hostname}/ schemaname -qtAX --field-separator ' ' -c "GRANT ${DBRRow} TO ${adname};" done
• 12.
  Synchronisation Approach Database DB_Report DB_Admin DB_Analysts Andrew Smith BobBrown Jenny Reid Andrew Smith Julie Benson Annette Huntly Paul Brown Andrew Smith Bob Brown Jenny Reid Members Groups ROLES postgres Andrew Smith Bob Brown Jenny Reid Julie Benson Annette Huntly Paul Brown AppOne DB StarOne DB
• 13.
  Synchronisation Approach Database DB_Report DB_Admin DB_Analysts Andrew Smith BobBrown Jenny Reid Andrew Smith Julie Benson Annette Huntly Paul Brown Andrew Smith Bob Brown Jenny Reid Members Groups ROLES Postgres (SU) appOneOwner (SU) StarOneOwner (SU) Andrew Smith Bob Brown Jenny Reid Julie Benson Annette Huntly Paul Brown AppOne DB StarOne DB Owner Owner
• 14.
  Synchronisation Approach Database DB_Report DB_Admin DB_Analysts Andrew Smith BobBrown Jenny Reid Andrew Smith Julie Benson Annette Huntly Paul Brown Andrew Smith Bob Brown Jenny Reid Members Groups ROLES Postgres (SU) appOneOwner (SU) StarOneOwner (SU) DB_Report (NL) DB_Admin (NL) DB_Analysts (NL) Andrew Smith Bob Brown Jenny Reid Julie Benson Annette Huntly Paul Brown AppOne DB StarOne DB Owner Owner Role inheritance
• 15.
  Synchronisation Approach Database DB_Report DB_Admin DB_Analysts Andrew Smith BobBrown Jenny Reid Andrew Smith Julie Benson Annette Huntly Paul Brown Andrew Smith Bob Brown Jenny Reid Members Groups ROLES Postgres (SU) appOneOwner (SU) StarOneOwner (SU) DB_Report (NL) DB_Admin (NL) DB_Analysts (NL) Andrew Smith Bob Brown Jenny Reid Julie Benson Annette Huntly Paul Brown AppOne DB StarOne DB Owner Owner Role inheritance
• 16.
  Synchronisation Approach Database DB_Report DB_Admin DB_Analysts Andrew Smith BobBrown Jenny Reid Andrew Smith Julie Benson Annette Huntly Paul Brown Andrew Smith Bob Brown Jenny Reid Members Groups ROLES Postgres (SU) appOneOwner (SU) StarOneOwner (SU) DB_Report (NL) DB_Admin (NL) DB_Analysts (NL) Andrew Smith Bob Brown Jenny Reid Julie Benson Annette Huntly Paul Brown AppOne DB StarOne DB Owner Owner Role inheritance
• 17.
  Existing tool Pg-ldap-sync