Thread: psql missing feature: show permissions for functions

Hi,


I was trying to check some permissions on functions and then I noticed
that there's no command in psql to show that information.  I've tried
using "\z", "\df" and "\df+" without success on PostgreSQL 7.4.5.

It would be an interesting addition to have that, as we do have the
commands to show permissions on tables.


Thanks for your attention,
--
Godoy.     <godoy@ieee.org>

> I was trying to check some permissions on functions and then I noticed
> that there's no command in psql to show that information.  I've tried
> using "\z", "\df" and "\df+" without success on PostgreSQL 7.4.5.
>
> It would be an interesting addition to have that, as we do have the
> commands to show permissions on tables.

ah, I found it.  try...

    SELECT proacl FROM pg_proc WHERE proname='functioname';

"John R Pierce" <pierce@hogranch.com> writes:

>> I was trying to check some permissions on functions and then I noticed
>> that there's no command in psql to show that information.  I've tried
>> using "\z", "\df" and "\df+" without success on PostgreSQL 7.4.5.
>> It would be an interesting addition to have that, as we do have the
>> commands to show permissions on tables.
>
> ah, I found it.  try...
>
>    SELECT proacl FROM pg_proc WHERE proname='functioname';


Hi John!


It works.  How does it deal with overloaded functions? :-)  I don't have
any here, yet, but from pg_proc's contents I couldn't find one way to
define exactly what is the function that is authorized for use (we have
to pass the parameters of the function at the command line, otherwise
there's an error:

testbase=# GRANT EXECUTE ON FUNCTION my_function(VARCHAR, VARCHAR) TO dbmanager;
GRANT
testbase=# GRANT EXECUTE ON FUNCTION my_function TO dbmanager;
ERROR:  syntax error at or near "to" at character 41
testbase=# GRANT EXECUTE ON FUNCTION my_functon() TO dbmanager;
ERROR:  function my_function() does not exist
testbase=#

<some more testing>

I found that I can check 'proargtypes' column and compare it with the
definitions I want, but it would be great if there was some psql builtin
macro to do that :-)


This solves my problem from the SQL point of view, but I still think
that psql should have something like that (with the appropriate
proargtypes replacement from numeric to the type name...).


testbase=# SELECT proacl, proargtypes FROM pg_proc WHERE proname='to_ascii';
    proacl     | proargtypes
---------------+-------------
 {=X/postgres} |          25
 {=X/postgres} |       25 19
 {=X/postgres} |       25 23
(3 rows)

testbase=#


Where can I find this '25', '19', '23', etc. name? :-)


Thanks.
--
Godoy.     <godoy@ieee.org>

On Mon, Sep 20, 2004 at 07:35:58PM -0300, Jorge Godoy wrote:

> testbase=# SELECT proacl, proargtypes FROM pg_proc WHERE proname='to_ascii';
>     proacl     | proargtypes
> ---------------+-------------
>  {=X/postgres} |          25
>  {=X/postgres} |       25 19
>  {=X/postgres} |       25 23
> (3 rows)
>
> testbase=#
>
> Where can I find this '25', '19', '23', etc. name? :-)

Those are OIDs from the pg_type catalog.  The format_type(oid, integer)
function can give you the actual name, as in

alvherre=# SELECT proacl, proargtypes, format_type(proargtypes[0], 0),
format_type(proargtypes[1], 0) FROM pg_proc WHERE proname='to_ascii';
    proacl     | proargtypes | format_type | format_type
---------------+-------------+-------------+-------------
 {=X/alvherre} |          25 | text        | -
 {=X/alvherre} |       25 19 | text        | name
 {=X/alvherre} |       25 23 | text        | integer
(3 filas)


The second argument is the type's typmod (the 25 in varchar(25), for
instance).

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"The ability to monopolize a planet is insignificant
next to the power of the source"

Jorge Godoy wrote:
>
> Hi,
>
>
> I was trying to check some permissions on functions and then I noticed
> that there's no command in psql to show that information.  I've tried
> using "\z", "\df" and "\df+" without success on PostgreSQL 7.4.5.
>
> It would be an interesting addition to have that, as we do have the
> commands to show permissions on tables.

I believe this is fixed in 8.0.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073