Обсуждение: Does the current user have UPDATE privilege on FOO?

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

On Mon, Feb 27, 2012 at 12:11:23PM -0600, Mike Blackwell wrote:
> On Mon, Feb 27, 2012 at 11:00, Lionel Elie Mamane <lionel@mamane.lu> wrote:

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

> For PostgreSQL only, see
> http://www.postgresql.org/docs/9.1/interactive/functions-info.html for a
> list of functions for this.

Ah yes, so basically I can implement the JDBC-alike getTablePrivileges
as something like:

 SELECT (...), tables.table_name, pr.rolname AS grantee,
        has_table_privilege(pr.oid, tables.table_schema, + '.' + tables.table_name, privilege.name), (...)
 FROM information_schema.tables, pg_catalog.pg_roles pr,
      VALUES ('SELECT', 'UPDATE', ...) AS privilege

I'd be missing a sensible value for the grantor column, but at least
it would be complete and robust for the rest of the information.

Thanks for the pointer.

--
Lionel