Dear committers, dear hackers,
> Subject: Re: [COMMITTERS] pgsql-server/src backend/utils/adt/acl.c ...
> > Ergo, my recommendation is to revert this change altogether. Fabien
> > should figure out the high-level description of what he wants to know
Please find attached as somehow requested a plpgsql implementation for a
"high-level description" (by that, I understand "relationnal", not
"functionnal") of acl in postgres.
The pg_{database,class,namespace,language,proc}_acl views are just
intermediate for the construction of the description from current acl
implementation. I'm not sure the implementation is right about the default
settings, but the spirit is there.
The actual descriptions are pg_{public,group,user}_acl, and pg_granted_acl
and pg_acl are examples of how to use these "high level descriptions".
You may notice that "high level" means two different things. High level
functions from the back-end point of view (has_privileves stuff), and high
level relationnal (something you can query). I need the second stuff.
Also, I must admit that I don't find it really motivating to have to
reimplement all this in C and to have it rejected for some reason such as
"we may change things in this area in some hypothetical future time", as
it was the motivation for rejecting 10 lines of code for 5 aclitem
accessor functions.
A general comment about pg_catalog is that it looks like it was designed
by a C programmer and cast later as an afterthought to a relationnal view.
It makes it quite uneasy to manipulate these tables for any other purpose
that the one that was foreseen by the designer from its internal point of
view, especially as it is not normalized and as opaque types are used.
Anyway, thanks in advance for your comments about this description, and
suggestions about the probability of acceptance it could have (if
implemented properly in C) in the backend, so as to replace quite infamous
aclitem accessors.
Have a nice day,
--
Fabien Coelho - [EMAIL PROTECTED]-- $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;
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
