1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
|
#!/usr/bin/ruby
#
# Copyright (c) 2004 Peter Palfrader <peter@palfrader.org>
#
# All rights reserved.
#
require "ldap"
require "myldap"
require "yaml"
require 'postgres'
require 'optparse'
$VERBOSE = nil
def show_help(parser, code=0, io=STDOUT)
io.puts parser
exit(code)
end
ARGV.options do |opts|
opts.on_tail("-h", "--help", "Display this help screen") { show_help(opts) }
opts.on("-v", "--verbose" , nil, "Be verbose") { $VERBOSE = 1 }
opts.parse!
end
def fatal(reason)
STDERR.puts reason
exit 1;
end
def query(pg, query)
res = pg.exec(query)
if (res.status != PGresult::TUPLES_OK)
raise PGerror,"FETCH ALL command didn't return tuples properly\n"
end
r = res.result.collect { |tupl|
line = Hash.new
i = 0
tupl.each { |fld|
line[ res.fields[i] ] = fld
i = i +1
}
line
}
res.clear
r
end
@config = YAML::load( File.open( '/etc/noreply/config' ) )
ldap = MyLDAP.new(@config, "ldap2postgres")
ldap_databases = Hash.new
ldap.conn.search2(@config['basedn'], LDAP::LDAP_SCOPE_SUBTREE, '(objectclass=tnPostgreSQLdatabase)').each{ |e|
cn = e['cn'][0]
dn = e['dn'][0]
ldap_databases[ cn ] = Array.new
ldap.conn.search2(dn, LDAP::LDAP_SCOPE_SUBTREE, '(objectclass=tnPostgreSQLuser)').each{ |e2|
uid = e2['uid'][0]
if (uid.index(cn+"_") != 0)
STDERR.puts "User %s in dn: %s has weird name. Supposed to start with '%s_'. Ignoring user."%[uid, dn, cn] unless
dn == 'ou=postgresql,o=system,ou=hosting,%s'%[config['basedn']]
else
ldap_databases[ e['cn'][0] ] << uid
end
}
}
pg = PGconn.connect('', 5432, '', '', 'template1')
pg_databases = query(pg, 'SELECT datname FROM pg_catalog.pg_database').collect { |line| line['datname'] }
pg_users = query(pg, 'SELECT usename FROM pg_catalog.pg_user') .collect { |line| line['usename'] }
pg_groups = query(pg, 'SELECT groname FROM pg_catalog.pg_group') .collect { |line| line['groname'] }
all_databases = ldap_databases.keys + pg_databases
all_databases.uniq!
all_databases.each { |dbname|
if (! ldap_databases.has_key?(dbname) )
puts "Additional database in Postgers: "+dbname unless %w(template0 template1 ogo).include?(dbname)
next
elsif (! pg_databases.include?(dbname))
puts "Creating database and group: "+dbname
pg.exec('CREATE DATABASE "'+dbname+'"')
pg.exec('REVOKE ALL ON DATABASE "'+dbname+'" FROM PUBLIC')
else
puts 'Common database: "'+dbname+'"' if $VERBOSE
end
if (pg_groups.include?(dbname))
puts ' Common group: '+dbname if $VERBOSE
else
puts ' Creating group: '+dbname
pgdb = PGconn.connect('', 5432, '', '', dbname)
pgdb.exec('CREATE GROUP "'+dbname+'"')
pgdb.exec('REVOKE ALL ON SCHEMA public FROM PUBLIC')
pgdb.exec('GRANT ALL ON SCHEMA public TO GROUP "'+dbname+'"')
pgdb.close
end
pg_groups.delete(dbname)
pgdb = PGconn.connect('', 5432, '', '', dbname)
pg_schemas = query(pgdb, 'SELECT nspname FROM pg_catalog.pg_namespace') .collect { |line| line['nspname'] }
ldap_databases[dbname].each { |uid|
if (pg_users.include?(uid))
puts " Common user: "+uid if $VERBOSE
# this does not hurt if the user already is in the group, but if the
# group was droped for some reason and just created now, it readds
# the user to gthe group
pg.exec('ALTER GROUP "'+dbname+'" ADD USER "'+uid+'"')
else
puts " Adding user: "+uid
pg.exec('CREATE USER "'+uid+'" NOCREATEDB NOCREATEUSER IN GROUP "'+dbname+'"')
end
if (pg_schemas.include?(uid))
puts ' Common schema: '+uid if $VERBOSE
else
puts ' Adding schema '+uid
pgdb.exec('CREATE SCHEMA "'+uid+'" AUTHORIZATION "' +uid+'"')
pgdb.exec('REVOKE ALL ON SCHEMA "'+uid+'" FROM PUBLIC')
end
pg_schemas.delete(uid)
pg_users.delete(uid)
}
pg_schemas.each { |schemaname|
puts "Additional schemas: "+schemaname unless %w(pg_toast pg_temp_1 pg_catalog public information_schema).include?(schemaname)
}
pgdb.close
}
pg.close
pg_users.each { |uid|
puts "Additional user: "+uid unless %w(postgres ogo).include?(uid)
}
pg_groups.each { |gid|
puts "Additional group: "+gid
}
|
