4 CREATE SEQUENCE user_unix_id_seq START 20000;
5 CREATE SEQUENCE group_unix_id_seq START 50000;
6 UPDATE users SET unix_uid=0,unix_gid=0,shell='/bin/cvssh.pl';
7 ALTER TABLE users ALTER COLUMN shell SET default '/bin/cvssh.pl';
8 UPDATE users SET unix_uid=nextval('user_unix_id_seq'),unix_gid=currval('user_unix_id_seq')
9 WHERE user_id IN (SELECT user_id FROM user_group);
10 ALTER TABLE groups ADD COLUMN unix_gid int;
11 ALTER TABLE groups SET DEFAULT nextval('group_unix_id_seq');
12 UPDATE groups SET unix_gid=nextval('group_unix_id_seq');
14 DROP FUNCTION userunixid_func() CASCADE;
15 CREATE OR REPLACE FUNCTION userunixid_func() RETURNS TRIGGER AS '
19 FOR newuser IN SELECT unix_uid FROM users WHERE user_id=NEW.user_id LOOP
20 IF newuser.unix_uid=0 THEN
21 UPDATE users SET unix_uid=nextval(''user_unix_id_seq''),
22 unix_gid=currval(''user_unix_id_seq'')
23 WHERE user_id=NEW.user_id;
30 CREATE TRIGGER usergroup_insert_userunixid AFTER INSERT ON user_group
31 FOR EACH ROW EXECUTE PROCEDURE userunixid_func();
37 CREATE VIEW nss_passwd AS
48 WHERE STATUS='A' AND EXISTS (SELECT user_id
49 FROM user_group WHERE user_id=users.user_id AND cvs_flags IN (0,1));
52 -- Shadow view (for future use)
55 CREATE VIEW nss_shadow AS
59 CHAR(1) 'n' AS expired,
60 CHAR(1) 'n' AS pwchange
62 WHERE STATUS='A' AND EXISTS (SELECT user_id
63 FROM user_group WHERE user_id=users.user_id AND cvs_flags IN (0,1));
66 -- Extracted from group information
68 DROP TABLE nss_groups;
70 CREATE VIEW nss_groups AS
71 -- SELECT user_id,0,user_name AS NAME, unix_gid
73 -- WHERE status = 'A' AND EXISTS (SELECT user_id
74 -- FROM user_group WHERE user_id=users.user_id AND cvs_flags IN (0,1));
76 SELECT 0 AS user_id, group_id,unix_group_name AS name, unix_gid AS gid
81 DROP TABLE nss_usergroups ;
82 DROP VIEW nss_usergroups;
83 CREATE VIEW nss_usergroups AS
85 users.unix_uid AS uid,
86 groups.unix_gid AS gid,
87 users.user_id AS user_id,
88 groups.group_id AS group_id,
89 users.user_name AS user_name,
90 groups.unix_group_name AS unix_group_name
91 FROM users,groups,user_group
93 users.user_id=user_group.user_id
95 groups.group_id=user_group.group_id
100 AND user_group.cvs_flags IN (0,1);
102 --create index nssusergroup_gidusername ON nss_usergroups(gid,user_name);
103 --create index nssusergroup_usernamegid ON nss_usergroups(user_name,gid);
104 create index users_uid on users(unix_uid);
105 create index users_gid on users(unix_gid);
106 create index groups_gid on groups (unix_gid);
107 grant select on nss_passwd to cvsuser;
108 grant select on nss_usergroups to cvsuser;
109 grant select on nss_groups to cvsuser;
110 grant select on nss_shadow to cvsuser;