REVISED PATCH [REVIEW:3-5,3-5-1] PostgreSQL regression wrt 3.5.0: privileges via groups

Forgot to regenerate the patch after last-minute fix before sending
email... Missing closing parenthesis in previous patch. New patch
attached.

On Mon, Feb 27, 2012 at 01:41:21PM +0100, Lionel Elie Mamane wrote:
> Attached patch fixes fdo#46675, a regression in 3.5.1rc1 wrt to 3.5.0
> introduced in the fix for fdo#45254. It is a backport of my
> corresponding commit in master; in 3.5.1 only getTablePrivileges is
> affected, not getColumnPrivileges.
>
> A PostgreSQL role can be member of another role. Think of the first
> role as a user and of the second role as a group; a role can be both a
> user and a group.
>
> PostgreSQL-SDBC in LibreOffice 3.5.1 leads the rest of the system to
> ignore privileges (permissions) given to a user via a group
> membership. This has the consequence that Base (e.g. in a form) won't
> allow the user to make things he is allowed to do: e.g. edit data,
> insert new data, ...: The corresponding UI elements are locked /
> greyed out, in the case that the user does not have that privilege
> directly, but "only" via a group membership.
>
> The bug has a testcase, but one needs a PostgreSQL server to test.
>
> The patch duplicates every privilege description line given to a role
> (group) for each member of that role, by doing a cross-product with
> every existing role, and restricting to rows such that the role is a
> member of the grantee group. PUBLIC is the special role "anyone".
> "pg_has_role(pr.oid, dp.grantee, 'USAGE')" is true if and only of
> pr.oid is a member of dp.grantee; it is false otherwise.
>
>
> Please apply to libreoffice-3-5 and libreoffice-3-5-1.
From 72e2ca2d1e915cc998ae286ede8b47eae2b45b09 Mon Sep 17 00:00:00 2001
From: Lionel Elie Mamane <lionel@mamane.lu>
Date: Mon, 27 Feb 2012 13:10:40 +0100
Subject: [PATCH] fdo#46675: expand group memberships in PostgreSQL-SDBC
 get*Privileges

---
 .../drivers/postgresql/pq_databasemetadata.cxx     |    8 +++++---
 1 files changed, 5 insertions(+), 3 deletions(-)

diff --git a/connectivity/source/drivers/postgresql/pq_databasemetadata.cxx 
b/connectivity/source/drivers/postgresql/pq_databasemetadata.cxx
index bfc7be0..6b6b8fe 100644
--- a/connectivity/source/drivers/postgresql/pq_databasemetadata.cxx
+++ b/connectivity/source/drivers/postgresql/pq_databasemetadata.cxx
@@ -1732,7 +1732,8 @@ static void columnMetaData2DatabaseTypeDescription(
 
     rtl::OUStringBuffer sSQL(260);
     sSQL.append( ASCII_STR(
-            " SELECT * FROM ("
+            " SELECT dp.TABLE_CAT, dp.TABLE_SCHEM, dp.TABLE_NAME, dp.GRANTOR, pr.rolname AS 
GRANTEE, dp.privilege, dp.is_grantable "
+            " FROM ("
             "  SELECT table_catalog AS TABLE_CAT, table_schema AS TABLE_SCHEM, table_name,"
             "         grantor, grantee, privilege_type AS PRIVILEGE, is_grantable"
             "  FROM information_schema.table_privileges") );
@@ -1754,8 +1755,9 @@ static void columnMetaData2DatabaseTypeDescription(
             "  WHERE c.relkind IN ('r', 'v') AND c.relacl IS NULL AND pg_has_role(rg.oid, 
c.relowner, 'USAGE')"
             "        AND c.relowner=ro.oid AND c.relnamespace = pn.oid") );
     sSQL.append( ASCII_STR(
-            " ) s"
-            " WHERE table_schem LIKE ? AND table_name LIKE ? "
+            " ) dp,"
+            " (SELECT oid, rolname FROM pg_catalog.pg_roles UNION ALL VALUES (0, 'PUBLIC')) pr"
+            " WHERE table_schem LIKE ? AND table_name LIKE ? AND (dp.grantee = 'PUBLIC' OR 
pg_has_role(pr.oid, dp.grantee, 'USAGE'))"
             " ORDER BY table_schem, table_name, privilege" ) );
 
     Reference< XPreparedStatement > statement = m_origin->prepareStatement( 
sSQL.makeStringAndClear() );
-- 
1.7.7.3