-- -- -- 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;