PostgreSQL: тот же пользователь должен иметь разные роли в отношении базы данных, но роли/привилегии применяются во всем ⇐ JAVA

[Anonymous]

среда/архитектура
dbc-server (Frontend, интеграция KeyCloak)-создает/обновляет пользователей, назначает роли высокого уровня, отправляет события через Rabbitmq. Подключите, прекращайте сеансы и т. Д.
Postgresql Одиночный кластер (многие базы данных: hazna, click, ...).
Цель: единственный логический пользователь (KeyCloak имени пользователя) может присутствовать/использоваться в нескольких базах данных, но должен иметь различные привилегии для DataBase. Пример: в базе данных HAZNA пользователь должен быть владельцем (полные привилегии по этому DB/Schema), в то время как в базе данных клика одним и тем же пользователем должен быть Project_cru (без удаления).
Сводка задачи
Postgres Роли являются кластерами-глобальными. Я использовал глобальные роли (например, Project_owner, Project_cru). Я также предоставил привилегии по публичной схеме (по умолчанию) или по нескольким схемам. В результате, когда я назначаю Project_owner пользователю (или грант Project_owner Privileges на публике), этот пользователь эффективно становится владельцем в других базах данных-даже когда я явно не намеревался, чтобы для этих баз данных. Ожидайте
alice предоставлена ​​hazna_owner (или project_owner только для hazna) ⇒ полные привилегии только в hazna.
alice предоставлен Clic Db.
Что на самом деле происходит
Использование глобальной ролевой проекты_ундер + предоставление этой ролевой привилегии на общественных (или на нескольких DB/схеме) заставляет Алису действовать как владелец и в других DBS. Роли/привилегии, по-видимому, являются «глобальными» с точки зрения приложения, поэтому DB-специфическая изоляция теряется.
Соответствующий код Java (агент)
Я прикрепляю точный метод, который выполняет создание/включение/отключение/пароль/роли синхронизации-это то, где гранты/отзывы выполняются:

private void setUpdated(User user) {
if (user == null) {
return;
}

Long userCount = repository.userCount(user.getUsername());
boolean hasUser = userCount != null && userCount > 0;

if (Objects.equals(user.getAction(), UserAction.DELETED)) {
Map database = Map.of(
"database", user.getDatabaseName(),
"db_user", user.getUsername()
);

repository.executeQuery(query.getRevokeConnect(), database);
repository.executeQuery1(query.getTerminateSessions(), database);
return;
}

if (Objects.equals(user.getAction(), UserAction.PASSWORD_UPDATED)) {
Map data = Map.of(
"db_user", user.getUsername(),
"password", Objects.requireNonNull(user.getPassword()));
repository.executeQuery(query.getChangePassword(), data);
return;
}

String decryptPassword = null;
try {
decryptPassword = Utils.decrypt(user.getPassword(), ResourceUtils.getFile(agent.getRsaPrivate()));
System.out.println("decryptPassword = " + decryptPassword);
} catch (Exception e) {
log.error("Error while decrypting password");
}

Map data = Map.of(
"db_user", user.getUsername(),
"password", Objects.requireNonNull(decryptPassword),
"valid_until", user.getValidUntil() != null ? new Timestamp(user.getValidUntil()).toString() : "infinity");

if (StringUtils.hasText(user.getUsername()) && StringUtils.hasText(user.getPassword()) && !hasUser) {
repository.executeQuery(query.getCreateUser(), data);
List databases = repository.getDatabases();

for (String database : databases) {
Map info = Map.of(
"database", database,
"db_user", user.getUsername());

repository.executeQuery(query.getRevokeConnectPublic(), info);
repository.executeQuery(query.getRevokeConnect(), info);
}
}

boolean userEnabled = user.getState() == 1;
repository.executeQuery(userEnabled ? query.getLogin() : query.getNoLogin(), data);

if (!userEnabled) return;

Map databaseName = Map.of(
"database_name", user.getDatabaseName(),
"db_user", user.getUsername()
);

repository.executeQuery(query.getGrantConnect(), databaseName);
repository.executeQuery(query.getPasswordValidUntil(), data);
repository.executeQuery(query.getChangePassword(), data);

List roles = user.getRoles() == null ? new ArrayList() : user.getRoles();
List centralRoles = roles.stream().flatMap(o -> {
if (o instanceof Collection) {
return ((Collection) o).stream();
} else {
return Stream.of(o);
}
}).toList();

List systemRoles = repository.loadSystemRoles(); // tizimdagi rollar
List list = Utils.listExclude(centralRoles, systemRoles);
centralRoles = Utils.listExclude(centralRoles, list);

List userRoles = repository.userRoles(data); // userdagi mavjud rollar

List grantRoles = Utils.listExclude(centralRoles, userRoles);
List revokeRoles = Utils.listExclude(userRoles, centralRoles);

for (Object role : grantRoles) {
Map attrs = Map.of(
"db_user", user.getUsername(),
"rolename", role);
repository.executeQuery(query.getGrantRole(), attrs);
}

for (Object role : revokeRoles) {
Map attrs = Map.of(
"db_user", user.getUsername(),
"rolename", role);
repository.executeQuery(query.getRevokeRole(), attrs);
}
}
< /code>

[*] Пароль-Valid-ut-util: alter use $ {db_user} с действительным до '$ {valive_until}' < /li>
Изменение-password: alter ater $ {db_user} с паролем. с nologin
[*] login: ealge user $ {db_user} с Login
[*] Grant-Role: Grant $ {rolename} to $ {db_user}
[*] repoke: $ {rolename} от $ {db_user {db_user {db_user {db_user} [*] count-user-by-username: select count (t.*) из (select usename как имя из pg_user, где usename =: db_user union select rolname как имя из pg_roles, где rolname =: db_user) t < /li>
create-user: create ufer $ {db_user} '$ {password}'
Пользовательские роли: SELECT C.RolName AS rolename из PG_ROLES A Внутреннее соединение PG_AUTH_MEMBERS B ON A.OID = B.MEMBER Внутреннее соединение PG_ROLES C ON B.ROLEID = C.OOD, где A.ROLNAME =: DB_USER # и Current_DATABASE () =: antaMe_NAME_NAME_NAME Рули нагрузочной системы: Select R.RolName из PG_ROLES R LEAND JOIN PG_USER U ON R.ROLNAME = U.USENAME, где U.USENAME-это NULL и R.ROLNAME, не похожее на 'pg _%'
get_database: select datname из pg_dantabase, где datisteplate = false < /> < /> < /> < />

Grant-Connect: Grant подключение к базе данных $ {database_name} to $ {db_user} < /li>
renminate-sessions: select pg_cermint_backend (pid). '$ {Database}' и Usename = '$ {db_user}' < /li>
Revoke-connect-public: repoke connect на базе данных $ {база данных} из public < /li>
< /ol>
root cane (my my meally) < /p> prebure-roles rolesq rolesq. Грантовая роль пользователю-это картирование в кластере. Привилегии предоставляются на объектах (схема/таблицы) внутри каждой базы данных. Если вы предоставили Project_Owner Privileges на публике во многих DBS, а Алиса предоставлена ​​Project_Owner, у нее будут эти привилегии повсюду, где существуют эти гранты схемы. Я пытался добавить фильтрацию current_database (), но это не работает для ролевого членства. < /P>
Что я попробовал /считал < /p>
Роли префикса на базу данных (например, hazna_owner, click_owner)-работает, но создает много ролей и управления. Схемы click_data, назначить привилегии глобальным ролям на схему) - работает только в том случае, если я не предоставляю привилегии общественности. Если я непреднамеренно предоставлен на публике, утечка привилегий. Это централизует картирование, но требует дополнительного хранилища данных и логики синхронизации. DB B)-Каков рекомендуемый, безопасный подход с производством с наименее оперативной болью? Держите таблицу отображения и позвольте агенту запустить грант/отображение на отображение? Пример надежного SQL -запроса, чтобы получить роли, которые имеют привилегии в данной схеме/базе данных? (Я думал, что has_schema_privilege (C.RolName ,: Schema_Name, 'Использование') или HAS_TABLE_PRIVILEGE, является ли это правильным/заслуживающим доверия?) Создание и гранты (сценарии /схема миграции), чтобы избежать ручных ошибок? HAS_SCHEMA_PRIVILEGE) для реализации рекомендуемого подхода.>

Подробнее здесь: https://stackoverflow.com/questions/797 ... s-privileg