On 2025-06-25 17:55:12 +0200, raphi wrote: > Am 25.06.2025 um 17:33 schrieb Peter J. Holzer: > > On 2025-06-25 14:42:26 +0200, raphi wrote: > > > That's not how the identiy principle works, at least not how it's > > > implement in our company. A user in ldap has a direct relation to > > > one digital entity, either a token from an application or > > > certificate from a physical person (maybe some AD shenanigans > > > also). We don't have digital entities for teams, that's what's > > > missing. For it to work they (security) would need to allow to > > > weaken this principle and as you said, allow everyone who has a > > > certain role to manage the associated user in LDAP, like setting a > > > new password. > > That user shouldn't have a password, since nobody is authenticating as > > that user. It also doesn't have to exist in LDAP. It's just a role in > > the database. > hmm I don't follow, maybe I was doing it wrong?
I'm thinking of something like this:
Roles assigned to people are in LDAP, and only they have passwords.
Application roles don't have to be in LDAP (maybe there are operational
reasons to have them there, but PostgreSQL doesn't need them) and don't
have passwords.
So assuming I have an application testapp which needs its own database
and and admin account and I am one of the admins, I would create it like
this:
postgres=# create role testapp_admin;
CREATE ROLE
postgres=# create database testapp owner testapp_admin;
CREATE DATABASE
postgres=# grant testapp_admin to hjp with inherit false, set true;
GRANT ROLE
Now, when I want to do some work on that database I can't login directly
as testapp_admin (that role doesn't have a password), but I can login as
hjp:
% psql -h localhost -U hjp -d testapp
Password for user hjp:
Null display is "(∅)".
Line style is unicode.
Border style is 2.
Unicode border line style is "double".
Timing is on.
Expanded display is used automatically.
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression:
off)
Type "help" for help.
testapp=>
Because of the "inherit false" I can't do anything as me:
testapp=> create table foo(id serial primary key, t text);
ERROR: permission denied for schema public
But I can change my role to testapp_admin:
testapp=> set role testapp_admin ;
SET
And then I can do my work:
testapp=> create table foo(id serial primary key, t text);
CREATE TABLE
testapp=> \dt foo
List of relations
╔════════╤══════╤═══════╤═══════════════╗
║ Schema │ Name │ Type │ Owner ║
╟────────┼──────┼───────┼───────────────╢
║ public │ foo │ table │ testapp_admin ║
╚════════╧══════╧═══════╧═══════════════╝
If I turn on logging, these actions will also be attributed to me in the
log:
2025-06-26 14:10:26.458 CEST [32945] [unknown]@[unknown] LOG:
connection received: host=127.0.0.1 port=45780
2025-06-26 14:10:26.499 CEST [32945] hjp@testapp LOG:
connection authenticated: identity="hjp" method=scram-sha-256
(/etc/postgresql/16/main/pg_hba.conf:125)
2025-06-26 14:10:26.499 CEST [32945] hjp@testapp LOG:
connection authorized: user=hjp database=testapp application_name=psql
SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256)
2025-06-26 14:11:38.641 CEST [32945] hjp@testapp LOG:
statement: create table bar(id serial primary key, t text);
(There are variations of the theme. For example, if your databases are
on Linux and your developers/admins are familiar with the commandline
you could use pg_ident.conf - which I use a lot)
> An ansible playbook creates a role for someone and sends the password
> by email to the user who wanted the role created.
Right. That's what I would do, too. Except that I would not set a
password and grant the role to the intended user(s) instead.
(We do currently have role accounts with passwords - but they are a bit
of a pain, so maybe I should try to get rid of them.)
hjp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | h...@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
