Privileges for PostgreSQL procedures

[kkrasnov1]

ldap2pg.yml

privileges:
  connect:
  - __connect__
  priv_readers:
  - __connect__
  - __select_on_tables__
  - __select_on_sequences__
  - __usage_on_schemas__
  - __usage_on_types__

  priv_writers:
  - priv_readers
  - __temporary__
  - __delete_on_tables__
  - __insert_on_tables__
  - __truncate_on_tables__
  - __update_on_tables__
  - __execute_on_functions__
  - __usage_on_sequences__


  priv_owners:
  - priv_writers
  - __all_on_schemas__
  - __all_on_tables__
  - __all_on_sequences__
  - __all_on_functions__

Expectations

Hi,

We use procedures in PostgreSQL and builtin priviledge execute_on_functions. If there are procedures, ldap2pg constantly tries to grant privileges to all functions, but cannot.

If privileges for procedures are granted manually, then ldap2pg no longer tries to grant privileges for all functions.

ldap2pg probably correctly determines that there are not enough privileges for procedures, but tries to issue them for functions.

Verbose output of ldap2pg execution

Grant privilege.  grant="EXECUTE ON ALL FUNCTIONS IN SCHEMA xxxx.schema1 TO owner_group" database=xxxx

[bersace]

Hi @kkrasnov1 , thanks for reaching. Can you share a sample procedure object, with ACL and owner ? Use \df+ for example.

[kkrasnov1]

DDL

CREATE SCHEMA ldap2pg_test AUTHORIZATION xxx;

create or replace procedure ldap2pg_test.proc1()
language plpgsql    
as $$
begin

    commit;
end;$$;;

psql

xxx=# \df+ ldap2pg_test.proc1
List of functions
-[ RECORD 1 ]-------+-------------
Schema              | ldap2pg_test
Name                | proc1
Result data type    |
Argument data types |
Type                | proc
Volatility          | volatile
Parallel            | unsafe
Owner               | postgres
Security            | invoker
Access privileges   |
Language            | plpgsql
Source code         |             +
                    | begin       +
                    |             +
                    |     commit; +
                    | end;
Description         |

ldap2pg output

GE Revoke privilege.                                grant="EXECUTE ON ALL FUNCTIONS IN SCHEMA xxx.ldap2pg_test TO public" database=xxx
GE Grant privilege.                                 grant="EXECUTE ON ALL FUNCTIONS IN SCHEMA xxx.ldap2pg_test TO db_pg_test_xxx_datawriter" database=xxx
GE Grant privilege.                                 grant="EXECUTE ON ALL FUNCTIONS IN SCHEMA xxx.ldap2pg_test TO db_pg_test_xxx_owner" database=xxx
GE Grant privilege.                                 grant="EXECUTE ON ALL FUNCTIONS IN SCHEMA xxx.ldap2pg_test TO db_pg_test_xxx_owner" database=xxx

[kkrasnov1]

Hi @bersace, Is it possible to grant privileges to stored procedures?

[bersace]

Hi @bersace, Is it possible to grant privileges to stored procedures?

You can manage EXECUTE on all functions with __execute_on_functions__ privilege. See https://ldap2pg.readthedocs.io/en/latest/builtins/#execute-on-functions

[bersace]

Hi @bersace, Is it possible to grant privileges to stored procedures?

You can manage only privileges per schema. No finer granularity.

[kkrasnov1]

Hi @bersace, Is it possible to grant privileges to stored procedures?

You can manage EXECUTE on all functions with __execute_on_functions__ privilege. See https://ldap2pg.readthedocs.io/en/latest/builtins/#execute-on-functions

Thank you. It works correctly.

[kkrasnov1]

@bersace, every time I start, I get a message CHANGE Revoke privilege. grant="EXECUTE ON ALL FUNCTIONS IN SCHEMA userdb.ldap2pg_test TO myuser" database=userdb

Am I doing something wrong or is it a bug in ldap2pg?

[bersace]

@bersace, every time I start, I get a message CHANGE Revoke privilege. grant="EXECUTE ON ALL FUNCTIONS IN SCHEMA userdb.ldap2pg_test TO myuser" database=userdb

Am I doing something wrong or is it a bug in ldap2pg?

This will be fixed in next release. cf. #674