dalibo / ldap2pg Goto Github PK

View Code? Open in Web Editor NEW

200.0 19.0 33.0 4.06 MB

:elephant: :busts_in_silhouette: Manage PostgreSQL roles and privileges from YAML or LDAP

Home Page: https://labs.dalibo.com/ldap2pg

License: PostgreSQL License

Python 7.44% Makefile 1.81% Shell 2.61% Dockerfile 0.44% Go 87.70%

ldap postgresql yaml roles acl privileges golang directory security users

ldap2pg's Introduction

Swiss-army knife to synchronize Postgres roles and privileges from YAML or LDAP.

Postgres is able to check password of an existing role using the LDAP protocol out of the box. ldap2pg automates the creation, update and removal of PostgreSQL roles and users from an entreprise directory.

Managing roles is close to managing privileges as you expect roles to have proper default privileges. ldap2pg can grant and revoke privileges too.

Features

Reads settings from an expressive YAML config file.
Creates, alters and drops PostgreSQL roles from LDAP searches.
Creates static roles from YAML to complete LDAP entries.
Manages role parents (alias groups).
Grants or revokes privileges statically or from LDAP entries.
Dry run, check mode.
Logs LDAP searches as ldapsearch(1) commands.
Logs every SQL statements.

Here is a sample configuration and execution:

$ cat ldap2pg.yml
version: 6

rules:
- role:
    name: nominal
    options: NOLOGIN
    comment: "Database owner"
- ldapsearch:
    base: ou=people,dc=ldap,dc=ldap2pg,dc=docker
    filter: "(objectClass=organizationalPerson)"
  role:
    name: '{cn}'
    options:
      LOGIN: yes
      CONNECTION LIMIT: 5
$ ldap2pg --real
08:25:12 INFO   Starting ldap2pg                                 version=v6.0-alpha5 runtime=go1.21.0 commit=<none>
08:25:12 INFO   Using YAML configuration file.                   path=docs/readme/ldap2pg.yml
08:25:12 INFO   Running as unprivileged user.                    user=ldap2pg super=false server="PostgreSQL 15.3" cluster=ldap2pg-dev database=nominal
08:25:12 INFO   Connected to LDAP directory.                     uri=ldaps://ad.bridoulou.fr authzid="dn:cn=administrator,cn=users,dc=bridoulou,dc=fr"
08:25:12 INFO   Real mode. Postgres instance will modified.
08:25:12 CHANGE Create role.                                     role=charles database=nominal
08:25:12 CHANGE Set role comment.                                role=charles database=nominal
08:25:12 CHANGE Inherit role for management.                     role=charles database=nominal
08:25:12 CHANGE Alter options.                                   role=alain options="LOGIN CONNECTION LIMIT 5" database=nominal
08:25:12 CHANGE Terminate running sessions.                      role=omar database=nominal
08:25:12 CHANGE Allow current user to reassign objects.          role=omar parent=ldap2pg database=nominal
08:25:12 CHANGE Reassign objects and purge ACL.                  role=omar owner=nominal database=nominal
08:25:12 CHANGE Drop role.                                       role=omar database=nominal
08:25:12 INFO   Comparison complete.                             elapsed=68.47058ms mempeak=1.6MiB postgres=15.323294ms queries=8 ldap=635.894µs searches=1
$

Installation

Download package or binary from Releases page.

$ sudo yum install https://github.com/dalibo/ldap2pg/releases/download/v6.1/ldap2pg_6.1_linux_amd64.rpm
...
Installed:
  ldap2pg-6.1-1.x86_64

Complete!
$ ldap2pg --help
usage: ldap2pg [OPTIONS] [dbname]

      --check              Check mode: exits with 1 if Postgres instance is unsynchronized.
      --color              Force color output. (default true)
  -c, --config string      Path to YAML configuration file. Use - for stdin.
  -C, --directory string   Path to directory containing configuration files.
  -?, --help               Show this help message and exit.
  -q, --quiet count        Decrease log verbosity.
  -R, --real               Real mode. Apply changes to Postgres instance.
  -P, --skip-privileges    Turn off privilege synchronisation.
  -v, --verbose count      Increase log verbosity.
  -V, --version            Show version and exit.


Optional argument dbname is alternatively the database name or a conninfo string or an URI.
See man psql(1) for more information.

By default, ldap2pg runs in dry mode.
ldap2pg requires a configuration file to describe LDAP searches and mappings.
See https://ldap2pg.readthedocs.io/en/latest/ for further details.
$

ldap2pg is licensed under PostgreSQL license.

ldap2pg requires a configuration file called ldap2pg.yaml. Project ships a tested ldap2pg.yml as a starting point.

# curl -LO https://github.com/dalibo/ldap2pg/raw/master/ldap2pg.yml
# editor ldap2pg.yml

Finally, it's up to you to use ldap2pg in a crontab or a playbook. Have fun!

ldap2pg is reported to work with Samba Directory, OpenLDAP, FreeIPA, Oracle Internet Directory and Microsoft Active Directory.

Support

If you need support and you didn't found it in documentation, just drop a question in a GitHub discussion! French accepted. If you hit a bug or miss a feature, open a GitHub issue. Don't miss the cookbook for advanced use cases.

Authors

ldap2pg is a Dalibo Labs project.

Étienne BERSAC is the maintainer.
Damien Cazeils designed the logo.
Harold le CLÉMENT de SAINT-MARCQ implemented LDAP sub searches.
Randolph Voorhies implemented role configuration synchronization.

ldap2pg's People

Contributors

Stargazers

Watchers

ldap2pg's Issues

Documentation

Running as non-superuser

When connecting to the DB with a non-superuser, an unhandled error occurs (and I was recommended to file an issue):

[ldap2pg.psql         DEBUG] Doing:
[ldap2pg.psql         DEBUG] SELECT array_agg(column_name::text)
[ldap2pg.psql         DEBUG] FROM information_schema.columns
[ldap2pg.psql         DEBUG] WHERE table_schema = 'pg_catalog' AND table_name = 'pg_authid'
[ldap2pg.psql         DEBUG] LIMIT 1
[ldap2pg.script       ERROR] Unhandled error:
[ldap2pg.script       ERROR] Traceback (most recent call last):
[ldap2pg.script       ERROR]   File "/usr/local/lib/python3.5/dist-packages/ldap2pg/script.py", line 87, in main
[ldap2pg.script       ERROR]     exit(wrapped_main(config))
[ldap2pg.script       ERROR]   File "/usr/local/lib/python3.5/dist-packages/ldap2pg/script.py", line 51, in wrapped_main
[ldap2pg.script       ERROR]     RoleOptions.update_supported_columns(supported_columns)
[ldap2pg.script       ERROR]   File "/usr/local/lib/python3.5/dist-packages/ldap2pg/role.py", line 155, in update_supported_columns
[ldap2pg.script       ERROR]     c for c in RoleOptions.COLUMNS.keys()
[ldap2pg.script       ERROR]   File "/usr/local/lib/python3.5/dist-packages/ldap2pg/role.py", line 156, in <listcomp>
[ldap2pg.script       ERROR]     if c in columns
[ldap2pg.script       ERROR] TypeError: argument of type 'NoneType' is not iterable
[ldap2pg.script       ERROR] Please file an issue at https://github.com/dalibo/ldap2pg/issues with full log.

Btw, do you recommend using a superuser role? Or which privileges (and grants on tables) would any other role need to run the ldap2pg queries?

Add role_match to grant rule

Add a simple conditionnal againts role name to grant rule.

- grant:
    role_attribute: cn
    role_match: '*_R'
    acl: ro

Users in multiple ldap groups are assigned to only one postgresql group

I have the following scenario:

multiple databases
database a should be writable by users from ldap group 1
database b should be writable by users from ldap group 2
there are users which are in both groups

I tried to realize this with the following ldap2pg.yml

---
color: yes
verbose: yes
dry: no

ldap:
    uri: ldap://example.com
    binddn: cn=svc.ldap,ou=Functional Users,ou=Users,dc=example,dc=com
    password: "redacted"

postgres:
    blacklist: [postgres, pg_*, testing]

acls:
  ro:
  - __usage_on_schemas__
  - __select_on_tables__
  rw:
  - ro
  - __insert__
  - __update_on_tables__
  ddl:
  - rw
  - __all_on_schemas__
  - __all_on_tables__

sync_map:
    __all__:
    - roles: [test1_writer,test1_reader,test2_writer,test2_reader]
      options: NOLOGIN

    test01:
    - ldap:
        base: ou=justatest,dc=example,dc=com
        filter: "(memberOf:1.2.840.113556.1.4.1941:=cn=g_r_postgres01-test,ou=FunctionalGroups,ou=Groups,ou=justatest,dc=idiv,dc=de)"
        attributes: sAMAccountName
      roles:
        name_attribute: sAMAccountName
        options: LOGIN
        parent: test1_writer
      grant:
        - acl: rw
          database: test1
          role: test1_writer

    test02:
    - ldap:
        base: ou=justatest,dc=example,dc=com
        filter: "(memberOf:1.2.840.113556.1.4.1941:=cn=g_r_postgres01-test2,ou=FunctionalGroups,ou=Groups,ou=justatest,dc=idiv,dc=de)"
        attributes: sAMAccountName
      roles:
        name_attribute: sAMAccountName
        options: LOGIN
        parent: test2_writer
      grant:
        - acl: rw
          database: test2
          role: test2_writer

Expected behaviour:

users in cn=g_r_postgres01-test and cn=g_r_postgres01-test2 end up in test1_writer and test2_writer
users which are in cn=g_r_postgres01-test only end up in test1_writer
users which are in cn=g_r_postgres01-test2 only end up in test2_writer

Actual behaviour:

users in cn=g_r_postgres01-test and cn=g_r_postgres01-test2 end up in test2_writer but not in test1_writer
users which are in cn=g_r_postgres01-test only end up in test1_writer
users which are in cn=g_r_postgres01-test2 only end up in test2_writer

I did not find an append flag or similar in the documentation. Is there another way to realize this?

p.s. The memberOf:1.2.840.113556.1.4.1941 is used to recursively get members from AD which are in nested ldap groups.

Create Dockerfile

On docker only platform, having an official ldap2pg Docker image is great.

Tasks

I offer mentorship on this issue :-)

Issue in creating user roles

[ldap2pg.manager      DEBUG] Got 1 entries from LDAP.
[ldap2pg.script       ERROR] Unhandled error:
[ldap2pg.script       ERROR] Traceback (most recent call last):
[ldap2pg.script       ERROR]   File "/spare/local/infrad/m2/lib/python2.7/site-packages/ldap2pg/script.py", line 88, in main
[ldap2pg.script       ERROR]     exit(wrapped_main(config))
[ldap2pg.script       ERROR]   File "/spare/local/infrad/m2/lib/python2.7/site-packages/ldap2pg/script.py", line 68, in wrapped_main
[ldap2pg.script       ERROR]     count = manager.sync(syncmap=config['sync_map'])
[ldap2pg.script       ERROR]   File "/spare/local/infrad/m2/lib/python2.7/site-packages/ldap2pg/manager.py", line 198, in sync
[ldap2pg.script       ERROR]     ldaproles, ldapacl = self.inspect_ldap(syncmap)
[ldap2pg.script       ERROR]   File "/spare/local/infrad/m2/lib/python2.7/site-packages/ldap2pg/manager.py", line 155, in inspect_ldap
[ldap2pg.script       ERROR]     for grant in grants:
[ldap2pg.script       ERROR]   File "/spare/local/infrad/m2/lib/python2.7/site-packages/ldap2pg/manager.py", line 120, in apply_grant_rules
[ldap2pg.script       ERROR]     role = role.lower()
[ldap2pg.script       ERROR] AttributeError: 'dict' object has no attribute 'lower'
[ldap2pg.script       ERROR] Please file an issue at https://github.com/dalibo/ldap2pg/issues with full log.

My LDAP File

---
acls:
  ro:
  - __connect__
  - __select_on_all_tables__
  - __select_on_all_sequences__
  - __default_select_on_tables__
  - __default_select_on_sequences__
postgres:
  dsn: "postgres://*:*@machine.new:5432/trade"
  databases_query: [trade]
  schemas_query: [public]
  blacklist : [postgres, pg_*, root,trade_rw]
sync_map:
  grant:
    schema : public
    database : trade
    acl: ro
    roles:
    - name_attribute: cn
      options: NOLOGIN
      member_attribute: memberUid
    - name_attribute: memberUid
      options: LOGIN
  ldap:
    base: "cn=readers,ou=Group,dc=microsoft,dc=com"

I want to create individual roles for each of the user returned. I have no role defined in database. i am getting the above error.

Commande line options

Using parents_attribute results in more PostgreSQL grants than it should

I'm trying to create login roles and assigning them to a group they are a member of according to LDAP. I set up the following LDAP query (group1, group2 and group3 are created beforehand with another LDAP query):

- ldap:
    base: ou=groups,dc=ldap,dc=xy,dc=org
    filter: "(|(cn=group1)(cn=group2)(cn=group3))"
  role:
    name_attribute: memberUid
    options: LOGIN
    parent:
    - ldap_roles
    parents_attribute: cn

This basically works. Members of LDAP group1 are granted PostgreSQL role group1.
However, members of LDAP group2 are granted PostgreSQL roles group2 and group1.
And members of LDAP group3 are granted PostgreSQL roles group3 and group2 and group1.

Is it a bug, or am I doing something wrong?

ACL configuration for create rights GRANT

Hi so sorry I don't understand how to use ACL for generate GRANT options I need for my roles.

I have created this groups in my Active Directory:

Database1_ro
Database1_rw
Database2_ro
Database2_rw
Global_rw
Global_ro

So for exemple each users in Database1_ro need to have read privileges on Database1 only.
I think I need to generate this: GRANT CONNECT ON Database1 TO rolename.

I have use this acl:

acl_dict:
  all-tables-select:
    # Warning: schema with no tables are never granted!
    inspect: |
      WITH
      namespace_tables AS (
        -- All namespace and role having grant on it, and array of available
        -- relations in the namespace.
        SELECT

nsp.nspname,
          ARRAY(
            SELECT UNNEST(array_agg(rel.relname)
            FILTER (WHERE rel.relname IS NOT NULL))
            ORDER BY 1
          ) AS tables
        FROM pg_catalog.pg_namespace nsp
        LEFT OUTER JOIN pg_catalog.pg_class rel
          ON rel.relnamespace = nsp.oid AND relkind IN ('r', 'v')
        WHERE nspname NOT LIKE 'pg_%'
        GROUP BY 1
      ),
      tables_grants AS (
        SELECT
          table_schema AS "schema",
          grantee,
          -- Aggregate the relation grant for this privilege.
          ARRAY(SELECT UNNEST(array_agg(table_name::name)) ORDER BY 1) AS tables
        FROM information_schema.role_table_grants
        WHERE privilege_type = 'SELECT'
        GROUP BY 1, 2
      )
      SELECT
        nspname, rolname,
        rels.tables = nsp.tables AS "full"
      FROM namespace_tables nsp
      CROSS JOIN pg_catalog.pg_roles rol
      JOIN tables_grants rels
        ON rels."schema" = nsp.nspname AND rels.grantee = rolname
    grant: |
      GRANT CONNECT ON Database1 TO {role};
    #revoke: |
      #REVOKE SELECT ON ALL TABLES IN SCHEMA {schema} FROM {role};

With this group definition:

acl_groups:
  ro: [all-tables-select]

And use this grant configuration in group mapping:

  grant:
    role_attribute: sAMAccountName
    acl: ro
    database: Database1
    schema: __all__

from the exemple of the cookbook.
But there are no privilèges declared in my Database1 and there no error when I run ldap2pg.

Somebody can help me?

LDAP bind with GSSAPI

Is it possible to use a keytab file instead of supplying user name and password for binding to LDAP?

The environment is Active Directory based. Saving a password in clear text and sending the password over the wire is unacceptable from security perspective.

Support setuptools version shipped with RHEL 7

Hi,

This package builds fine on my Fedora 26 box, but I'm having issues on RHEL 6, RHEL 7 and Fedora 25.

-bash-4.2$ python setup.py bdist running bdist running bdist_dumb running build installing to build/bdist.linux-x86_64/dumb running install running install_egg_info running egg_info writing requirements to UNKNOWN.egg-info/requires.txt writing UNKNOWN.egg-info/PKG-INFO writing top-level names to UNKNOWN.egg-info/top_level.txt writing dependency_links to UNKNOWN.egg-info/dependency_links.txt reading manifest file 'UNKNOWN.egg-info/SOURCES.txt' writing manifest file 'UNKNOWN.egg-info/SOURCES.txt'
...and this UNKNOWN word leads to other issues as you can guess.

BTW, this command also does not build ldap2pg binary, too.

Can you please let me know how to build ldap2pg on RHEL 7?
Regards, Devrim

Investigate pgbouncer integration

Stdout of only modified data

Hi!

Right now, running ldap2pg will always output data on the standard output, even if quiet mode is specified.

It would be very good to have output modes for:

Something that only prints errors, and nothing else. As long as everything works, nothing at all is printed.
Something that prints all changes, but no status messages. As long as nothing has changed, nothing is printed. If something has changed, the change is printed. And of course, if something goes wrong, errors are printed.

When running it in a cronjob, this makes for proper "change notifications", instead of just having to pipe the whole thing to /dev/null.

default privileges can't be determined error

Hi,
I reached next level by first using ldapsearch and then getting parameters correct. But next I came across this below:

ldap2pg.yml.
Running in dry mode. Postgres will be untouched.
Inspecting roles in Postgres cluster...
Querying LDAP cn=developers-group,ou=d... (objectclass...
Would create ash12.
Would create ashle.
Would create tt.
Would create developers-group.
Inspecting GRANTs in Postgres cluster...
In dry mode, some owners aren't created, their default privileges can't be determined.
Unhandled error:
Traceback (most recent call last):
File "/usr/local/lib/python3.4/dist-packages/ldap2pg/script.py", line 84, in main
exit(wrapped_main(config))
File "/usr/local/lib/python3.4/dist-packages/ldap2pg/script.py", line 68, in wrapped_main
count = manager.sync(syncmap=config['sync_map'])
File "/usr/local/lib/python3.4/dist-packages/ldap2pg/manager.py", line 213, in sync
pgacl = self.inspector.fetch_grants(schemas, pgmanagedroles)
File "/usr/local/lib/python3.4/dist-packages/ldap2pg/inspector.py", line 254, in fetch_grants
rows = psql(privilege.inspect)
File "/usr/local/lib/python3.4/dist-packages/ldap2pg/psql.py", line 142, in call
self.cursor.execute(query, *args)
psycopg2.ProgrammingError: syntax error at or near "("
LINE 26: FILTER (WHERE pro.proname IS NOT NULL) AS procs
^
Please file an issue at https://github.com/dalibo/ldap2pg/issues with full log.

Here I am giving ldap2pg.yml:

postgres:
  dsn: postgres://****:pass123@localhost:5432/
  databases_query: [postgres]

privileges:
  # Define an privilege group `ro` with read-only grants
  ro:
  - __connect__
  - __execute__
  - __select_on_tables__
  - __select_on_sequences__
  - __usage_on_schemas__
  - __usage_on_types__

sync_map:
- ldap:
    base: cn=*,ou=*,dc=ldap,dc=com
    filter: (objectclass=*)
  role:
    name: '{cn}'    
    options: LOGIN SUPERUSER

Any help would be appreciated.

Support multiple LDAP servers

For High availability ! :-)

Add dry run

Add a dry run mode. Ensure log output is effective.

acls not cycled over pg_catalog by default

I tried to follow along the examples in the documentation to create a group of ddl users. But they are not able to create a database:

exampledb=> create table test(f1 int);
ERROR:  permission denied for schema pg_catalog
LINE 1: create table test(f1 int);

Granting usage on pg_catalog schema resolves the issue.

grant USAGE on SCHEMA pg_catalog to ldap_test;

postgres:
    blacklist: [postgres, pg_*, testing]

acls:
  ro:
  - __connect__
  - __usage_on_schemas__
  - __select_on_tables__

  rw:
  - ro
  - __insert__
  - __update_on_tables__

  ddl:
  - rw
  - __all_on_schemas__
  - __all_on_tables__


sync_map:
    __all__:
    - roles: [ldap_test]

    exampledb:
    - ldap:
        base: ou=AAI,dc=example,dc=com
        filter: "(memberOf:1.2.840.113556.1.4.1941:=cn=g_r_postgres01-test,ou=FunctionalGroups,ou=Groups,ou=MYORG,dc=example,dc=com)"
        attributes: sAMAccountName
      roles:
        name_attribute: sAMAccountName
        options: LOGIN
        parent: ldap_test
      grant:
        - acl: ddl
          database: exampledb
          role: ldap_test

It seems that acls are not applied to schema pg_catalog, neither default nor when explicitly stating schema: __all__. Applying an acl explictly to pg_catalog works, e.g. adding the following acl to the above grants

        - acl: ro
          database: exampledb
          schema: pg_catalog
          role: ldap_test

Simple ACL example

Hi,

I would like to control permissions of the objects in Postgres. Our scenario is that we have one database and I would like all objects (tables, schemas ...) to be accessible to all. My current conf is (the ACL bit is taken from the cookbook):

acls:
  ro:
  - __usage_on_schemas__
  - __select_on_tables__

  rw:
  - ro
  - __all_on_tables__

  ddl:
  - rw
  - __create_on_schemas__

sync_map:
  - roles:
    - names: [owners, readers, writers]
      options: NOLOGIN
      database: company

  - grant:
    - acl: ro
      database: company
      schema: __all__
      role: readers

    - acl: rw
      database: company
      schema: __all__
      role: writers

    - acl: ddl
      database: company
      schema: __all__
      role: owners

  - ldap:
      base: "OU=People,OU=company,DC=int,DC=company,DC=com"
      attributes: sAMAccountName
    role:
      name_attribute: sAMAccountName
      options: LOGIN NOSUPERUSER NOCREATEDB
      parent: owners
    grant:
      - acl: ddl
        role: owners

As you can see above, all LDAP users are being asinged to the ddl group, however, when I run it it prevents users from logging in using pgadmin 4. I have tried a few variations of the above and got errors like (from postgresql.log):
1.

2018-01-25 18:28:06.141 UTC [20822] joshlk@postgres ERROR:  permission denied for function version
2018-01-25 18:28:06.141 UTC [20822] joshlk@postgres STATEMENT:  SELECT version()

2018-01-25 18:25:23.306 UTC [20810] joshlk@postgres ERROR:  permission denied for schema pg_catalog at character 143
2018-01-25 18:25:23.306 UTC [20810] joshlk@postgres STATEMENT:
	SELECT
	    oid as id, rolname as name, rolsuper as is_superuser,
	    rolcreaterole as can_create_role, rolcreatedb as can_create_db
	FROM
	    pg_catalog.pg_roles
	WHERE
	    rolname = current_user

2018-01-25 18:47:00.433 UTC [21181] joshlk@postgres ERROR:  permission denied for schema pg_catalog
2018-01-25 18:47:00.433 UTC [21181] joshlk@postgres STATEMENT:  SELECT CASE WHEN usesuper
	       THEN pg_is_in_recovery()
	       ELSE FALSE
	       END as inrecovery,
	       CASE WHEN usesuper AND pg_is_in_recovery()
	       THEN pg_is_xlog_replay_paused()
	       ELSE FALSE
	       END as isreplaypaused
	FROM pg_user WHERE usename=current_user

It looks like it is because of its locking users out from accessing the postgres default database and objects within it. Can I ask ldap2pg to exclude revoking permissions on the posgres database?

Do you have any recommendations on what ACL rules to use? What I want to prevent is someone creating a table or schema and then the rest of the team not been able to access it. My team is small so something simple would suffice.

Bonus question... is there a way that when a user is created a schema of the same name is also created? So for user joshlk it automatically creates a schema joshlk?

Many thanks for your help and really appreciate your work and giving to the open source community!

Josh

Add whitelist

Hi,

We need to filter out the list of PG roles (returned by SyncManager.fetch_pg_roles()) by group (parent) name.

Maybe we could add a new parameter in postgres section of the configuration file : whitelist

With this parameter, we'll be able to fetch (and sync!!) PG roles only from groups like this:

postgres:
   ...
  blacklist: [*]
  whitelist:
     groups: [MyGroup1, foo*]
     users: []

Thoughts ?

Unknown attribute u'samaccountname' after drop of attributes parameter in 4.2

With release 4.2 the attributes parameter was dropped from the ldap part of the configuration. Here this results in in critical Unknown attribute u'samaccountname' errors directly after the ldap search.

4.1 log

Doing: ldapsearch -x -D cn=mybinduser,ou=Functional Users,ou=Users,ou=IDIV,dc=idiv,dc=de -W -b ou=AAI,dc=idiv,dc=de -s sub '(memberOf:1.2.840.113556.1.4.1941:=cn=g_r_postgres01-KREINITZ_rw,ou=FunctionalGroups,ou=Groups,ou=IDIV,dc=idiv,dc=de)' sAMAccountName

Got 3 entries from LDAP.
Found role redac t. ed from CN=redac ted,OU=German Centre for Integrative Biodiversity Research,OU=Mitarbeiter,OU=AAI,DC=idiv,DC=de sAMAccountName.
[…]
Found ACL item ddl on KREINITZ.* for * to kreinitz_owner (granted) in LDAP.
[…]

4.2 log

Doing: ldapsearch -x -D cn=mybinduser,ou=Functional Users,ou=Users,ou=IDIV,dc=idiv,dc=de -W -b ou=AAI,dc=idiv,dc=de -s sub '(memberOf:1.2.840.113556.1.4.1941:=cn=g_r_postgres01-KREINITZ_rw,ou=FunctionalGroups,ou=Groups,ou=IDIV,dc=idiv,dc=de)' sAMAccountName
[ldap2pg.manager     DEBUG] Got 3 entries from LDAP.
[ldap2pg.script   CRITICAL] Failed to process CN=redac t. ed,OU=German Centre for Integra: Unknown attribute u'samaccountname'

It might(!) be related to the base search string. The users are in …,ou=AAI,dc… while the groups are in …,ou=IDIV,dc…. Using the dc… as base search string does work for ldapsearch but for some reason not via ldap2pg. With ldapsearch you actually need to omit the ou as it would not find the users which are in the ou=AAI,dc… if the base search string points to ou=IDIV,dc…

[ldap2pg.script   CRITICAL] Failed to query LDAP: {'info': '000004DC: LdapErr: DSID-0C09079A, comment: In order to perform this operation a successful bind must be completed on the connection., data 0, v23f0', 'desc': 'Operations error'}.

Support LDAP TLS

Blacklist does not affect users loaded from AD Groups

Related to #282

When creating LDAP roles, I would expect the blacklist to also apply to any users found there, in effect skipping them. This does not appear to be the case today.

UnicodeDecodeError when using sync_map.ldap and Python 2.7.13

The following ldap2pg.yml is working fine with Python 2.7.13 on Debian 9.4 (see attached ldap2pg.success.log for details):

verbose: on

ldap:
  uri: ldap://localhost:389/

postgres:
  managed_roles_query: |
    SELECT rolname
    FROM pg_catalog.pg_roles
    WHERE
        (rolsuper IS FALSE)
        AND (rolname NOT IN ('xxx-admin'))
    ORDER BY 1;

sync_map:
- roles:
  - name: role1
    options: NOLOGIN
  - name: role2
    option: NOLOGIN

But whenever I add ldap entries to the sync_map:

sync_map:
- roles:
  - name: role1
    options: NOLOGIN
  - name: role2
    option: NOLOGIN
- ldap:
    base: dc=cxptc,dc=org
  role:
    name_attribute: sAMAccountName
    options: LOGIN

I get this:

[...]
Querying LDAP dc=cxptc,dc=org... (objectClass...
Doing: ldapsearch -x -D  -b dc=cxptc,dc=org -s sub '(objectClass=*)' 
Unhandled error:
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/ldap2pg/script.py", line 84, in main
    exit(wrapped_main(config))
  File "/usr/local/lib/python2.7/dist-packages/ldap2pg/script.py", line 68, in wrapped_main
    count = manager.sync(syncmap=config['sync_map'])
  File "/usr/local/lib/python2.7/dist-packages/ldap2pg/manager.py", line 188, in sync
    ldaproles, ldapacl = self.inspect_ldap(syncmap)
  File "/usr/local/lib/python2.7/dist-packages/ldap2pg/manager.py", line 127, in inspect_ldap
    entries = self.query_ldap(**mapping['ldap'])
  File "/usr/local/lib/python2.7/dist-packages/ldap2pg/manager.py", line 37, in query_ldap
    base, scope, filter, attributes,
  File "/usr/local/lib/python2.7/dist-packages/ldap2pg/ldap.py", line 161, in search_s
    return self.wrapped.search_s(base, scope, filter, attributes)
  File "/usr/local/lib/python2.7/dist-packages/ldap2pg/ldap.py", line 132, in __call__
    return decode_value(self.callable_(*a, **kw))
  File "/usr/local/lib/python2.7/dist-packages/ldap2pg/utils.py", line 86, in decode_value
    return [decode_value(v) for v in value]
  File "/usr/local/lib/python2.7/dist-packages/ldap2pg/utils.py", line 88, in decode_value
    return tuple([decode_value(v) for v in value])
  File "/usr/local/lib/python2.7/dist-packages/ldap2pg/utils.py", line 83, in decode_value
    for k, v in value.items()
  File "/usr/local/lib/python2.7/dist-packages/ldap2pg/utils.py", line 86, in decode_value
    return [decode_value(v) for v in value]
  File "/usr/local/lib/python2.7/dist-packages/ldap2pg/utils.py", line 79, in decode_value
    return value.decode('utf-8')
  File "/usr/lib/python2.7/encodings/utf_8.py", line 16, in decode
    return codecs.utf_8_decode(input, errors, True)
UnicodeDecodeError: 'utf8' codec can't decode byte 0xbb in position 5: invalid start byte

The logs for successful and failed runs are attached:
ldap2pg.fail.log
ldap2pg.success.log

My config is: ldap2pg 4.10 from git master, psycopg2 2.6.2, python-ldap 2.4.28 and Python 2.7.13 on Debian 9.4. LDAP (Samba AD DC 4.8.2-debian) and PostgreSQL (9.6.7) are on the same host and anonymous LDAP searches are enabled.

I tried with Python 3.5.3 but encountered another issue: #224

sync_map user with with ROLE postgreadmin and SUPERUSER role

Hi,

i am testing and have removed from ldap group one user.
i want that ONLY ldapgroup member AND postgresadmins member should be sync.

If i run ldap2pg the deleted user from ldapgroup was not deleted from postgres.
And it will be recreate the already exist user and give error.

[ldap2pg.script    WARNING] Running in dry mode. Postgres will be untouched.
[ldap2pg.manager      INFO] Inspecting Postgres...
[ldap2pg.psql        DEBUG] Connecting to Postgres postgres://postgres:[email protected]:5432/postgres.
[ldap2pg.psql        DEBUG] Doing: SELECT datname FROM pg_catalog.pg_database
[ldap2pg.psql        DEBUG] WHERE datallowconn IS TRUE ORDER BY 1;
[ldap2pg.psql        DEBUG] Doing: SELECT
[ldap2pg.psql        DEBUG]     role.rolname, array_agg(members.rolname) AS members,
[ldap2pg.psql        DEBUG]     role.rolbypassrls, role.rolcanlogin, role.rolcreatedb, role.rolcreaterole, role.rolinherit, role.rolreplication, role.rolsuper
[ldap2pg.psql        DEBUG] FROM
[ldap2pg.psql        DEBUG]     pg_catalog.pg_roles AS role
[ldap2pg.psql        DEBUG] LEFT JOIN pg_catalog.pg_auth_members ON roleid = role.oid
[ldap2pg.psql        DEBUG] LEFT JOIN pg_catalog.pg_roles AS members ON members.oid = member
[ldap2pg.psql        DEBUG] WHERE role.rolsuper IS TRUE AND role.rolname='postgresadmins'
[ldap2pg.psql        DEBUG] GROUP BY role.rolname, role.rolbypassrls, role.rolcanlogin, role.rolcreatedb, role.rolcreaterole, role.rolinherit, role.rolreplication, role.rolsuper
[ldap2pg.psql        DEBUG] ORDER BY 1;
[ldap2pg.manager     DEBUG] Found role u'postgresadmins' SUPERUSER NOBYPASSRLS NOCREATEDB INHERIT NOREPLICATION NOCREATEROLE NOLOGIN.
[ldap2pg.manager     DEBUG] Role postgresadmins has members user1,user2.
[ldap2pg.manager     DEBUG] Postgres inspection done.
[ldap2pg.manager     DEBUG] Working on schema __all__.__any__.
[ldap2pg.manager     DEBUG] Found role postgresadmins from YAML.
[ldap2pg.manager     DEBUG] Working on schema __all__.__any__.
[ldap2pg.manager      INFO] Querying LDAP cn=manager,ou=Groups,ou=IAM,ou=_DomainOperations,DC=company,DC=cnb...
[ldap2pg.ldap        DEBUG] Doing: ldapsearch -x -W -b cn=manager,ou=Groups,ou=IAM,ou=_DomainOperations,DC=company,DC=cnb -s sub '(objectClass=*)' member
[ldap2pg.manager     DEBUG] Got 1 entries from LDAP.
[ldap2pg.manager     DEBUG] Found role user1 from CN=manager,OU=Groups,OU=IAM,OU=_DomainOperations,DC=company,DC=cnb member.CN.
[ldap2pg.manager     DEBUG] LDAP inspection completed. Post processing.
[ldap2pg.role        DEBUG] Add user1 as member of postgresadmins.
[ldap2pg.psql        DEBUG] Using Postgres connection to postgres://postgres:[email protected]:5432/postgres.
[ldap2pg.manager      INFO] Would create user1.
[ldap2pg.manager     DEBUG] Would execute: CREATE ROLE user1 WITH NOSUPERUSER NOBYPASSRLS NOCREATEDB INHERIT NOREPLICATION NOCREATEROLE LOGIN;
[ldap2pg.role        DEBUG] Role postgresadmins has spurious members user2.
[ldap2pg.psql        DEBUG] Using Postgres connection to postgres://postgres:[email protected]:5432/postgres.
[ldap2pg.manager      INFO] Would delete spurious postgresadmins members.
[ldap2pg.manager     DEBUG] Would execute: REVOKE postgresadmins FROM user2;
[ldap2pg.manager     DEBUG] Generated 2 querie(s).
[ldap2pg.script       INFO] Comparison complete.
[ldap2pg.psql        DEBUG] Closing Postgres cursor to postgres://postgres:[email protected]:5432/postgres.
[ldap2pg.psql        DEBUG] Closing Postgres connexion to postgres://postgres:[email protected]:5432/postgres.

without dry modus logfile it gives me CRITICAL ERROR

[ldap2pg.manager      INFO] Create user1.
[ldap2pg.psql        DEBUG] Doing: CREATE ROLE user1 WITH NOSUPERUSER NOBYPASSRLS NOCREATEDB INHERIT NOREPLICATION NOCREATEROLE LOGIN;
[ldap2pg.script   CRITICAL] Error while executing SQL query:
[ldap2pg.script   CRITICAL] role "user1" already exists

config file

# List of glob patterns to exclude external roles.
  blacklist: [postgres, pg_*]

#Synchronize ONLY Superusers/DB Admins
#we don't touch application user
  roles_query: |
    SELECT
        role.rolname, array_agg(members.rolname) AS members,
        {options}
    FROM
        pg_catalog.pg_roles AS role
    LEFT JOIN pg_catalog.pg_auth_members ON roleid = role.oid
    LEFT JOIN pg_catalog.pg_roles AS members ON members.oid = member
    WHERE role.rolsuper IS TRUE AND role.rolname='postgresadmins'
    GROUP BY role.rolname, {options}
    ORDER BY 1;

sync_map:
- role:
    name: POSTGRESADMINS
    options: NOLOGIN SUPERUSER

- ldap:
    base: "cn=manager,ou=Groups,ou=IAM,ou=_DomainOperations,DC=ccompany,DC=cnb"
    scope: sub
    attribute: member
  role:
    name_attribute: member.CN
    options: LOGIN
    parents: [POSTGRESADMINS]

Why ldap2pg will create user1 because it existing in ldap and postgres.

Configuration file

Support more SASL mechanism

For now, ldap2pg is limited to DIGEST-MD5, we should add more mechanism like GSSAPI, etc.

Please ask here which mech you need !

Support for PG 9.4

Currently running on pg 9.4 I receive this error msg:

[ldap2pg.psql        DEBUG] SELECT
[ldap2pg.psql        DEBUG]     role.rolname, array_agg(members.rolname) AS members,
[ldap2pg.psql        DEBUG]     role.rolbypassrls, role.rolcanlogin, role.rolcreatedb, role.rolcreaterole, role.rolinherit, role.rolreplication, role.rolsuper
[ldap2pg.psql        DEBUG] FROM
[ldap2pg.psql        DEBUG]     pg_catalog.pg_roles AS role
[ldap2pg.psql        DEBUG] LEFT JOIN pg_catalog.pg_auth_members ON roleid = role.oid
[ldap2pg.psql        DEBUG] LEFT JOIN pg_catalog.pg_roles AS members ON members.oid = member
[ldap2pg.psql        DEBUG] GROUP BY role.rolname, role.rolbypassrls, role.rolcanlogin, role.rolcreatedb, role.rolcreaterole, role.rolinherit, role.rolreplication, role.rolsuper
[ldap2pg.psql        DEBUG] ORDER BY 1;
[ldap2pg.script   CRITICAL] column role.rolbypassrls does not exist
[ldap2pg.script   CRITICAL] LINE 3:     role.rolbypassrls, role.rolcanlogin, role.rolcreatedb, r...
[ldap2pg.script   CRITICAL]             ^

Postgres 9.4 doesn't have field rolbypassrls https://www.postgresql.org/docs/9.4/static/view-pg-roles.html

YAML indentation error are not detected

Hi,

if i write managed_roles_query without space in the yml file i get different result.

correct result: space in (indent)

Using /var/lib/pgsql/ldap2pg/ldap2pg.yml.
Running in dry mode. Postgres will be untouched.
Inspecting roles in Postgres cluster...
Querying LDAP cn=bs.a.postgresadmins,o... (objectClass...
Role postgresadmins already exists in cluster. Reusing.
Nothing to do.
Comparison complete.

wrong result without indent in the yml file:
result give all wrong roles and will delete wrong roles they are NOT superuser.

Starting ldap2pg 4.7.
Using /var/lib/pgsql/ldap2pg/ldap2pg.yml.
Running in dry mode. Postgres will be untouched.
Inspecting roles in Postgres cluster...
Querying LDAP cn=bs.a.postgresadmins,o... (objectClass...
Would reassign mysuperuser objects and purge ACL on ICAT.
Would reassign mysuperuser objects and purge ACL on postgres.
Would reassign mysuperuser objects and purge ACL on template1.
Would drop mysuperuser.
Would reassign marketing objects and purge ACL on ICAT.
Would reassign marketing objects and purge ACL on postgres.
Would reassign marketing objects and purge ACL on template1.
Would drop marketing.
Would reassign sales objects and purge ACL on ICAT.
Would reassign sales objects and purge ACL on postgres.
Would reassign sales objects and purge ACL on template1.
Would drop sales.
Would reassign mzmoc objects and purge ACL on ICAT.
Would reassign mzmoc objects and purge ACL on postgres.
Would reassign mzmoc objects and purge ACL on template1.
Would drop mzmoc.
Would reassign group_role objects and purge ACL on ICAT.
Would reassign group_role objects and purge ACL on postgres.
Would reassign group_role objects and purge ACL on template1.
Would drop group_role.
Would reassign doe objects and purge ACL on ICAT.
Would reassign doe objects and purge ACL on postgres.
Would reassign doe objects and purge ACL on template1.
Would drop doe.
Generated 24 querie(s).
Comparison complete.

Allow for empty AD group in LDAP sync

We have an issue where we mirror ldap2pg.yaml across our environments, (test, dev, prod) and have a similar naming scheme for our AD groups.

  - ldap:
      base: "base_dn_goes_here"
      filter: "(CN=${ENV}_name_of_ad_group)" # Replaced at runtime with ENV
    role:
      name: '{member.cn}'
      options: LOGIN SUPERUSER
      parent:
        - ldap_roles
        - owners
      on_unexpected_dn: warn

The problem occurs when there are no users in the AD group - something that can also occur if someone quits. ldap2pg will error out saying it has no attribute member.

As far as I can see, there is no way to have ldap2pg ignore an AD group with no members.
I have tried "on_unexpected_dn" but that does not seem to work in this case.

Fine-grained privileges on every objects types

I'm sharing a proposal to manage fine-grained privileges in ldap2pg.

To manage privileges, ldap2pg loops on database and schema. But sometime, that would be nice to loop on other objects like table, views, procedures, etc.

An idea on this would be to be able to define generic object inspection:

postgres:
  object_queries:
    database: …
    schema: …
    object: …

When granting a privilege, one can append extra parameter. Matching object type will be used.

- grant:
    privilege: select_on_table
    database: …
    schema: …
    object: __all__

Privilges can use object type in grant and revoke queries:

privileges:
  select_on_table:
    grant: GRANT SELECT ON TABLE {object} IN SCHEMA {schema} TO {role};
    revoke: REVOKE SELECT ON TABLE {object} IN SCHEMA {schema} FROM {role};

__select_on_all_tables__ could be rewritten like this, saving huge query to inspect.

Feedback welcome :-)

Generate role with sAMAccountName from each user of a group

Hi,
We have already sharing about my problem with @bersace.
So you send me this config for sync_map:

- ldap:
    base: ou=users,ou=site,ou=oncodesign,dc=ldap,dc=local 
    attributes: sAMAccountName
  roles:
  - name_attribute: sAMAccountName
    options: LOGIN
- ldap:
    base: ou=groups,ou=site,ou=oncodesign,dc=ldap,dc=local 
    attributes: [groupname, members]
  roles:
  - name_attribute: groupname
    options: NOLOGIN
    member_attribute: members

I had adapted this config like this:

- ldap:
    base: ou=users,ou=site,ou=oncodesign,dc=ldap,dc=local
    filter: "(objectClass=User)"
    attributes: sAMAccountName
  roles:
    name_attribute: sAMAccountName
    options: LOGIN
- ldap:
    base: ou=groups,ou=site,ou=oncodesign,dc=ldap,dc=local
    filter: "(cn=NameofGroup)"
    attributes: [member, cn]
  roles:
    name_attribute: member
    options: NOLOGIN
    member_attribute: cn

I had change "groupename and member" attributes because they are not recognized by AD.

So after start ldap2pg in dry mode, the script would create role for all users in OU Users but not only users member of my specified group.

Maybe I need to extract CN user from a first research to the group and next creat a sync_map for each user returned to take his sAMAccountName for create a role with this name. But I don't know how to do this.

Thanks for support :)

Add check mode

It's exactly like dry run, but exists with error if something needs to be done. This allow to check whether something changed or if inspect queries are wrong.

$ ldap2pg --check
...
Nothing to do.
$ echo $?
0
$ ldap2pg --check
...
Would revoke DDL from oscar.
$ echo $?
1
$

Query for databases not scraped properly?

Hi,

with this yml configuration file:

#
# **LDAP2PG SAMPLE CONFIGURATION**
#
# This is a sample starting point configuration file for ldap2pg.yml. Including
# static roles, groups, privilege and LDAP query.
#
# This configuration assumes the following principles:
#
# - All LDAP users are grouped in `ldap_roles` group.
# - Read privileges are granted to `readers` group.
# - Write privileges are granted to `writers` group.
# - DDL privileges are granted to `owners` group.
# - We have one or more databases with public and maybe a schema.
# - Grants are not specific to a schema. Once you're writer in a database, you
#   are writer to all schemas in it.
#
# Adapt to your needs! See also full documentation on how to configure ldap2pg
# at https://ldap2pg.readthedocs.io/en/latest/config/.
#

verbosity: 5

ldap:
  #HA ldap handling
  uri: "ldap://ldap.example.com"
  binddn: uid=testuser,cn=users,cn=accounts,dc=example,dc=com
  password: password

postgres:
  dsn: postgres://user:[email protected]:5432/demodb
  # Scope the database where to purge objects when dropping roles. This is the
  # scope of grant on `__all__` databases.
  #databases_query: [postgres, appdb, olddb]
  database_query: |
    SELECT datname FROM pg_database
    WHERE datallowconn IS TRUE
    AND datname != 'rdsadmin' AND datname != 'template0' AND datname != 'template1';
  # List of managed schema. This skip pg_toast, pg_temp1, etc. but not pg_catalog.
  schemas_query: |
    SELECT nspname FROM pg_catalog.pg_namespace
    WHERE nspname NOT LIKE 'pg_%' AND nspname NOT LIKE 'information_schema';
  # Return managed roles which can be dropped or revoked.
  managed_roles_query: |
    SELECT 'public'
    UNION
    SELECT DISTINCT role.rolname
    FROM pg_roles AS role
    LEFT OUTER JOIN pg_auth_members AS ms ON ms.member = role.oid
    LEFT OUTER JOIN pg_roles AS ldap_roles
      ON ldap_roles.rolname = 'ldap_roles' AND ldap_roles.oid = ms.roleid
    WHERE role.rolname IN ('ldap_roles', 'readers', 'writers', 'owners')
        OR ldap_roles.oid IS NOT NULL
    ORDER BY 1;

  # Since readers/writer/owners groups are globals, we have a global
  # owners_query.
  owners_query: |
    SELECT DISTINCT role.rolname
    FROM pg_catalog.pg_roles AS role
    JOIN pg_catalog.pg_auth_members AS ms ON ms.member = role.oid
    JOIN pg_catalog.pg_roles AS owners
      ON owners.rolname = 'owners' AND owners.oid = ms.roleid
    ORDER BY 1;


privileges:
  # Define an privilege group `ro` with read-only grants
  ro:
  - __connect__
  - __execute__
  - __select_on_tables__
  - __select_on_sequences__
  - __usage_on_schemas__
  - __usage_on_types__

  # `rw` privilege group lists write-only grants
  rw:
  - __temporary__
  - __all_on_tables__
  - __all_on_sequences__

  # `ddl` privilege group lists DDL only grants.
  ddl:
  - __create_on_schemas__


sync_map:
# First, setup static roles and grants
- roles:
  - names:
    - ldap_roles
    - readers
    options: NOLOGIN
    comment: Custom static comment.
  - name: writers
    # Grant reading to writers
    parent: readers
    options: NOLOGIN
  - name: owners
    # Grant read/write to owners
    parent: writers
    options: NOLOGIN
  # Now grant privileges to each groups
  grant:
  - privilege: ro
    role: readers
    # Let's everyone see pg_catalog
    schema: __all__
  - privilege: rw
    role: writers
    # But avoid writers to write in pg_catalog
    schema: public
  # Allow ddl to create tables in public only
  - privilege: ddl
    role: owners
    schema: public
  # owners must have write access to pg_catalog
  - privilege: rw
    role: owners
    schema: pg_catalog
  # Grants on specific schema appdb.appns:
  - privilege: rw
    role: writers
    database: appdb
    schema: appns
  - privilege: ddl
    role: owners
    database: appdb
    schema: appns

# Now query LDAP to create roles and grant them privileges by parenting.
- ldap:
    base: cn=groups,cn=accounts,dc=example,dc=com
    filter: "(cn=dba)"
  role:
    name: '{member.cn}'
    options: LOGIN SUPERUSER
    parent:
    - ldap_roles
    - owners
    comment: "Custom comment from LDAP: {dn}"
- ldap:
    base: cn=groups,cn=accounts,dc=example,dc=com
    filter: "(cn=app*)"
  role:
    name: '{member.cn}'
    options: LOGIN
    parent:
    - ldap_roles
    - writers
    on_unexpected_dn: warn
- ldap:
    base: cn=groups,cn=accounts,dc=example,dc=com
    filter: |
      (&
        (cn=bi)
        (objectClass=*)
      )
  role:
    name: '{member.cn}'
    options: LOGIN
    parent:
    - ldap_roles
    - readers

I got a connection attempt on "rdsadmin" database which should be exlude by my databases_query.

Error message:

[ldap2pg.script       ERROR] Unhandled error:
[ldap2pg.script       ERROR] Traceback (most recent call last):
[ldap2pg.script       ERROR]   File "/usr/local/lib/python3.5/dist-packages/ldap2pg/script.py", line 94, in main
[ldap2pg.script       ERROR]     exit(wrapped_main(config))
[ldap2pg.script       ERROR]   File "/usr/local/lib/python3.5/dist-packages/ldap2pg/script.py", line 70, in wrapped_main
[ldap2pg.script       ERROR]     count = manager.sync(syncmap=config['sync_map'])
[ldap2pg.script       ERROR]   File "/usr/local/lib/python3.5/dist-packages/ldap2pg/manager.py", line 236, in sync
[ldap2pg.script       ERROR]     schemas = self.inspector.fetch_schemas(databases, ldaproles)
[ldap2pg.script       ERROR]   File "/usr/local/lib/python3.5/dist-packages/ldap2pg/inspector.py", line 233, in fetch_schemas
[ldap2pg.script       ERROR]     for dbname, psql in self.psql.itersessions(databases):
[ldap2pg.script       ERROR]   File "/usr/local/lib/python3.5/dist-packages/ldap2pg/psql.py", line 73, in itersessions
[ldap2pg.script       ERROR]     with self(dbname) as session:
[ldap2pg.script       ERROR]   File "/usr/local/lib/python3.5/dist-packages/ldap2pg/psql.py", line 142, in __enter__
[ldap2pg.script       ERROR]     self.conn = psycopg2.connect(self.connstring)
[ldap2pg.script       ERROR]   File "/usr/local/lib/python3.5/dist-packages/psycopg2/__init__.py", line 130, in connect
[ldap2pg.script       ERROR]     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
[ldap2pg.script       ERROR] psycopg2.OperationalError: FATAL:  pg_hba.conf rejects connection for host "X.X.X.X", user "username", database "rdsadmin", SSL on
[ldap2pg.script       ERROR] FATAL:  pg_hba.conf rejects connection for host "X.X.X.X", user "username", database "rdsadmin", SSL off
[ldap2pg.script       ERROR] Please file an issue at https://github.com/dalibo/ldap2pg/issues with full log.

Trying to understand your code, adding some logging in
python3.5/dist-packages/ldap2pg/inspector.py

 29 class PostgresInspector(object):
 30     def __init__(
 31             self, psql=None, privileges=None, roles_blacklist=None,
 32             shared_queries=None, **queries):
 33         self.psql = psql
 34         self.privileges = privileges or {}
 35         self.shared_queries = shared_queries or {}
 36         self.queries = queries
 37         msg = ("MARKER {q} {c}").format(q=type(self.queries), c=self.queries)     <-- Added line
 38         logging.error(msg)                                                                                       <-- Added line

Error message output:

[root                 ERROR] MARKER <class 'dict'> {'all_roles': 'SELECT\n  role.rolname, array_agg(members.rolname) AS members, {options}\nFROM\n  pg_catalog.pg_roles AS role\nLEFT JOIN pg_catalog.pg_auth_members ON roleid = role.oid\nLEFT JOIN pg_catalog.pg_roles AS members ON members.oid = member\nGROUP BY role.rolname, {options}\nORDER BY 1;\n', 'databases': 'SELECT datname FROM pg_catalog.pg_database\nWHERE datallowconn IS TRUE ORDER BY 1;\n', 'managed_roles': "SELECT 'public'\nUNION\nSELECT DISTINCT role.rolname\nFROM pg_roles AS role\nLEFT OUTER JOIN pg_auth_members AS ms ON ms.member = role.oid\nLEFT OUTER JOIN pg_roles AS ldap_roles\n  ON ldap_roles.rolname = 'ldap_roles' AND ldap_roles.oid = ms.roleid\nWHERE role.rolname IN ('ldap_roles', 'readers', 'writers', 'owners')\n    OR ldap_roles.oid IS NOT NULL\nORDER BY 1;\n", 'owners': "SELECT DISTINCT role.rolname\nFROM pg_catalog.pg_roles AS role\nJOIN pg_catalog.pg_auth_members AS ms ON ms.member = role.oid\nJOIN pg_catalog.pg_roles AS owners\n  ON owners.rolname = 'owners' AND owners.oid = ms.roleid\nORDER BY 1;\n", 'schemas': "SELECT nspname FROM pg_catalog.pg_namespace\nWHERE nspname NOT LIKE 'pg_%' AND nspname NOT LIKE 'information_schema';\n"}

The query seems to be the default one defined in the config.py (lines 348-350):
'databases': 'SELECT datname FROM pg_catalog.pg_database\nWHERE datallowconn IS TRUE ORDER BY 1;

Am I missing something here?

Other parameters in the conf file seem to be scraped correctly.

And by the way, why are you doing your own mapping and not using something like: https://pyyaml.org/wiki/PyYAML ?

Thanks in advance.

Generate role with sAMAccountName from each user of a group: Bad search filter

Ref. #128

ldap2pg -V
ldap2pg 4.11
psycopg2 2.7.5 (dt dec pq3 ext lo64)
python-ldap 3.1.0
Python 2.7.5 (default, Jul 13 2018, 13:06:57)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-28)]

OS: Centos 7

I've configured the yml file: ldap2pg.yml

ldap:
  uri: ldap://pdc.office.company.com:389
  binddn: CN=inetwork,OU=Network/Nweb Service Account,DC=office,DC=company,DC=com
  user: inetwork
  password: "*****"
 - ldap:
    base: DC=office,DC=company,DC=com
    scope: sub
    filter: >
      (&
        (objectClass=User)
        (memberOf=CN=SQL-DBA,OU=SQL Groups,DC=office,DC=company,DC=com)
      )
  role:
    name_attribute: sAMAccountName
    options: LOGIN SUPERUSER

running the command: ldap2pg -c ./ldap2pg.yml -n -vvv

[ldap2pg.manager INFO] Querying LDAP DC=office,DC=company,DC=c... (&
[ldap2pg.manager INFO] (object...
[ldap2pg.ldap DEBUG] Doing: ldapsearch -Y DIGEST-MD5 -U inetwork -W -b DC=office,DC=company,DC=com -s sub '(&
[ldap2pg.ldap DEBUG] (objectClass=User)
[ldap2pg.ldap DEBUG] (memberOf=CN=SQL-DBA,OU=SQL Groups,DC=office,DC=company,DC=com)
[ldap2pg.ldap DEBUG] )
[ldap2pg.ldap DEBUG] ' sAMAccountName
[ldap2pg.script CRITI] Failed to query LDAP: {u'info': 'Resource temporarily unavailable', 'errno': 11, 'desc': u'Bad search filter'}.

I receive the error, but if I run the command line: ldapsearch -h pdc.office.company.com -D "CN=inetwork,OU=Network/Nweb Service Account,DC=office,DC=company,DC=com" -W -Y DIGEST-MD5 -U inetwork -W -b DC=office,DC=company,DC=com -s sub '(&(objectClass=User)(memberOf=CN=SQL-DBA,OU=SQL Groups,DC=office,DC=company,DC=com))' sAMAccountName

Enter LDAP Password:
SASL/DIGEST-MD5 authentication started
SASL username: inetwork
SASL SSF: 128
SASL data security layer installed.
# extended LDIF
#
# LDAPv3
# base <DC=office,DC=company,DC=com> with scope subtree
# filter: (&(objectClass=User)(memberOf=CN=SQL-DBA,OU=SQL Groups,DC=office,DC=company,DC=com))
# requesting: sAMAccountName
#
# John Doe, It Group, Network, office.company.com
dn: CN=John Doe,OU=It Group,OU=Network,DC=office,DC=company,DC=com
sAMAccountName: doe

It returns the sAMAccountName of related group and I cannot use the CN because is space separated.
Thanks in advance.

Add __temp_on_database__ privilege

The creation of temporary table is not managed by ldap2pg.

It would be good, in the default yml file, if this temporary object creation is not removed :

REVOKE TEMP ON DATABASE <db> FROM <role>;

Create schema of same name when user is created

It common to have a schema that the same name as a user in a database. By default, postgres includes such a schema in its search_path automatically, see "section 5.8.3" in the docs.

It would be ace to have an option in ldap2pg that would create a schema of the same name when a user is newly created.

Thanks

Tiny little bug in blacklist ?

Hi,

I installed ldap2pg for testing, i don't want to synchronize all my roles and privileges with ldap2pg, but just manage named users on our database, assign to 2 roles : 'dba' and 'analysts'

So i look into the blacklist section and found this problem :

this works :

postgres:
  blacklist: [postgres, pg_*,bob_*,lol_*,twt_*, a*_ro]

this doesn't :

postgres:
  blacklist: [postgres, pg_*,*_adm,*_ro,*_rw]

And get me the following error :

Starting ldap2pg 4.6.
Using /home/perette/git/ldap2pg/ldap2pg.yml.
YAML error with /home/perette/git/ldap2pg/ldap2pg.yml: found undefined alias '_adm'
  in "/home/perette/git/ldap2pg/ldap2pg.yml", line 3, column 30

it seems the issue appear only if the wildcard is put first ? Or maybe i'm doing wrong something else :)

Support LDAP server pool

Allow to define multiple LDAP servers

manager.py chokes on special characters in dn

If the distinguished name of a user contains special characters, e.g. CN=Markus Gräßer,OU=my_group,dc=example,dc=com (name changed), execution of ldap2pg fails in apply_role_rules. The following error is thrown:

[ldap2pg.script   CRITICAL] Failed to process ('CN=Markus Gr\xc3\xa4\xc3\x9fer: 'ascii' codec can't decode byte 0xc3 in position 12: ordinal not in range(128)

I worked around it by changing apply_role_rules

def apply_role_rules(self, rules, entries):
    for rule in rules:
        for raw_entry in entries:
            entry = (
                raw_entry[0].decode('unicode_escape').encode('iso8859-1').decode('utf8'),
                raw_entry[1]
            )
            try:
                for role in self.process_ldap_entry(entry=entry, **rule):
                    yield role
            except ValueError as e:
                msg = "Failed to process %.32s: %s" % (entry, e,)
                raise UserError(msg)

The decoding line is a shameless copy from https://stackoverflow.com/a/6956914 (my python is limited).

This is on Centos 7.4.1708 with epel and pgdg-96-centos repository. ldap2pg was build according to https://ldap2pg.readthedocs.io/en/latest/install/ (the rpm in the pgdp-96 repo does not include all dependencies, but that is a bug report for the maintainer of the rpm).

Issue with join

I was using configuration provided in #128 (comment), but I got result:

[ldap2pg.script      ERROR] Traceback (most recent call last):
[ldap2pg.script      ERROR]   File "/home/raivis/virtualEnvs/ldap2pg/lib/python3.5/site-packages/ldap2pg/script.py", line 79, in main
[ldap2pg.script      ERROR]     exit(wrapped_main(config))
[ldap2pg.script      ERROR]   File "/home/raivis/virtualEnvs/ldap2pg/lib/python3.5/site-packages/ldap2pg/script.py", line 59, in wrapped_main
[ldap2pg.script      ERROR]     count = manager.sync(syncmap=config['sync_map'])
[ldap2pg.script      ERROR]   File "/home/raivis/virtualEnvs/ldap2pg/lib/python3.5/site-packages/ldap2pg/manager.py", line 375, in sync
[ldap2pg.script      ERROR]     ldaproles, ldapacls = self.inspect_ldap(syncmap)
[ldap2pg.script      ERROR]   File "/home/raivis/virtualEnvs/ldap2pg/lib/python3.5/site-packages/ldap2pg/manager.py", line 256, in inspect_ldap
[ldap2pg.script      ERROR]     entries = self.query_ldap(**mapping['ldap'])
[ldap2pg.script      ERROR] TypeError: query_ldap() got an unexpected keyword argument 'join'
[ldap2pg.script      ERROR] Please file an issue at https://github.com/dalibo/ldap2pg/issues with full log.

Is join still supported?
Maybe there is another way to get members sAMAccountName?

hello from pаdnаg.io

I had no idea this project existed, otherwise I might not have started pаdnаg.io :)

Anyways congratulations on your release and it looks excellent.

Best,
/g

Process values in template, alias filter

The need is to process LDAP values while rendering role or grant parameters. This would looks like:

- role:
    name: '{cn.slugify()}'

The trick is to subclass string values with a extended class implementing various filter.

Filters idea

upper, lower -> natives.
slugify : escape for Posgres native.

Tasks

Implementation
Documentation

Failed to query LDAP: {'matched': u'dc=,dc=', 'desc': u'No such object'}.

Hello,
I am trying to use ldap2pg but when i execute ldap2pg this happen:

The ldap2pg-yml is:

Psql and ldapsearch work.

Any help would be appreciated.

Best regards

Verify managed_roles_query

After roles are synchronized, ldap2pg could check whether postgres:managed_roles_query returns all roles as LDAP directory wants.

Background

postgres:managed_roles_query returns the list of droppable roles and roles whose privileges are managed. This query can be tricky with groups. If this query miss some roles, they will never be dropped. We should help users to ensure this query is ok.

A solution

After roles are synchronized, ldap2pg should re-run postgres:managed_roles_query query and compare it with ldaproles set. If the results are different, this is a bug in managed_roles_query.

ldap2pg should rely only on managed_roles_query to inspect privileges.

The code entrypoint is at https://github.com/dalibo/ldap2pg/blob/master/ldap2pg/manager.py#L247-L257

Output would looks like.

$ ldap2pg
Starting ldap2pg 4.5.
Using .../docs/ldap2pg.minimal.yml.
Running in real mode.
Inspecting Postgres roles...
Querying LDAP ou=people,dc=ldap,dc=lda... (objectClass...
Create albert.
...
managed_roles_query does not return all created roles. You should review it.
Synchronization complete.
$

Tasks

Review synchronisation logic to reinspect managed roles and compare
Ensure it's unit tested
Maybe document this in docs/roles.md and point to the section in the log message

I offer mentorship to this contribution :-)

Support SASL

"Failed to process [...],DC=org: Unknown attribute 'samaccountname'"

I have the following ldap2pg.yml:

ldap:
  uri: ldap://localhost:389/

sync_map:
- ldap:
    base: cn=Users,dc=cxptc,dc=org
  role:
    name_attribute: sAMAccountName
    options: LOGIN

Any sync attempt fails with a "Failed to process [...],DC=org: Unknown attribute 'samaccountname'", whatever the case used for the name_attribute. The same with "userPrincipalName".

My config is: ldap2pg 4.10, psycopg2 2.6.2, python-ldap 3.1.0 and Python 3.5.3 on Debian 9.4. LDAP (Samba AD DC 4.8.2-debian) and PostgreSQL (9.6.7) are on the same host and anonymous LDAP searches are enabled.

Manage one instance with several config files

As a Sysadmin, I need to manage one Postgres instance with several file config (e.g. to use different LDAP directory, different Postgres access, whatever).

Proposal

The key point is to scope roles managed by the config file. This could be done by customizing the SELECT query that inspect roles in Postgres instance.

postgres:
  inspect_query: |
    SELECT
        role.rolname, array_agg(members.rolname) AS members, %(options)s
    FROM
        pg_catalog.pg_roles AS role
    LEFT JOIN pg_catalog.pg_auth_members ON roleid = role.oid
    LEFT JOIN pg_catalog.pg_roles AS members ON members.oid = member
    GROUP BY role.rolname, %(options)s
    ORDER BY 1;

This is the default query.

postgres:blacklist is still applied on the result set.

Fails to discard LDAP search references

Hello Étienne Bersac,

I'm facing one issue with ldap2pg package and I hope you can help to solve it.
So, I have several OU in my LDAP structure:
Every OU have own Groups and users.

So, I place one of users from different OU to group of other OU and configured LDAP search in this way:

...
- ldap:
    base: dc=f10,dc=cloud
    scope: sub
    filter: |
      (&
      (objectClass=User)
      (memberOf=cn=psql-acl-local-bi-ro,ou=groups,ou=.f100,dc=f10,dc=cloud)
      )
  role:
    name_attribute: sAMAccountName
    options: LOGIN
    parent:
    - ldap_roles
    - acl_local_bi_ro
    comment: "Managed by LDAP"
...

When I make run I'm getting error:

[ldap2pg.manager       INFO] Querying LDAP dc=f10,dc=cloud... (&(objectCla...
[ldap2pg.ldap         DEBUG] Doing: ldapsearch -x -D cn=pgadsync,cn=Users,dc=f10,dc=cloud -W -b dc=f10,dc=cloud -s sub '(&
[ldap2pg.ldap         DEBUG] (objectClass=User)
[ldap2pg.ldap         DEBUG] (memberOf=cn=psql-acl-local-bi-ro,ou=groups,ou=.f100,dc=f10,dc=cloud)
[ldap2pg.ldap         DEBUG] )' sAMAccountName
[ldap2pg.manager      DEBUG] Got 5 entries from LDAP.
[ldap2pg.script       ERROR] Unhandled error:
[ldap2pg.script       ERROR] Traceback (most recent call last):
[ldap2pg.script       ERROR]   File "/usr/local/lib/python3.5/dist-packages/ldap2pg/script.py", line 94, in main
[ldap2pg.script       ERROR]     exit(wrapped_main(config))
[ldap2pg.script       ERROR]   File "/usr/local/lib/python3.5/dist-packages/ldap2pg/script.py", line 70, in wrapped_main
[ldap2pg.script       ERROR]     count = manager.sync(syncmap=config['sync_map'])
[ldap2pg.script       ERROR]   File "/usr/local/lib/python3.5/dist-packages/ldap2pg/manager.py", line 215, in sync
[ldap2pg.script       ERROR]     ldaproles, ldapacl = self.inspect_ldap(syncmap)
[ldap2pg.script       ERROR]   File "/usr/local/lib/python3.5/dist-packages/ldap2pg/manager.py", line 154, in inspect_ldap
[ldap2pg.script       ERROR]     entries = self.query_ldap(**mapping['ldap'])
[ldap2pg.script       ERROR]   File "/usr/local/lib/python3.5/dist-packages/ldap2pg/manager.py", line 46, in query_ldap
[ldap2pg.script       ERROR]     attributes['dn'] = [dn]
[ldap2pg.script       ERROR] TypeError: list indices must be integers or slices, not str
[ldap2pg.script       ERROR] Please file an issue at https://github.com/dalibo/ldap2pg/issues with full log.
[ldap2pg.psql         DEBUG] Closing Postgres connexion to libpq default.

When I run this query manually it is working fine for me:

$ ldapsearch -H ldap://ldap-test.f10.cloud -x -W -D "pgadsync" -b "dc=f10,dc=cloud" -s sub "(&(objectClass=User)(memberOf=cn=psql-acl-local-bi-ro,ou=groups,ou=.f100,dc=f10,dc=cloud))" sAMAccountName
Enter LDAP Password:
# extended LDIF
#
# LDAPv3
# base <dc=f10,dc=cloud> with scope subtree
# filter: (&(objectClass=User)(memberOf=cn=psql-acl-local-bi-ro,ou=groups,ou=.f100,dc=f10,dc=cloud))
# requesting: sAMAccountName
#

# Some user 1, users, .f100, f10.cloud
dn: CN=Some user 1,OU=users,OU=.f100,DC=f10,DC=cloud
sAMAccountName: xx582hj

# Some user 2, users, .f101, f10.cloud
dn: CN=Some user 2,OU=users,OU=.f101,DC=f10,DC=cloud
sAMAccountName: ME335OH

# search reference
ref: ldap://ForestDnsZones.f10.cloud/DC=ForestDnsZones,DC=f10,DC=cloud

# search reference
ref: ldap://DomainDnsZones.f10.cloud/DC=DomainDnsZones,DC=f10,DC=cloud

# search reference
ref: ldap://f10.cloud/CN=Configuration,DC=f10,DC=cloud

# search result
search: 2
result: 0 Success

# numResponses: 6
# numEntries: 2
# numReferences: 3

Could you take a look what is happening here?

can't revoke a custom privilege

I follow an example from https://ldap2pg.readthedocs.io/en/latest/privileges/#defining-custom-privilege
to define a custom privilege:

inspect query for datacl must return a rowset with two columns, the first is unused, the second is the name of grantee.

privileges:
  my_grant_all_on_database_privilege:
    type: datacl
    grant: GRANT ALL ON DATABASE {database} TO {role};
    revoke:  REVOKE ALL ON DATABASE {database} FROM {role};
    inspect: SELECT 1, 'admin'; 

sync_map:
- grant:
    database: mydb
    schema: public
    privilege: my_grant_all_on_database_privilege
    role: admin

The privilege does get granted, but it can't be revoked. My question is what would be a proper "inspect" definition in this case?

minimal configuration to sync ldap group member to postgres

Can you give a minimal configuration example for sync of ldap Group members to postgres.

I have created a ldap Group with two members.
And create some user in postgres database.

i want to get ldap Group members and only members of this Group should be add or remove from postgres database.

sync_map:
__all__:
- ldap:
   base: cn=manager,ou=Groups,ou=IAM,ou=_DomainOperations,DC=xxxx,DC=cnb
   filter: "(objectClass=groupOfNames)"
   attribute: member

  roles:
  - name_attribute: cn
    members_attribute: member.cn
    options: LOGIN

-bash-4.2$ DEBUG=Y ldap2pg -c ldap2pg.minimal.yml
[ldap2pg.script      DEBUG] Debug mode enabled.
[ldap2pg.config      DEBUG] Processing CLI arguments.
[ldap2pg.config       INFO] Starting ldap2pg 3.3.
[ldap2pg.config      DEBUG] Trying ldap2pg.minimal.yml.
[ldap2pg.config       INFO] Using /var/lib/pgsql/ldap2pg.minimal.yml.
[ldap2pg.config      DEBUG] Read color from YAML.
[ldap2pg.config      DEBUG] Read dry from YAML.
[ldap2pg.config      DEBUG] Read verbose from DEBUG.
[ldap2pg.config      DEBUG] Read ldap:uri from YAML.
[ldap2pg.config      DEBUG] Read ldap:binddn from YAML.
[ldap2pg.config      DEBUG] Read ldap:password from YAML.
[ldap2pg.config      DEBUG] Read postgres:dsn from YAML.
[ldap2pg.config      DEBUG] Read postgres:blacklist from YAML.
[ldap2pg.config      DEBUG] Read sync_map from YAML.
[ldap2pg.config      DEBUG] Configuration loaded.
[ldap2pg.config    WARNING] Empty synchronization map!
[ldap2pg.ldap        DEBUG] Ignoring: [Errno 2] No such file or directory: u'/etc/ldap/ldap.conf'
[ldap2pg.ldap        DEBUG] Ignoring: [Errno 2] No such file or directory: u'/var/lib/pgsql/ldaprc'
[ldap2pg.ldap        DEBUG] Ignoring: [Errno 2] No such file or directory: u'/var/lib/pgsql/.ldaprc'
[ldap2pg.ldap        DEBUG] Ignoring: [Errno 2] No such file or directory: u'ldaprc'
[ldap2pg.ldap        DEBUG] Connecting to LDAP server ldap://de.company.cnb:389.
[ldap2pg.ldap        DEBUG] Trying simple bind.
[ldap2pg.script    WARNING] Running in dry mode. Postgres will be untouched.
[ldap2pg.manager      INFO] Inspecting Postgres...
[ldap2pg.psql        DEBUG] Connecting to Postgres postgres://userldap:[email protected]:5432/postgres.
[ldap2pg.psql        DEBUG] Doing: SELECT datname FROM pg_catalog.pg_database
[ldap2pg.psql        DEBUG] WHERE datallowconn IS TRUE ORDER BY 1;
[ldap2pg.psql        DEBUG] Doing: SELECT
[ldap2pg.psql        DEBUG]     role.rolname, array_agg(members.rolname) AS members,
[ldap2pg.psql        DEBUG]     role.rolbypassrls, role.rolcanlogin, role.rolcreatedb, role.rolcreaterole, role.rolinherit, role.rolreplication, role.rolsuper
[ldap2pg.psql        DEBUG] FROM
[ldap2pg.psql        DEBUG]     pg_catalog.pg_roles AS role
[ldap2pg.psql        DEBUG] LEFT JOIN pg_catalog.pg_auth_members ON roleid = role.oid
[ldap2pg.psql        DEBUG] LEFT JOIN pg_catalog.pg_roles AS members ON members.oid = member
[ldap2pg.psql        DEBUG] GROUP BY role.rolname, role.rolbypassrls, role.rolcanlogin, role.rolcreatedb, role.rolcreaterole, role.rolinherit, role.rolreplication, role.rolsuper
[ldap2pg.psql        DEBUG] ORDER BY 1;
[ldap2pg.manager     DEBUG] Found role u'demo_sst' SUPERUSER NOBYPASSRLS NOCREATEDB INHERIT NOREPLICATION NOCREATEROLE LOGIN.
[ldap2pg.manager     DEBUG] Role demo_sst has members userabc.
[ldap2pg.manager     DEBUG] Found role u'userabc' SUPERUSER NOBYPASSRLS NOCREATEDB INHERIT NOREPLICATION NOCREATEROLE LOGIN.
[ldap2pg.manager     DEBUG] Ignoring role userldap. Matches u'userldap'.
[ldap2pg.manager     DEBUG] Ignoring role pg_signal_backend. Matches u'pg_*'.
[ldap2pg.manager     DEBUG] Ignoring role postgres. Matches u'postgres'.
[ldap2pg.manager     DEBUG] Found role u'test2' NOSUPERUSER NOBYPASSRLS NOCREATEDB INHERIT NOREPLICATION NOCREATEROLE LOGIN.
[ldap2pg.manager     DEBUG] Postgres inspection done.
[ldap2pg.manager     DEBUG] LDAP inspection completed. Post processing.
[ldap2pg.psql        DEBUG] Using Postgres connection to postgres://userldap:[email protected]:5432/postgres.
[ldap2pg.manager      INFO] Would reassign test2 objects and purge ACL on postgres.
[ldap2pg.manager     DEBUG] Would execute: DO $$BEGIN EXECUTE 'REASSIGN OWNED BY test2 TO ' || session_user; END$$;
[ldap2pg.manager     DEBUG] DROP OWNED BY test2;
[ldap2pg.psql        DEBUG] Connecting to Postgres postgres://userldap:[email protected]:5432/template1.
[ldap2pg.manager      INFO] Would reassign test2 objects and purge ACL on template1.
[ldap2pg.manager     DEBUG] Would execute: DO $$BEGIN EXECUTE 'REASSIGN OWNED BY test2 TO ' || session_user; END$$;
[ldap2pg.manager     DEBUG] DROP OWNED BY test2;
[ldap2pg.psql        DEBUG] Connecting to Postgres postgres://userldap:[email protected]:5432/testdb.
[ldap2pg.manager      INFO] Would reassign test2 objects and purge ACL on testdb.
[ldap2pg.manager     DEBUG] Would execute: DO $$BEGIN EXECUTE 'REASSIGN OWNED BY test2 TO ' || session_user; END$$;
[ldap2pg.manager     DEBUG] DROP OWNED BY test2;
[ldap2pg.psql        DEBUG] Using Postgres connection to postgres://userldap:[email protected]:5432/postgres.
[ldap2pg.manager      INFO] Would drop test2.
[ldap2pg.manager     DEBUG] Would execute: DROP ROLE test2;
[ldap2pg.psql        DEBUG] Using Postgres connection to postgres://userldap:[email protected]:5432/postgres.
[ldap2pg.manager      INFO] Would reassign demo_sst objects and purge ACL on postgres.
[ldap2pg.manager     DEBUG] Would execute: DO $$BEGIN EXECUTE 'REASSIGN OWNED BY demo_sst TO ' || session_user; END$$;
[ldap2pg.manager     DEBUG] DROP OWNED BY demo_sst;
[ldap2pg.psql        DEBUG] Using Postgres connection to postgres://userldap:[email protected]:5432/template1.
[ldap2pg.manager      INFO] Would reassign demo_sst objects and purge ACL on template1.
[ldap2pg.manager     DEBUG] Would execute: DO $$BEGIN EXECUTE 'REASSIGN OWNED BY demo_sst TO ' || session_user; END$$;
[ldap2pg.manager     DEBUG] DROP OWNED BY demo_sst;
[ldap2pg.psql        DEBUG] Using Postgres connection to postgres://userldap:[email protected]:5432/testdb.
[ldap2pg.manager      INFO] Would reassign demo_sst objects and purge ACL on testdb.
[ldap2pg.manager     DEBUG] Would execute: DO $$BEGIN EXECUTE 'REASSIGN OWNED BY demo_sst TO ' || session_user; END$$;
[ldap2pg.manager     DEBUG] DROP OWNED BY demo_sst;
[ldap2pg.psql        DEBUG] Using Postgres connection to postgres://userldap:[email protected]:5432/postgres.
[ldap2pg.manager      INFO] Would drop demo_sst.
[ldap2pg.manager     DEBUG] Would execute: DROP ROLE demo_sst;
[ldap2pg.psql        DEBUG] Using Postgres connection to postgres://userldap:[email protected]:5432/postgres.
[ldap2pg.manager      INFO] Would reassign userabc objects and purge ACL on postgres.
[ldap2pg.manager     DEBUG] Would execute: DO $$BEGIN EXECUTE 'REASSIGN OWNED BY userabc TO ' || session_user; END$$;
[ldap2pg.manager     DEBUG] DROP OWNED BY userabc;
[ldap2pg.psql        DEBUG] Using Postgres connection to postgres://userldap:[email protected]:5432/template1.
[ldap2pg.manager      INFO] Would reassign userabc objects and purge ACL on template1.
[ldap2pg.manager     DEBUG] Would execute: DO $$BEGIN EXECUTE 'REASSIGN OWNED BY userabc TO ' || session_user; END$$;
[ldap2pg.manager     DEBUG] DROP OWNED BY userabc;
[ldap2pg.psql        DEBUG] Using Postgres connection to postgres://userldap:[email protected]:5432/testdb.
[ldap2pg.manager      INFO] Would reassign userabc objects and purge ACL on testdb.
[ldap2pg.manager     DEBUG] Would execute: DO $$BEGIN EXECUTE 'REASSIGN OWNED BY userabc TO ' || session_user; END$$;
[ldap2pg.manager     DEBUG] DROP OWNED BY userabc;
[ldap2pg.psql        DEBUG] Using Postgres connection to postgres://userldap:[email protected]:5432/postgres.
[ldap2pg.manager      INFO] Would drop userabc.
[ldap2pg.manager     DEBUG] Would execute: DROP ROLE userabc;
[ldap2pg.manager     DEBUG] Generated 12 querie(s).
[ldap2pg.script       INFO] Comparison complete.
[ldap2pg.psql        DEBUG] Closing Postgres cursor to postgres://userldap:[email protected]:5432/template1.
[ldap2pg.psql        DEBUG] Closing Postgres connexion to postgres://userldap:[email protected]:5432/template1.
[ldap2pg.psql        DEBUG] Closing Postgres cursor to postgres://userldap:[email protected]:5432/testdb.
[ldap2pg.psql        DEBUG] Closing Postgres connexion to postgres://userldap:[email protected]:5432/testdb.
[ldap2pg.psql        DEBUG] Closing Postgres cursor to postgres://userldap:[email protected]:5432/postgres.
[ldap2pg.psql        DEBUG] Closing Postgres connexion to postgres://userldap:[email protected]:5432/postgres.

But i expect that user in ldap to be add to postgres.

dalibo / ldap2pg Goto Github PK

ldap2pg's Introduction

Features

Installation

Support

Authors

ldap2pg's People

Contributors

Stargazers

Watchers

Forkers

ldap2pg's Issues

Tasks

Filters idea

Tasks

Background

A solution

Tasks

Proposal

Recommend Projects

Recommend Topics

Recommend Org