dalibo / ldap2pg

:elephant: :busts_in_silhouette: Manage PostgreSQL roles and privileges from YAML or LDAP
https://labs.dalibo.com/ldap2pg
PostgreSQL License
211 stars 35 forks source link

Privileges for PostgreSQL procedures #655

Open kkrasnov1 opened 6 months ago

kkrasnov1 commented 6 months ago

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 commented 6 months ago

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

kkrasnov1 commented 6 months ago

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 commented 5 months ago

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

bersace commented 5 months ago

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 commented 5 months ago

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

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

kkrasnov1 commented 5 months ago

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 commented 5 months ago

@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?