How to get the permissions assigned to user?

In response to Jignesh Shah :

Hi,
�
Is there any way to get the set of permissions list assigned to user? I want to
know�whether user has create table permissions on particular schema or not?

There are a lot of functions for that, read:
http://www.postgresql.org/docs/8.4/interactive/functions-info.html
Table 9-48. Access Privilege Inquiry Functions

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99

On Tue, Feb 23, 2010 at 23:22, Jignesh Shah <jignesh.shah1980@gmail.com> wrote:

Hi,

Is there any way to get the set of permissions list assigned to user? I want
to know whether user has create table permissions on particular schema or
not?

See http://www.postgresql.org/docs/8.4/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE.

You can of course troll through the system tables... You might find
psql -E useful if as it will show you the queries psql runs for the
backslash commands'\d').

For example:

$ psql -E
=>\dpn
Schema | Name | Type | Access privileges |
Column access privileges
--------+------+-------+------------------------------------------+--------------------------
public | a | table | | logged_session=arwdDxt/guy |
: read_only=r/guy

gives me the sql:
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'S'
THEN 'sequence' END as "Type",
pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",
pg_catalog.array_to_string(ARRAY(
SELECT attname || E':\n ' || pg_catalog.array_to_string(attacl, E'\n ')
FROM pg_catalog.pg_attribute a
WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
), E'\n') AS "Column access privileges"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'v', 'S') ORDER BY 1, 2;