Postgresql – Postgres get function owner

access-controlfunctionspostgresqlrolestored-procedures

I would like to be able to get a table of function names and function owner names.

Currently, this query returns a list of functions for a given schema:

  SELECT  proname
  FROM    pg_catalog.pg_namespace n
  JOIN    pg_catalog.pg_proc p
  ON      pronamespace = n.oid
  WHERE   nspname = '<%= schema_name %>';

I can see there is a proowner column which returns the ID of the role.
https://www.postgresql.org/docs/10/catalog-pg-proc.html

What I would like is something that returns the name of the owner, I just don't know enough SQL yet to figure out how to JOIN the tables etc..

|function_name|function_owner|
______________________________
|func1        |func1_owner   |
|func2        |func2_owner   |

If someone could explain how to do this I would be very grateful!

Thanks

Best Answer

There is a simple and a complicated way.

The complicated way is to join with pg_roles and get the user name from there.

But since PostgreSQL hackers don't want to type more than necessary, they came up with something else:

Internally, every object is not identified by its name (which is mutable) but by its “object ID”. This is the number that is for example used in the proowner column of pg_proc.

Now there are the so-called “object identifier types”. Internally such a type is just the oid, but the type output function, which is used for display of the type, renders it as the object's name.

Put that together with PostgreSQL's type cast operator ::, and you can do:

SELECT proname,
       proowner::regrole
FROM pg_proc
WHERE pronamespace::regnamespace::text = 'public';

Additional hint: maybe you would prefer oid::regprocedure over proname.

Related Solutions

Postgresql – Is it possible to wrap aggregate functions in Postgres

For all I know, you cannot wrap an aggregate function. You have to write your own aggregate function.

If you write your own function, you may run into incompatibilies when upgrading or porting to another database. So, personally, I wouldn't bother and just add the delimiter to string_agg() on every call. It really isn't that much of a pain.

That said, it's not that complicated to write your own aggregate function, either:

A custom final function:

CREATE FUNCTION f_str_agg_final(anyarray)
    RETURNS text LANGUAGE SQL AS
$func$SELECT array_to_string($1, ', ')$func$;

The aggregate:

CREATE AGGREGATE f_str_agg (anyelement) (
    SFUNC     = array_append
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
   ,FINALFUNC = f_str_agg_final
);

Call:

SELECT f_str_agg (kat) from tbl;

Works for almost any type - except for array types. You would have to modify your aggregate for this. More in this closely related answer on SO.

Postgresql – How to get all roles that a user is a member of (including inherited roles)

You can query the system catalog with a recursive query, in particular pg_auth_members:

WITH RECURSIVE cte AS (
   SELECT oid FROM pg_roles WHERE rolname = 'maxwell'

   UNION ALL
   SELECT m.roleid
   FROM   cte
   JOIN   pg_auth_members m ON m.member = cte.oid
   )
SELECT oid, oid::regrole::text AS rolename FROM cte;  -- oid & name

The manual about the cast to object identifier type regrole.

BTW 1: INHERIT is the default behavior of CREATE ROLE and doesn't have to be spelled out.

BTW 2: circular dependencies are not possible. Postgres disallows that. So we don't have to check for that.

Best Answer

Related Solutions

Postgresql – Is it possible to wrap aggregate functions in Postgres

Postgresql – How to get all roles that a user is a member of (including inherited roles)

Related Question