7 ms·
Well, a client invited me to evaluate PostgreSQL. Strong MSSQL shop, thousands of databases, millions of customers, tens thousands internal users. Big pile of l
by zaat 7y ago
Well, a client invited me to evaluate PostgreSQL. Strong MSSQL shop, thousands of databases, millions of customers, tens thousands internal users. Big pile of licensing money they were eager to save.
We investigated the options for LDAP and Kerberos integration, commercial and free. Turns out there is no decent way to do it and grant permissions based on LDAP groups. There wasn't even a a half decent way. The only options were way to hackish to consider. MS SQL still own the house.
- AnonymousPlanet 7y agoWhy would you want LDAP integration for a database? Maybe that kind of messed up requirement is part of the problem? One of the bigger issues with places that have made themselves dependent on MS solutions is that MS software completely permeates the place. Then when the pain of going full MS is too big, they are only looking for drop in replacements of existing parts. This will never go well. MS software never plays well with alternatives. If you have let yourself slide too far down the slippery slope of MS Office integrations and MS Sharepoints and the like, you will have to pay the MS price. Or you have to be willing to chuck out the entire lot.
- al_ak 7y agoThis is very typical in an enterprise shop where you have end users connecting directly to the database for reporting purposes. Being able to take advantage of your existing directory structure and security groups is huge.
- amaccuish 7y agoExactly. Why should I have to rewrite a whole ACL infrastructure when I can store it all in one place, and it doesn't matter how you access the database, it's all enforced.
- AnonymousPlanet 7y agoWhy do end users have direct access to a database? What do they do with it? Copy and paste from their Excel sheets into a GUI database client? Why is there no layer of software in between?
- skylanh 7y agoSituations where you have enterprise setups have users measured in the 10s of thousands. There will be existing tooling around ACL management (typically AD). User Access Management groups, geo-separated local account manager, multiple help-desks to allow for disaster plans (pandemics, fires, localized grid failures). Access can be as simple as: - my application will use a system-account, and we'll manage access - my application will pass user credentials (based on AD), these are the groups (based on AD) that have read/write/delete access; regional team leaders will request access using existing UAM, regional managers will authorize requests, your existing tooling will process those requests as you are the designated authority over those groups Changing this requires either exceptional levels of observable returns on effort, or extreme will-power and political power. During evaluation of MS SQL / PostgreSQL / whatever, fitting into existing UAM will be a requirement.
- thu2111 7y agoBecause the world and especially the enterprise world is full of smart analysts who aren't programmers but who are pretty hot shots with SQL and Excel. They can answer a LOT of ad-hoc business queries just with those two tools and don't need expensive slow programmers to spend 3 months twiddling JIRA tickets when they could get the same answer in a few hours, when they have direct database access.
- zaat 7y agoBecause certain groups of users need the same sets of permissions on hundreds of databases and servers. And the members of those groups are constantly changing. In addition this ties an identity across system and facilitates SSO on the one hand and auditing on the other. There's more to it but these are the most obvious. This is a common, standard requirement in enterprise systems, and it seem quite sensible. If you have a better, more robust solution, there are billions in this market, in the most literal way. One more thing, although Active Directory is a standard in the enterprise, LDAP is an open protocol that existed before Microsoft were dreaming on being a player in the enterprise field and have at least half a dozen open source implementations.
- AnonymousPlanet 7y ago> Because certain groups of users need the same sets of permissions on hundreds of databases and servers. And the members of those groups are constantly changing. In addition this ties an identity across system and facilitates SSO on the one hand and auditing on the other. There's more to it but these are the most obvious. That is precisely what I meant with messed up requirements. Why would any of your SSO be relevant on the database level? A database user should be coupled to an application, not to a single user in AD. If you are letting constantly changing users do their own SQL requests on DB level, there is something rotten in the first place. > This is a common, standard requirement in enterprise systems, and it seem quite sensible. If you have a better, more robust solution, there are billions in this market, in the most literal way. I have never come across this kind of setup in a normal enterprise environment. There, you have everything behind some kind of enterprise software. Data entry, auditing, etc. However, you could have need for this if we are talking developers sharing a single database and you don't want to manage accounts and passwords separately. You said the solutions you found for Postgres were too "hacky". I think if you have a setup where enterprise users who are not developers or DBAs need personal access on DB level, your setup is quite "hacky" to begin with. > One more thing, although Active Directory is a standard in the enterprise, LDAP is an open protocol that existed before Microsoft were dreaming on being a player in the enterprise field and have at least half a dozen open source implementations. Microsoft has bent LDAP and Kerberos in its own way. Trying to use AD like an LDAP server is full of unpleasant surprises dealt to you by badly documented or outright undocumented "features", and useless error messages and logging. Believe me, it's a major nightmare to get anything integrated with AD.
- egeozcan 7y agoLDAP is amazingly convenient. Why not centralize the user/group structure and use it for db access as well?
- ldng 7y agoWould enabling LDAP auth at PAM level be too hackish for the purpose ?
- zaat 7y agoNot at all if was possible. But PosgtreSQL only map users that already exists in PostgreSQL to LDAP users, matching the user name. And there is no way to map the groups. That's ridiculous. The hackish implementations are syncing users from LDAP to PostgreSQL, on schedule. There are more than 3 different implementations of this madness.