[PosgreSQL] Problem of permissions on function

postgres-perm-function.md

My problem: impossible to execute the function pg_ls_dir() from the schema pg_catalog with the user racpg:

[pginc@pp-snp-pg11 ~]$ psql --host 10.242.20.5 --port 4900 --user racpg dbnccl01
psql (12.3)
Type "help" for help.

dbnccl01=> SELECT pg_catalog.pg_ls_dir('/tmp/');
ERROR:  permission denied for function pg_ls_dir
dbnccl01=> 

What I have done (tried) to solve this problem:

# The goal is to give EXECUTE permission to the user racpg:
[pginc@pp-snp-pg11 ~]$ psql -x postgres
psql (12.3)
Type "help" for help.

postgres=# GRANT EXECUTE ON FUNCTION pg_catalog.pg_ls_dir(text, bool, bool) TO racpg;
GRANT
postgres=# GRANT EXECUTE ON FUNCTION pg_catalog.pg_ls_dir(text) TO racpg;
GRANT
postgres=# \df+ pg_catalog.pg_ls_dir();
List of functions
-[ RECORD 1 ]-------+------------------------------
Schema              | pg_catalog
Name                | pg_ls_dir
Result data type    | SETOF text
Argument data types | text
Type                | func
Volatility          | volatile
Parallel            | safe
Owner               | pginc
Security            | invoker
Access privileges   | pginc=X/pginc                +
                    | racpg=X/pginc
Language            | internal
Source code         | pg_ls_dir_1arg
Description         | list all files in a directory
-[ RECORD 2 ]-------+------------------------------
Schema              | pg_catalog
Name                | pg_ls_dir
Result data type    | SETOF text
Argument data types | text, boolean, boolean
Type                | func
Volatility          | volatile
Parallel            | safe
Owner               | pginc
Security            | invoker
Access privileges   | pginc=X/pginc                +
                    | racpg=X/pginc
Language            | internal
Source code         | pg_ls_dir
Description         | list all files in a directory

postgres=# SELECT * FROM pg_catalog.pg_namespace;
-[ RECORD 1 ]------------------------
oid      | 99
nspname  | pg_toast
nspowner | 10
nspacl   | 
-[ RECORD 2 ]------------------------
oid      | 12314
nspname  | pg_temp_1
nspowner | 10
nspacl   | 
-[ RECORD 3 ]------------------------
oid      | 12315
nspname  | pg_toast_temp_1
nspowner | 10
nspacl   | 
-[ RECORD 4 ]------------------------
oid      | 2200
nspname  | public
nspowner | 10
nspacl   | {pginc=UC/pginc,=UC/pginc}
-[ RECORD 5 ]------------------------
oid      | 14097
nspname  | information_schema
nspowner | 10
nspacl   | {pginc=UC/pginc,=U/pginc}
-[ RECORD 6 ]------------------------
oid      | 11
nspname  | pg_catalog
nspowner | 10
nspacl   | {pginc=UC/pginc,=U/pginc}

postgres=# \q

Now, I test the function with the user racpg:

[pginc@pp-snp-pg11 ~]$ psql -x --host 10.242.20.5 --port 4900 --user racpg dbnccl01
psql (12.3)
Type "help" for help.

dbnccl01=> \df+ pg_catalog.pg_ls_dir();
List of functions
-[ RECORD 1 ]-------+------------------------------
Schema              | pg_catalog
Name                | pg_ls_dir
Result data type    | SETOF text
Argument data types | text
Type                | func
Volatility          | volatile
Parallel            | safe
Owner               | pginc
Security            | invoker
Access privileges   | pginc=X/pginc   ### <=== Access privileges for racpg are gone!
Language            | internal
Source code         | pg_ls_dir_1arg
Description         | list all files in a directory
-[ RECORD 2 ]-------+------------------------------
Schema              | pg_catalog
Name                | pg_ls_dir
Result data type    | SETOF text
Argument data types | text, boolean, boolean
Type                | func
Volatility          | volatile
Parallel            | safe
Owner               | pginc
Security            | invoker
Access privileges   | pginc=X/pginc   ### <=== Access privileges for racpg are gone!
Language            | internal
Source code         | pg_ls_dir
Description         | list all files in a directory

dbnccl01=> SELECT pg_catalog.pg_ls_dir('/tmp/');
ERROR:  permission denied for function pg_ls_dir ### BOOOM!!!
dbnccl01=> \q

Any idea?

Just in case, here my OS version:

[pginc@pp-snp-pg11 ~]$ grep VERSION /etc/os-release 
VERSION="8.5 (Ootpa)"
VERSION_ID="8.5"
REDHAT_BUGZILLA_PRODUCT_VERSION=8.5
REDHAT_SUPPORT_PRODUCT_VERSION="8.5"

From depesz in #postgresql:

- flaf: functions are local to database.
- you granted the privileges in database "postgres", but are trying to run the function in database named "dbnccl01"

Thx to him. :)

So here is the solution:

# Connection in the database dbnccl01!
[pginc@pp-snp-pg11 ~]$ psql -x dbnccl01
psql (12.3)
Type "help" for help.

dbnccl01=# 
dbnccl01=# 
dbnccl01=# 
dbnccl01=# \df+ pg_catalog.pg_ls_dir();
List of functions
-[ RECORD 1 ]-------+------------------------------
Schema              | pg_catalog
Name                | pg_ls_dir
Result data type    | SETOF text
Argument data types | text
Type                | func
Volatility          | volatile
Parallel            | safe
Owner               | pginc
Security            | invoker
Access privileges   | pginc=X/pginc
Language            | internal
Source code         | pg_ls_dir_1arg
Description         | list all files in a directory
-[ RECORD 2 ]-------+------------------------------
Schema              | pg_catalog
Name                | pg_ls_dir
Result data type    | SETOF text
Argument data types | text, boolean, boolean
Type                | func
Volatility          | volatile
Parallel            | safe
Owner               | pginc
Security            | invoker
Access privileges   | pginc=X/pginc
Language            | internal
Source code         | pg_ls_dir
Description         | list all files in a directory

dbnccl01=# GRANT EXECUTE ON FUNCTION pg_catalog.pg_ls_dir(text, bool, bool) TO racpg;
GRANT
dbnccl01=# GRANT EXECUTE ON FUNCTION pg_catalog.pg_ls_dir(text) TO racpg;
GRANT
dbnccl01=# \df+ pg_catalog.pg_ls_dir();
List of functions
-[ RECORD 1 ]-------+------------------------------
Schema              | pg_catalog
Name                | pg_ls_dir
Result data type    | SETOF text
Argument data types | text
Type                | func
Volatility          | volatile
Parallel            | safe
Owner               | pginc
Security            | invoker
Access privileges   | pginc=X/pginc                +
                    | racpg=X/pginc
Language            | internal
Source code         | pg_ls_dir_1arg
Description         | list all files in a directory
-[ RECORD 2 ]-------+------------------------------
Schema              | pg_catalog
Name                | pg_ls_dir
Result data type    | SETOF text
Argument data types | text, boolean, boolean
Type                | func
Volatility          | volatile
Parallel            | safe
Owner               | pginc
Security            | invoker
Access privileges   | pginc=X/pginc                +
                    | racpg=X/pginc
Language            | internal
Source code         | pg_ls_dir
Description         | list all files in a directory

dbnccl01=# \q

And now it works with the account racpg:

[pginc@pp-snp-pg11 ~]$ psql dbnccl01
psql (12.3)
Type "help" for help.

dbnccl01=# SELECT pg_catalog.pg_ls_dir('.');
      pg_ls_dir       
----------------------
 pg_wal
 global
 pg_commit_ts
 pg_dynshmem
 pg_notify
 pg_serial
 pg_snapshots
 pg_subtrans
 pg_twophase
 pg_multixact
 base
 pg_replslot
 pg_tblspc
 pg_stat
 pg_stat_tmp
 pg_xact
 pg_logical
 PG_VERSION
 postgresql.auto.conf
 pg_hba.conf
 pg_ident.conf
 postmaster.opts
 postgresql.conf
 postmaster.pid
 current_logfiles
(25 rows)

dbnccl01=#