Does the current user have UPDATE privilege on FOO? - Mailing list pgsql-general

Hi,

I'm trying to understand the "clean" way to determine whether the
current role has UPDATE (or SELECT or DELETE or UPDATE) privileges on
a specific table (or column). If I can do it in a way that is portable
across different DBMSs, even better :)

I went through several iterations, 'CUR_USER' being replaced by the
name of the current user (the one the application authenticated with
to PostgreSQL):

 SELECT * FROM information_schema.table_privileges WHERE
   table_schema='foo' AND table_name='bar' AND grantee='CUR_USER'

This misses the privileges given to a role (group) the current role is
a member of (false negative).

 SELECT * FROM information_schema.table_privileges WHERE
   table_schema='foo' AND table_name='bar'

Is also not OK, because it also contains privileges that the current
user granted to other users, privileges it does not necessarily have
itself, so false positives :-|

I tried mucking around with enabled_roles, something like:

 SELECT * FROM information_schema.table_privileges WHERE
   table_schema='foo' AND table_name='bar' AND
   grantee IN (SELECT role_name FROM information_schema.enabled_roles);

But as explained below, that is inconvenient in the general framework
of the application because I can't do "a similar thing" when given
only a JDBC/ODBC interface.

I currently use something like (modulo a workaround for bug in PostgreSQL
< 9.2 that the default ACL is not taken into account when no ACL on object):

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

(On Postgresql < 9.2, I add to the definition of dp a "UNION ALL"
 giving full permissions to the owner on all objects that don't have
 an ACL.)

For reasons internal to the application, I execute this SQL and then
filter (in the application) the entries that have
grantee='CUR_USER'. Is there a better / cleaner / more canonical way?

Full background: I'm taking care of the native PostgreSQL driver for
LibreOffice, the successor to OpenOffice.org. The driver exposes a
JDBC-like interface to the rest of the system, and the above is taken
from its "getTablePrivileges" interface method. The filtering is done
in the driver-agnostic part of LibreOffice, which has to also work
with JDBC drivers and ODBC drivers.

So the "direct" way with information_schema.enabled_roles is not
feasible, since ODBC/JDBC don't give me an interface to (an equivalent
of) enabled_roles :-|


Do you have a better suggestion than what I do now, what to do in the
PostgreSQL driver and what to do in the driver-agnostic part for
maximum compatibility (across DMBSs, across different PostgreSQL
versions, ...)?


The reason we want see whether a user has a given privilege is to
enable or disable the corresponding UI elements that allow the user to
edit / delete / insert data in / from / into the table, in GUI forms
and datasheets and such.

--
Lionel