-- $Id: aclitem_rows.sql,v 1.7 2004/04/12 10:13:12 coelho Exp $

-- composite type for aclitem entries
CREATE TYPE aclitem_row AS
 (object OID,        -- object id
  idtype INTEGER,    -- public=0, user=1, group=2
  grantee INTEGER,   -- user id or group id or nothing
  grantor INTEGER,   -- user id who gave it
  privs INTEGER,     -- 15 bits of privileges
  goptions INTEGER); -- 15 bits of grant options

CREATE OR REPLACE FUNCTION aclitem_as_rows(TEXT, TEXT, TEXT, TEXT, INTEGER)
RETURNS SETOF aclitem_row AS '
DECLARE
  table ALIAS FOR $1;
  object ALIAS FOR $2;
  owner ALIAS FOR $3;
  acl ALIAS FOR $4;
  allrights ALIAS FOR $5;
  res aclitem_row;
  i INTEGER;
  r RECORD;
BEGIN
  FOR r IN EXECUTE 
    \'SELECT \' 
    || object || \' AS object,\'
    || owner || \' AS owner,\'
    || acl || \' AS acl FROM \' || table 
  LOOP
    res.object := r.object;
    IF r.acl IS NOT NULL THEN
      -- everything is explicit?
      FOR i IN array_lower(r.acl,1) .. array_upper(r.acl, 1) LOOP
        res.idtype := aclitem_idtype(r.acl[i]);
        res.grantee := aclitem_grantee(r.acl[i]);
	IF res.idtype = 0 THEN
	  res.grantee = NULL;
        END IF;
        res.grantor := aclitem_grantor(r.acl[i]);
        res.privs := aclitem_privs(r.acl[i]);
        res.goptions := aclitem_goptions(r.acl[i]);
        RETURN NEXT res;
      END LOOP;
    ELSE
       -- owner has all?
       res.idtype := 1;
       res.grantee := r.owner;
       res.grantor := r.owner;
       res.privs := allrights;
       res.goptions := allrights;
       RETURN NEXT res;

       -- public has nope? default?
       res.idtype := 0;
       res.grantee := NULL;
       res.grantor := r.owner;
       res.privs := 0;
       res.privs := 0;
       RETURN NEXT res;
    END IF;
  END LOOP;
  RETURN;
END;'
LANGUAGE plpgsql;

-- row acls...
CREATE VIEW pg_database_acl AS
SELECT * 
FROM aclitem_as_rows('pg_database', 'oid', 'datdba', 'datacl', 1536);

CREATE VIEW pg_class_acl AS
SELECT * 
FROM aclitem_as_rows('pg_class', 'oid', 'relowner', 'relacl', 127);

CREATE VIEW pg_namespace_acl AS
SELECT * 
FROM aclitem_as_rows('pg_namespace', 'oid', 'nspowner', 'nspacl', 768);

CREATE VIEW pg_language_acl AS
SELECT *
FROM aclitem_as_rows('pg_language', 'oid', '1', 'lanacl', 256);

CREATE VIEW pg_proc_acl AS
SELECT *
FROM aclitem_as_rows('pg_proc', 'oid', 'proowner', 'proacl', 128);

-- 
CREATE VIEW pg_public_acl AS
SELECT object, grantor, privs, goptions
FROM pg_database_acl
WHERE idtype = 0
UNION ALL
SELECT object, grantor, privs, goptions
FROM pg_class_acl
WHERE idtype = 0
UNION ALL
SELECT object, grantor, privs, goptions
FROM pg_namespace_acl
WHERE idtype = 0
UNION ALL
SELECT object, grantor, privs, goptions
FROM pg_language_acl
WHERE idtype = 0
UNION ALL
SELECT object, grantor, privs, goptions
FROM pg_proc_acl
WHERE idtype = 0;

--
CREATE VIEW pg_user_acl AS
SELECT object, grantee, grantor, privs, goptions
FROM pg_database_acl
WHERE idtype = 1
UNION ALL
SELECT object, grantee, grantor, privs, goptions
FROM pg_class_acl
WHERE idtype = 1
UNION ALL
SELECT object, grantee, grantor, privs, goptions
FROM pg_namespace_acl
WHERE idtype = 1
UNION ALL
SELECT object, grantee, grantor, privs, goptions
FROM pg_language_acl
WHERE idtype = 1
UNION ALL
SELECT object, grantee, grantor, privs, goptions
FROM pg_proc_acl
WHERE idtype = 1;

--
CREATE VIEW pg_group_acl AS
SELECT object, grantee, grantor, privs, goptions
FROM pg_database_acl
WHERE idtype = 2
UNION ALL
SELECT object, grantee, grantor, privs, goptions
FROM pg_class_acl
WHERE idtype = 2
UNION ALL
SELECT object, grantee, grantor, privs, goptions
FROM pg_namespace_acl
WHERE idtype = 2
UNION ALL
SELECT object, grantee, grantor, privs, goptions
FROM pg_language_acl
WHERE idtype = 2
UNION ALL
SELECT object, grantee, grantor, privs, goptions
FROM pg_proc_acl
WHERE idtype = 2;

CREATE VIEW pg_granted_acl(usesysid, object, grantor, privs, goptions) AS
SELECT u.usesysid, a.object, a.grantor, a.privs, a.goptions
FROM pg_user AS u, pg_public_acl AS a
UNION ALL
SELECT u.usesysid, a.object, a.grantor, a.privs, a.goptions
FROM pg_user AS u, pg_group AS g, pg_group_acl AS a
WHERE g.grosysid = a.grantee
  AND u.usesysid = ANY (g.grolist)
UNION ALL
SELECT u.usesysid, a.object, a.grantor, a.privs, a.goptions
FROM pg_user AS u, pg_user_acl AS a
WHERE u.usesysid = a.grantee;

-- rights of every user on every object
CREATE VIEW pg_acl AS
SELECT
  usesysid,
  object,
  BINARY_OR(privs) AS privs,
  BINARY_OR(goptions) AS goptions
FROM pg_granted_acl
GROUP BY usesysid, object;

-- show
-- SELECT * FROM pg_database_acl;
-- SELECT * FROM pg_class_acl;
-- SELECT * FROM pg_namespace_acl;
-- SELECT * FROM pg_language_acl;
-- SELECT * FROM pg_proc_acl;
-- SELECT * FROM pg_public_acl;
-- SELECT * FROM pg_user_acl;
-- SELECT * FROM pg_group_acl;