From: | Lionel Elie Mamane <lionel(at)mamane(dot)lu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Does the current user have UPDATE privilege on FOO? |
Date: | 2012-02-27 17:00:44 |
Message-ID: | 20120227170044.GB18809@capsaicin.mamane.lu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Ireneusz Pluta | 2012-02-27 17:53:01 | pg_class.relnamespace NOT IN pg_namespace.oid |
Previous Message | James B. Byrne | 2012-02-27 16:51:11 | Having a problem with RoR-3.1.1 and Pg-9.1 |