Re: Does the current user have UPDATE privilege on FOO?

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(at)mamane(dot)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