Re: where table privileges are stored

On Thu, May 7, 2015 at 12:07 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> * Markova, Nina (Nina(dot)Markova(at)NRCan-RNCan(dot)gc(dot)ca) wrote:
> > I need to check in a GUI if a user has certain privileges on the table
> he is trying to modify - insert/update/delete.
>
> You probably want to use the "has_table_privilege" family of functions.
>
> Look here: http://www.postgresql.org/docs/9.4/static/functions-info.html
>
> > Something I could query system catalogs about:
> > Select ... from ... where table_name = 'MYTABLE';
> >
> > Does Postgres store user privileges on tables in format similar to the
> one below, I mean the info below to be stored as a text field somewhere?
> > grant delete on MYTABLE to myuser
> > grant insert on MYTABLE to myuser
>
> Privileges are stored in the catalog tables but not is a terribly useful
> format for querying, which is why the helper functions exist. If you
> want to look at it though, look at pg_class.relacl.
>

Another option for tables, is querying information_schema.table_privileges.
There are other views for privileges:

* information_schema.column_privileges
* information_schema.routine_privileges
* information_schema.table_privileges
* information_schema.udt_privileges
* information_schema.usage_privileges
* information_schema.data_type_privileges

But I don't think they supply information about all possible
objects/privileges available in the system.

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres