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
|
--
--
--
CREATE SEQUENCE user_unix_id_seq START 20000;
CREATE SEQUENCE group_unix_id_seq START 50000;
UPDATE users SET unix_uid=0,unix_gid=0,shell='/bin/cvssh.pl';
ALTER TABLE users ALTER COLUMN shell SET default '/bin/cvssh.pl';
UPDATE users SET unix_uid=nextval('user_unix_id_seq'),unix_gid=currval('user_unix_id_seq')
WHERE user_id IN (SELECT user_id FROM user_group);
ALTER TABLE groups ADD COLUMN unix_gid int;
ALTER TABLE groups SET DEFAULT nextval('group_unix_id_seq');
UPDATE groups SET unix_gid=nextval('group_unix_id_seq');
DROP FUNCTION userunixid_func() CASCADE;
CREATE OR REPLACE FUNCTION userunixid_func() RETURNS TRIGGER AS '
DECLARE
newuser RECORD;
BEGIN
FOR newuser IN SELECT unix_uid FROM users WHERE user_id=NEW.user_id LOOP
IF newuser.unix_uid=0 THEN
UPDATE users SET unix_uid=nextval(''user_unix_id_seq''),
unix_gid=currval(''user_unix_id_seq'')
WHERE user_id=NEW.user_id;
END IF;
END LOOP;
RETURN NEW;
END;
' LANGUAGE plpgsql;
CREATE TRIGGER usergroup_insert_userunixid AFTER INSERT ON user_group
FOR EACH ROW EXECUTE PROCEDURE userunixid_func();
--
-- Passwd view
--
DROP VIEW nss_passwd;
CREATE VIEW nss_passwd AS
SELECT
unix_uid AS uid,
unix_gid AS gid,
user_name AS login,
unix_pw AS passwd,
realname AS gecos,
shell,
user_name AS homedir,
status
FROM users
WHERE STATUS='A' AND EXISTS (SELECT user_id
FROM user_group WHERE user_id=users.user_id AND cvs_flags IN (0,1));
--
-- Shadow view (for future use)
--
DROP VIEW nss_shadow;
CREATE VIEW nss_shadow AS
SELECT
user_name AS login,
unix_pw AS passwd,
CHAR(1) 'n' AS expired,
CHAR(1) 'n' AS pwchange
FROM users
WHERE STATUS='A' AND EXISTS (SELECT user_id
FROM user_group WHERE user_id=users.user_id AND cvs_flags IN (0,1));
--
-- Group Table
-- Extracted from group information
--
DROP TABLE nss_groups;
DROP VIEW nss_groups;
CREATE VIEW nss_groups AS
-- SELECT user_id,0,user_name AS NAME, unix_gid
-- FROM users
-- WHERE status = 'A' AND EXISTS (SELECT user_id
-- FROM user_group WHERE user_id=users.user_id AND cvs_flags IN (0,1));
-- UNION
SELECT 0 AS user_id, group_id,unix_group_name AS name, unix_gid AS gid
FROM groups;
--
-- User_Group Table
--
DROP TABLE nss_usergroups ;
DROP VIEW nss_usergroups;
CREATE VIEW nss_usergroups AS
SELECT
users.unix_uid AS uid,
groups.unix_gid AS gid,
users.user_id AS user_id,
groups.group_id AS group_id,
users.user_name AS user_name,
groups.unix_group_name AS unix_group_name
FROM users,groups,user_group
WHERE
users.user_id=user_group.user_id
AND
groups.group_id=user_group.group_id
AND
groups.status = 'A'
AND
users.status = 'A'
AND user_group.cvs_flags IN (0,1);
--create index nssusergroup_gidusername ON nss_usergroups(gid,user_name);
--create index nssusergroup_usernamegid ON nss_usergroups(user_name,gid);
create index users_uid on users(unix_uid);
create index users_gid on users(unix_gid);
create index groups_gid on groups (unix_gid);
grant select on nss_passwd to cvsuser;
grant select on nss_usergroups to cvsuser;
grant select on nss_groups to cvsuser;
grant select on nss_shadow to cvsuser;
|
