Hi, the attached patch addresses the performance issues of the authorization related views from information_schema (BUG #4596). It implements what Tom suggests in
http://archives.postgresql.org/pgsql-bugs/2008-12/msg00144.php In the cases that I have tested both the new and the old view return the same data but I'd appreciate more tests. The patch currently does not remove the original views but renames them from xyz to old_xyz, so you can run your own tests of the new view definition vs. the old one. I also include a psql file that might help you testing. Regards, Joachim
diff -cr cvs/src/backend/catalog/information_schema.sql cvs.build/src/backend/catalog/information_schema.sql
*** cvs/src/backend/catalog/information_schema.sql 2009-09-06 08:59:03.000000000 +0200
--- cvs.build/src/backend/catalog/information_schema.sql 2009-09-25 02:25:14.000000000 +0200
***************
*** 480,485 ****
--- 480,552 ----
CREATE VIEW column_privileges AS
SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+ CAST(g_grantee.rolname AS sql_identifier) AS grantee,
+ CAST(current_database() AS sql_identifier) AS table_catalog,
+ CAST(nc.nspname AS sql_identifier) AS table_schema,
+ CAST(relname AS sql_identifier) AS table_name,
+ CAST(attname AS sql_identifier) AS column_name,
+ CAST(prtxt.ptext AS character_data) AS privilege_type,
+ CAST(grantable AS yes_or_no) AS is_grantable
+ FROM (
+ SELECT pr_c.grantor,
+ pr_c.grantee,
+ attname,
+ relname,
+ relnamespace,
+ pr_c.type,
+ CASE WHEN
+ pr_c.is_grantable OR pg_has_role(pr_c.grantee, pr_c.relowner, 'USAGE')
+ THEN 'YES' ELSE 'NO' END as grantable
+ FROM
+ (SELECT oid, relname, relnamespace, relowner, (acls).*
+ FROM (SELECT oid, relname, relnamespace, relowner, aclexplode(relacl) as acls
+ FROM pg_class
+ WHERE relkind IN ('r', 'v')) x
+ ) pr_c (oid, relname, relnamespace, relowner, grantor, grantee, type, is_grantable),
+ pg_attribute a
+ WHERE a.attrelid = pr_c.oid
+ AND a.attnum > 0
+ AND NOT a.attisdropped
+ UNION
+ SELECT pr_a.grantor,
+ pr_a.grantee,
+ attname,
+ relname,
+ relnamespace,
+ pr_a.type,
+ CASE WHEN
+ pr_a.is_grantable OR pg_has_role(pr_a.grantee, c.relowner, 'USAGE')
+ THEN 'YES' ELSE 'NO' END as grantable
+ FROM
+ (SELECT attrelid, attname, (acls).*
+ FROM (SELECT attrelid, attname, aclexplode(attacl) AS acls
+ FROM pg_attribute
+ WHERE attnum > 0
+ AND NOT attisdropped) x
+ ) pr_a (attrelid, attname, grantor, grantee, type, is_grantable),
+ pg_class c
+ WHERE pr_a.attrelid = c.oid
+ AND relkind IN ('r','v')
+ ) x,
+ (SELECT oid, rolname FROM pg_authid
+ UNION ALL
+ SELECT 0::oid, 'PUBLIC'
+ ) AS g_grantee (oid, rolname),
+ pg_authid u_grantor,
+ (VALUES ('a', 'INSERT'),
+ ('r', 'SELECT'),
+ ('w', 'UPDATE'),
+ ('x', 'REFERENCES')) AS prtxt (pchr, ptext),
+ pg_namespace nc
+ WHERE nc.oid = relnamespace
+ AND x.grantee = g_grantee.oid
+ AND x.grantor = u_grantor.oid
+ AND prtxt.pchr = type;
+
+ GRANT SELECT ON column_privileges TO PUBLIC;
+
+ CREATE VIEW old_column_privileges AS
+ SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
CAST(grantee.rolname AS sql_identifier) AS grantee,
CAST(current_database() AS sql_identifier) AS table_catalog,
CAST(nc.nspname AS sql_identifier) AS table_schema,
***************
*** 523,530 ****
OR pg_has_role(grantee.oid, 'USAGE')
OR grantee.rolname = 'PUBLIC');
- GRANT SELECT ON column_privileges TO PUBLIC;
-
/*
* 5.20
--- 590,595 ----
***************
*** 1124,1129 ****
--- 1189,1203 ----
*/
CREATE VIEW role_column_grants AS
+ SELECT column_privileges.*
+ FROM column_privileges
+ JOIN enabled_roles er1 ON grantor = er1.role_name
+ JOIN enabled_roles er2 ON grantee = er2.role_name;
+
+ GRANT SELECT ON role_column_grants TO PUBLIC;
+
+
+ CREATE VIEW old_role_column_grants AS
SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
CAST(g_grantee.rolname AS sql_identifier) AS grantee,
CAST(current_database() AS sql_identifier) AS table_catalog,
***************
*** 1163,1177 ****
AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
- GRANT SELECT ON role_column_grants TO PUBLIC;
-
/*
* 5.39
* ROLE_ROUTINE_GRANTS view
*/
! CREATE VIEW role_routine_grants AS
SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
CAST(g_grantee.rolname AS sql_identifier) AS grantee,
CAST(current_database() AS sql_identifier) AS specific_catalog,
--- 1237,1251 ----
AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
/*
* 5.39
* ROLE_ROUTINE_GRANTS view
*/
! -- ROLE_TABLE_GRANTS view is based on 5.45 ROUTINE_PRIVILEGES and is defined there instead.
!
! CREATE VIEW old_role_routine_grants AS
SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
CAST(g_grantee.rolname AS sql_identifier) AS grantee,
CAST(current_database() AS sql_identifier) AS specific_catalog,
***************
*** 1200,1214 ****
AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
- GRANT SELECT ON role_routine_grants TO PUBLIC;
-
/*
* 5.40
* ROLE_TABLE_GRANTS view
*/
! CREATE VIEW role_table_grants AS
SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
CAST(g_grantee.rolname AS sql_identifier) AS grantee,
CAST(current_database() AS sql_identifier) AS table_catalog,
--- 1274,1288 ----
AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
/*
* 5.40
* ROLE_TABLE_GRANTS view
*/
! -- ROLE_TABLE_GRANTS view is based on 5.60 TABLE_PRIVILEGES and is defined there instead.
!
! CREATE VIEW old_role_table_grants AS
SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
CAST(g_grantee.rolname AS sql_identifier) AS grantee,
CAST(current_database() AS sql_identifier) AS table_catalog,
***************
*** 1243,1250 ****
AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
- GRANT SELECT ON role_table_grants TO PUBLIC;
-
/*
* 5.41
--- 1317,1322 ----
***************
*** 1260,1265 ****
--- 1332,1399 ----
*/
CREATE VIEW role_usage_grants AS
+ /* foreign-data wrappers */
+ SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+ CAST(g_grantee.rolname AS sql_identifier) AS grantee,
+ CAST(current_database() AS sql_identifier) AS object_catalog,
+ CAST('' AS sql_identifier) AS object_schema,
+ CAST(fdw.fdwname AS sql_identifier) AS object_name,
+ CAST('FOREIGN DATA WRAPPER' AS character_data) AS object_type,
+ CAST('USAGE' AS character_data) AS privilege_type,
+ CAST(
+ CASE WHEN
+ pg_has_role(g_grantee.oid, fdw.fdwowner, 'USAGE')
+ OR grantable = 't'
+ THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
+
+ FROM pg_authid u_grantor,
+ pg_authid g_grantee,
+ (
+ SELECT fdwname, fdwowner, (acls).*
+ FROM (SELECT fdwname, fdwowner, aclexplode(fdwacl) AS acls FROM pg_foreign_data_wrapper) x
+ ) AS fdw (fdwname, fdwowner, grantor, grantee, prtype, grantable),
+ (VALUES ('U', 'USAGE')) AS pr (pchr, ptext)
+
+ WHERE u_grantor.oid = fdw.grantor
+ AND g_grantee.oid = fdw.grantee
+ AND pr.pchr = fdw.prtype
+ AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
+ OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles))
+
+ UNION ALL
+
+ /* foreign server */
+ SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+ CAST(g_grantee.rolname AS sql_identifier) AS grantee,
+ CAST(current_database() AS sql_identifier) AS object_catalog,
+ CAST('' AS sql_identifier) AS object_schema,
+ CAST(srv.srvname AS sql_identifier) AS object_name,
+ CAST('FOREIGN SERVER' AS character_data) AS object_type,
+ CAST('USAGE' AS character_data) AS privilege_type,
+ CAST(
+ CASE WHEN
+ pg_has_role(g_grantee.oid, srv.srvowner, 'USAGE')
+ OR grantable = 't'
+ THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
+
+ FROM pg_authid u_grantor,
+ pg_authid g_grantee,
+ (
+ SELECT srvname, srvowner, (acls).*
+ FROM (SELECT srvname, srvowner, aclexplode(srvacl) AS acls FROM pg_foreign_server) x
+ ) AS srv (srvname, srvowner, grantor, grantee, prtype, grantable),
+ (VALUES ('U', 'USAGE')) AS pr (pchr, ptext)
+
+ WHERE u_grantor.oid = srv.grantor
+ AND g_grantee.oid = srv.grantee
+ AND pr.pchr = srv.prtype
+ AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
+ OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
+
+ GRANT SELECT ON role_usage_grants TO PUBLIC;
+
+
+ CREATE VIEW old_role_usage_grants AS
/* foreign-data wrappers */
SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
***************
*** 1313,1320 ****
AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
- GRANT SELECT ON role_usage_grants TO PUBLIC;
-
/*
* 5.43
--- 1447,1452 ----
***************
*** 1339,1344 ****
--- 1471,1523 ----
CREATE VIEW routine_privileges AS
SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+ CAST(g_grantee.rolname AS sql_identifier) AS grantee,
+ CAST(current_database() AS sql_identifier) AS specific_catalog,
+ CAST(n.nspname AS sql_identifier) AS specific_schema,
+ CAST(pr_p.proname || '_' || CAST(pr_p.oid AS text) AS sql_identifier) AS specific_name,
+ CAST(current_database() AS sql_identifier) AS routine_catalog,
+ CAST(n.nspname AS sql_identifier) AS routine_schema,
+ CAST(pr_p.proname AS sql_identifier) AS routine_name,
+ CAST('EXECUTE' AS character_data) AS privilege_type,
+ CAST(
+ CASE WHEN
+ pg_has_role(g_grantee.oid, pr_p.proowner, 'USAGE') OR is_grantable = 't'
+ THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
+ FROM pg_namespace n,
+ (
+ SELECT oid, proname, proowner, pronamespace, (acls).*
+ FROM (SELECT oid, proname, proowner, pronamespace, aclexplode(proacl) as acls FROM pg_proc) x
+ ) pr_p (oid, proname, proowner, pronamespace, grantor, grantee, type, is_grantable),
+ (
+ SELECT oid, rolname FROM pg_authid
+ UNION ALL
+ SELECT 0::oid, 'PUBLIC'
+ ) AS g_grantee (oid, rolname),
+ pg_authid u_grantor,
+ (VALUES ('X', 'EXECUTE')) AS prtxt (pchr, ptext)
+ WHERE pr_p.pronamespace = n.oid
+ AND prtxt.pchr = pr_p.type
+ AND g_grantee.oid = pr_p.grantee
+ AND u_grantor.oid = pr_p.grantor
+ AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
+ OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
+
+ GRANT SELECT ON routine_privileges TO PUBLIC;
+
+
+ -- This is 5.39 ROLE_ROUTINE_GRANTS view which is based on ROUTINE_PRIVILEGES
+
+ CREATE VIEW role_routine_grants AS
+ SELECT routine_privileges.*
+ FROM routine_privileges
+ JOIN enabled_roles er1 ON grantor = er1.role_name
+ JOIN enabled_roles er2 ON grantee = er2.role_name;
+
+ GRANT SELECT ON role_routine_grants TO PUBLIC;
+
+
+ CREATE VIEW old_routine_privileges AS
+ SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
CAST(grantee.rolname AS sql_identifier) AS grantee,
CAST(current_database() AS sql_identifier) AS specific_catalog,
CAST(n.nspname AS sql_identifier) AS specific_schema,
***************
*** 1371,1378 ****
OR pg_has_role(grantee.oid, 'USAGE')
OR grantee.rolname = 'PUBLIC');
- GRANT SELECT ON routine_privileges TO PUBLIC;
-
/*
* 5.46
--- 1550,1555 ----
***************
*** 1834,1839 ****
--- 2011,2070 ----
CREATE VIEW table_privileges AS
SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+ CAST(g_grantee.rolname AS sql_identifier) AS grantee,
+ CAST(current_database() AS sql_identifier) AS table_catalog,
+ CAST(nc.nspname AS sql_identifier) AS table_schema,
+ CAST(c.relname AS sql_identifier) AS table_name,
+ CAST(pr.ptext AS character_data) AS privilege_type,
+ CAST(
+ CASE WHEN
+ pg_has_role(g_grantee.oid, c.relowner, 'USAGE') OR grantable = 't'
+ THEN 'YES' ELSE 'NO' END AS yes_or_no
+ ) AS is_grantable,
+ CAST('NO' AS yes_or_no) AS with_hierarchy
+ FROM
+ pg_namespace nc,
+ (
+ SELECT oid, relname, relnamespace, relkind, relowner, (acls).*
+ FROM (SELECT oid, relname, relnamespace, relkind, relowner, aclexplode(relacl) AS acls FROM pg_class) x
+ ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
+ (
+ SELECT oid, rolname FROM pg_authid
+ UNION ALL
+ SELECT 0::oid, 'PUBLIC'
+ ) AS g_grantee (oid, rolname),
+ pg_authid u_grantor,
+ (VALUES ('a', 'INSERT'),
+ ('r', 'SELECT'),
+ ('w', 'UPDATE'),
+ ('d', 'DELETE'),
+ ('D', 'TRUNCATE'),
+ ('x', 'REFERENCES'),
+ ('t', 'TRIGGER')) AS pr (pchr, ptext)
+ WHERE c.relnamespace = nc.oid
+ AND c.relkind IN ('r', 'v')
+ AND c.grantee = g_grantee.oid
+ AND c.grantor = u_grantor.oid
+ AND c.prtype = pr.pchr
+ AND (pg_has_role(u_grantor.oid, 'USAGE')
+ OR pg_has_role(g_grantee.oid, 'USAGE')
+ OR g_grantee.rolname = 'PUBLIC');
+
+ GRANT SELECT ON table_privileges TO PUBLIC;
+
+ -- This is 5.40 ROLE_TABLE_GRANTS view which is based on TABLE_PRIVILEGES
+
+ CREATE VIEW role_table_grants AS
+ SELECT table_privileges.*
+ FROM table_privileges
+ JOIN enabled_roles er1 ON grantor = er1.role_name
+ JOIN enabled_roles er2 ON grantee = er2.role_name;
+
+ GRANT SELECT ON role_table_grants TO PUBLIC;
+
+
+ CREATE VIEW old_table_privileges AS
+ SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
CAST(grantee.rolname AS sql_identifier) AS grantee,
CAST(current_database() AS sql_identifier) AS table_catalog,
CAST(nc.nspname AS sql_identifier) AS table_schema,
***************
*** 1872,1879 ****
OR pg_has_role(grantee.oid, 'USAGE')
OR grantee.rolname = 'PUBLIC');
- GRANT SELECT ON table_privileges TO PUBLIC;
-
/*
* 5.61
--- 2103,2108 ----
***************
*** 2054,2059 ****
--- 2283,2379 ----
*/
CREATE VIEW usage_privileges AS
+ /* domains */
+ -- Domains have no real privileges, so we represent all domains with implicit usage privilege here.
+ SELECT CAST(u.rolname AS sql_identifier) AS grantor,
+ CAST('PUBLIC' AS sql_identifier) AS grantee,
+ CAST(current_database() AS sql_identifier) AS object_catalog,
+ CAST(n.nspname AS sql_identifier) AS object_schema,
+ CAST(t.typname AS sql_identifier) AS object_name,
+ CAST('DOMAIN' AS character_data) AS object_type,
+ CAST('USAGE' AS character_data) AS privilege_type,
+ CAST('NO' AS yes_or_no) AS is_grantable
+
+ FROM pg_authid u,
+ pg_namespace n,
+ pg_type t
+
+ WHERE u.oid = t.typowner
+ AND t.typnamespace = n.oid
+ AND t.typtype = 'd'
+
+ UNION ALL
+
+ /* foreign-data wrappers */
+ SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+ CAST(g_grantee.rolname AS sql_identifier) AS grantee,
+ CAST(current_database() AS sql_identifier) AS object_catalog,
+ CAST('' AS sql_identifier) AS object_schema,
+ CAST(fdw.fdwname AS sql_identifier) AS object_name,
+ CAST('FOREIGN DATA WRAPPER' AS character_data) AS object_type,
+ CAST('USAGE' AS character_data) AS privilege_type,
+ CAST(
+ CASE WHEN
+ pg_has_role(g_grantee.oid, fdw.fdwowner, 'USAGE')
+ OR grantable = 't'
+ THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
+
+ FROM pg_authid u_grantor,
+ (SELECT oid, rolname FROM pg_authid
+ UNION ALL
+ SELECT 0::oid, 'PUBLIC'
+ ) AS g_grantee (oid, rolname),
+ (
+ SELECT fdwname, fdwowner, (acls).*
+ FROM (SELECT fdwname, fdwowner, aclexplode(fdwacl) AS acls FROM pg_foreign_data_wrapper) x
+ ) AS fdw (fdwname, fdwowner, grantor, grantee, prtype, grantable),
+ (VALUES ('U', 'USAGE')) AS pr (pchr, ptext)
+
+ WHERE u_grantor.oid = fdw.grantor
+ AND g_grantee.oid = fdw.grantee
+ AND pr.pchr = fdw.prtype
+ AND (pg_has_role(u_grantor.oid, 'USAGE')
+ OR pg_has_role(g_grantee.oid, 'USAGE')
+ OR g_grantee.rolname = 'PUBLIC')
+
+ UNION ALL
+
+ /* foreign server */
+ SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+ CAST(g_grantee.rolname AS sql_identifier) AS grantee,
+ CAST(current_database() AS sql_identifier) AS object_catalog,
+ CAST('' AS sql_identifier) AS object_schema,
+ CAST(srv.srvname AS sql_identifier) AS object_name,
+ CAST('FOREIGN SERVER' AS character_data) AS object_type,
+ CAST('USAGE' AS character_data) AS privilege_type,
+ CAST(
+ CASE WHEN
+ pg_has_role(g_grantee.oid, srv.srvowner, 'USAGE')
+ OR grantable = 't'
+ THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
+
+ FROM pg_authid u_grantor,
+ (SELECT oid, rolname FROM pg_authid
+ UNION ALL
+ SELECT 0::oid, 'PUBLIC'
+ ) AS g_grantee (oid, rolname),
+ (
+ SELECT srvname, srvowner, (acls).*
+ FROM (SELECT srvname, srvowner, aclexplode(srvacl) AS acls FROM pg_foreign_server) x
+ ) AS srv (srvname, srvowner, grantor, grantee, prtype, grantable),
+ (VALUES ('U', 'USAGE')) AS pr (pchr, ptext)
+
+ WHERE u_grantor.oid = srv.grantor
+ AND g_grantee.oid = srv.grantee
+ AND pr.pchr = srv.prtype
+ AND (pg_has_role(u_grantor.oid, 'USAGE')
+ OR pg_has_role(g_grantee.oid, 'USAGE')
+ OR g_grantee.rolname = 'PUBLIC');
+
+ GRANT SELECT ON usage_privileges TO PUBLIC;
+
+
+ CREATE VIEW old_usage_privileges AS
/* domains */
-- Domains have no real privileges, so we represent all domains with implicit usage privilege here.
***************
*** 2138,2145 ****
OR pg_has_role(grantee.oid, 'USAGE')
OR grantee.rolname = 'PUBLIC');
- GRANT SELECT ON usage_privileges TO PUBLIC;
-
/*
* 5.72
--- 2458,2463 ----
diff -cr cvs/src/backend/utils/adt/acl.c cvs.build/src/backend/utils/adt/acl.c
*** cvs/src/backend/utils/adt/acl.c 2009-09-06 09:06:27.000000000 +0200
--- cvs.build/src/backend/utils/adt/acl.c 2009-09-22 01:34:42.000000000 +0200
***************
*** 24,29 ****
--- 24,30 ----
#include "commands/dbcommands.h"
#include "commands/tablespace.h"
#include "foreign/foreign.h"
+ #include "funcapi.h"
#include "miscadmin.h"
#include "utils/acl.h"
#include "utils/builtins.h"
***************
*** 1491,1496 ****
--- 1492,1600 ----
return result;
}
+ /*
+ * Convert an aclitem[] to a table.
+ *
+ * Example:
+ *
+ * aclexplode('{=r/joe,foo=a*w/joe}'::aclitem[])
+ *
+ * returns the table
+ *
+ * {{ OID(joe), 0::OID, 'r', 'f' },
+ * { OID(joe), OID(foo), 'a', 't' },
+ * { OID(joe), OID(foo), 'w', 'f' }}
+ */
+
+ Datum
+ aclexplode(PG_FUNCTION_ARGS)
+ {
+
+ FuncCallContext *funcctx;
+ int *idx;
+ Datum result;
+ Datum values[4];
+ HeapTuple tuple;
+ bool nulls[4];
+ Acl *acl = PG_GETARG_ACL_P(0);
+ AclItem *aidat;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ TupleDesc tupdesc;
+ MemoryContext oldcontext;
+
+ check_acl(acl);
+
+ funcctx = SRF_FIRSTCALL_INIT();
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /*
+ * build tupdesc for result tuples (matches pg_proc entry)
+ */
+ tupdesc = CreateTemplateTupleDesc(4, false);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "grantor",
+ OIDOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "grantee",
+ OIDOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 3, "privilege_type",
+ TEXTOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 4, "is_grantable",
+ BOOLOID, -1, 0);
+
+ funcctx->tuple_desc = BlessTupleDesc(tupdesc);
+
+ /* allocate memory for user context */
+ idx = (int *) palloc(sizeof(int[2]));
+ idx[0] = 0;
+ idx[1] = -1;
+ funcctx->user_fctx = (void *) idx;
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ funcctx = SRF_PERCALL_SETUP();
+ idx = (int *) funcctx->user_fctx;
+
+ aidat = ACL_DAT(acl);
+ while(1)
+ {
+ idx[1]++;
+ if (idx[1] == N_ACL_RIGHTS)
+ {
+ idx[1] = 0;
+ idx[0]++;
+ if (idx[0] == ACL_NUM(acl))
+ /* done */
+ break;
+ }
+ Assert(idx[0] < ACL_NUM(acl));
+ Assert(idx[1] < N_ACL_RIGHTS);
+ if (ACLITEM_GET_PRIVS(aidat[idx[0]]) & (1 << idx[1]))
+ {
+ char s[2];
+
+ MemSet(nulls, 0, sizeof(nulls));
+ values[0] = ObjectIdGetDatum(aidat[idx[0]].ai_grantor);
+ values[1] = ObjectIdGetDatum(aidat[idx[0]].ai_grantee);
+ s[0] = ACL_ALL_RIGHTS_STR[idx[1]];
+ s[1] = '\0';
+ values[2] = CStringGetTextDatum(s);
+ if (ACLITEM_GET_GOPTIONS(aidat[idx[0]]) & (1 << idx[1]))
+ values[3] = true;
+ else
+ values[3] = false;
+
+ tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
+ result = HeapTupleGetDatum(tuple);
+
+ SRF_RETURN_NEXT(funcctx, result);
+ }
+ }
+
+ SRF_RETURN_DONE(funcctx);
+ }
+
/*
* has_table_privilege variants
diff -cr cvs/src/include/catalog/pg_proc.h cvs.build/src/include/catalog/pg_proc.h
*** cvs/src/include/catalog/pg_proc.h 2009-09-06 09:08:02.000000000 +0200
--- cvs.build/src/include/catalog/pg_proc.h 2009-09-22 01:33:25.000000000 +0200
***************
*** 1312,1317 ****
--- 1312,1318 ----
DATA(insert OID = 1062 ( aclitemeq PGNSP PGUID 12 1 0 0 f f f t f i 2 0 16 "1033 1033" _null_ _null_ _null_ _null_ aclitem_eq _null_ _null_ _null_ ));
DESCR("equality operator for ACL items");
DATA(insert OID = 1365 ( makeaclitem PGNSP PGUID 12 1 0 0 f f f t f i 4 0 1033 "26 26 25 16" _null_ _null_ _null_ _null_ makeaclitem _null_ _null_ _null_ ));
+ DATA(insert OID = 1248 ( aclexplode PGNSP PGUID 12 1 10 0 f f f t t s 1 0 2249 "1034" "{26,26,25,16}" "{o,o,o,o}" "{grantor,grantee,privilege_type,is_grantable}" _null_ aclexplode _null_ _null_ _null_ ));
DESCR("make ACL item");
DATA(insert OID = 1044 ( bpcharin PGNSP PGUID 12 1 0 0 f f f t f i 3 0 1042 "2275 26 23" _null_ _null_ _null_ _null_ bpcharin _null_ _null_ _null_ ));
DESCR("I/O");
diff -cr cvs/src/include/utils/acl.h cvs.build/src/include/utils/acl.h
*** cvs/src/include/utils/acl.h 2009-09-06 09:08:07.000000000 +0200
--- cvs.build/src/include/utils/acl.h 2009-09-19 01:20:07.000000000 +0200
***************
*** 256,261 ****
--- 256,262 ----
extern Datum makeaclitem(PG_FUNCTION_ARGS);
extern Datum aclitem_eq(PG_FUNCTION_ARGS);
extern Datum hash_aclitem(PG_FUNCTION_ARGS);
+ extern Datum aclexplode(PG_FUNCTION_ARGS);
/*
* prototypes for functions in aclchk.c
compare.psql
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
