CODEWITHSUNDEEP
privilegespostgresql
MiB
MiB

Reputation: 143

How to check permissions to functions under psql console

Could you tell me please how to check permissions to functions with psql console but without being overwhelmed with source code and descirption (like when using \df+).

Upvotes: 14

Views: 15974

Answers (2)

Amir Ali Akbari
Amir Ali Akbari

Reputation: 6396

For a simpler query, use:

SELECT proacl FROM pg_proc WHERE proname='FUNCTION-NAME';

The results is like:

                           proacl                       
----------------------------------------------------
 {=X/postgres,postgres=X/postgres,test1=X/postgres}
(1 row)

which shows that test1 user also has access to this function.

For more details, see the discussion on psql's mailing list: psql missing feature: show permissions for functions.

Upvotes: 10

pcent
pcent

Reputation: 2029

You could query the system tables:

SELECT proname, rolname
  FROM pg_proc pr,
       pg_type tp,
       pg_authid id
 WHERE proowner = id.oid
   AND tp.oid = pr.prorettype
   AND pr.proisagg = FALSE
   AND tp.typname <> 'trigger'
   AND pr.pronamespace IN (
       SELECT oid
         FROM pg_namespace
        WHERE nspname NOT LIKE 'pg_%'
          AND nspname != 'information_schema'
);

Upvotes: 1

Related Questions