Has anyone tried ldap2pg on CockroachDB?

[arjan-saly-tfs]

Hi,

Sorry if this topic has been answered before. If so, I can't find it.

I'm currently exploring CockroachDB, which has a strong PostgreSQL base. The catalogue is the same in its base, although not all PostreSQL default functions are supported. The majority of my PostgreSQL Catalogue queries work flawless though.

For connecting with Active Directory / LDAP, I'm facing the same issue as with PostgreSQL: Authentication through LDAP /AD is possible, but the user must exist as a database user and CockroachDB does not provide any user synchronization.

Giving the high level of similarity, I start to wonder: Would ldap2pg work on CockroachDB as well?
Has anybody in the community tried this already? If so, what were your experiences?

Any useful thoughts on this?

Thanks!

Arjan

[bersace]

No idea. It seems that CockroachDB is not a fork but a Postgres-wire compatible DB. I need feedback for this.

[arjan-saly-tfs]

Thanks for answering @bersace. Since no other replies are given, I assume the community has not tried yet. Fair enough, would have been nice, but my expectations were low ;-).

I tried myself running CockroachDB version v22.1.10 and ldap2pg version 5.8. Unfortunately it failed on a CREATE ROLE statement:
Error while executing SQL query: [ldap2pg.script CRITI] at or near "nobypassrls": syntax error [ldap2pg.script CRITI] DETAIL: source SQL: [ldap2pg.script CRITI] CREATE ROLE "<Username Unrevealed>" WITH NOBYPASSRLS LOGIN NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION SUPERUSER

Checking CockroachDB Command reference https://www.cockroachlabs.com/docs/stable/create-role.html#role-options confirms that the NOBYPASSRLS parameter does not exist for CockroachDB. The same applies to these parameters used:

• INHERIT
• NOREPLICATION
• SUPERUSER

One question comes up is if the parameters NOBYPASSRLS, INHERIT and NOREPLICATION really are needed.
The unsupported SUPERUSER parameter should be handled differently by creating the user first, then granting the "admin" privilege.

I'm not a python developer, but based on this single message, I would think it's not be a very big change to make this piece of code work for CockroachDB as well. Big question of course is what's next...... There are not many syntax differences between CockroachDB and PostgreSQL, but they certainly are there.

On the other hand, from what I've seen from CockroachDB so far (played around with it a bit), the main differences are in the engine rather than in SQL syntax. Also, when connecting to CockroachDB, the PostgreSQL connectors are used, which seems to indicate a high level of similarity to me.

Not sure if there's a developer willing to dig deeper into this, but just in case I'll leave the version details of both ldap2pg and CockroachDB distributions I used:

For ldap2pg:

• ldap2pg 5.8
• psycopg2 2.9.2 (dt dec pq3 ext lo64) libpq 14.2
• python-ldap 3.2.0
• Python 3.10.6 (main, Nov 2 2022, 18:53:38) [GCC 11.3.0]

For CockroachDB:

• Build Tag: v22.1.10
• Build Time: 2022/10/27 20:08:22
• Distribution: CCL
• Platform: windows amd64 (x86_64-w64-mingw32)
• Go Version: go1.17.11
• C Compiler: gcc 6.5.0
• Build Commit ID: 477af1d876e3e62b26900854c2f33eaa7cec73db
• Build Type: release

[bersace]

Hi @arjan-saly-tfs . Thanks for the feed back, this is awesome !

ldap2pg already adapts options by inspecting pg_roles columns. Can you share the output of \d pg_catalog.pg_authid ?

[arjan-saly-tfs]

Hi @bersace,

Here's the output:

/defaultdb> \d pg_catalog.pg_authid
   column_name   |  data_type  | is_nullable | column_default | generation_expression | indices | is_hidden
-----------------+-------------+-------------+----------------+-----------------------+---------+------------
  oid            | OID         |    true     | NULL           |                       | {}      |   false
  rolname        | NAME        |    true     | NULL           |                       | {}      |   false
  rolsuper       | BOOL        |    true     | NULL           |                       | {}      |   false
  rolinherit     | BOOL        |    true     | NULL           |                       | {}      |   false
  rolcreaterole  | BOOL        |    true     | NULL           |                       | {}      |   false
  rolcreatedb    | BOOL        |    true     | NULL           |                       | {}      |   false
  rolcanlogin    | BOOL        |    true     | NULL           |                       | {}      |   false
  rolreplication | BOOL        |    true     | NULL           |                       | {}      |   false
  rolbypassrls   | BOOL        |    true     | NULL           |                       | {}      |   false
  rolconnlimit   | INT4        |    true     | NULL           |                       | {}      |   false
  rolpassword    | STRING      |    true     | NULL           |                       | {}      |   false
  rolvaliduntil  | TIMESTAMPTZ |    true     | NULL           |                       | {}      |   false
(12 rows)

[bersace]

Ok. That's a misbehaviour of CoackroachDB. It has internal structure for BYPASSRLS feature but does not accept the keyword. Odd.

Something that may be done is the ability to override role features in instance right from ldap2pg.yaml. Something like

postgres:
  role_options: LOGIN CREATEROLE CREATEDB  # not: BYPASSRLS SUPER INHERIT

What do you think of this ?

[arjan-saly-tfs]

Exactly what I thought when I saw the result of this table. Perhaps this is done for a different type of compatibility, who knows.

If your solution works, that might be a nice solution for normal users, but won't work for superusers.

I'm not a fan of maintaining superusers through ldap anyway, but I do need to be able to maintain users with elevated, admin-level, near-superuser, privileges. I have not found one (or a set) like that in the ldap2pg "Well known Privileges" and I could not figure out how to write proper custom privileges within the timebox we set for it.

To stay on the clear side: Would this option work already in 5.8? Or does it need to added to ldap2pg logic first?
Wondering because I added the line you suggested to my test environment and it does not change anything in the result, but I don't get an error message either.....

[bersace]

Hi @arjan-saly-tfs . I need a patch to ldap2pg to add such new postgres:role_options configuration to override internal logic. I don't have time allocated for develop ldap2pg until a few weeks.

If your solution works, that might be a nice solution for normal users, but won't work for superusers.

Why ? It's not related to any user. It's just the bootstrap inspection of PostgreSQL by ldap2pg. ldap2pg supports old PostgreSQL versions that do not have BYPASSRLS and other new options. For this, ldap2pg queries pg_authid columns and determines available role options. The idea is to bypass pg_authid introspection right from the YAML. This needs some new code in ldap2pg.

Can you confirm we are ok with such solution ?

[arjan-saly-tfs]

Hi @bersace , perhaps I misunderstand, but my thought is that since you mark out SUPER as a role option, it's not possible anymore to create/manage superusers........ ?

If that's not the case and it's still possible to create/maintain admin-level users and/or superusers in an easy way, yeah, seems like a good plan to me.

[bersace]

ok, my bad. Yes, you can't user SUPERUSER with ldap2pg and CoackroachDB.

Is admin a parent role ? If yes, you can simply add it to parent of super users.

[arjan-saly-tfs]

Interesting question. In the context of ldap2pg parent roles, I tried defining SUPERUSER as a parent role for a PostgreSQL environment. That didn't seem to work well, the users could not do what we expected for a SUPERUSER. There seems to be a difference between SUPERUSER being inherited from a parent role, which effectively happens when granted through a parent, or SUPERUSER being set as an option to the user itself. Was not an issue for me as we have a single admin group anyway.

Within CockroachDB however, the admin database role actually is a role to grant to an existing user, hence a parent role indeed. So, yeah, it seems like that would work for CockroachDB.

I can try this easily once you implemented the role_options you suggested...