You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@knox.apache.org by "ASF GitHub Bot (Jira)" <ji...@apache.org> on 2022/12/06 08:08:00 UTC
[jira] [Work logged] (KNOX-2851) Support additional username/password settings in PostgeSQL
[ https://issues.apache.org/jira/browse/KNOX-2851?focusedWorklogId=831316&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-831316 ]
ASF GitHub Bot logged work on KNOX-2851:
----------------------------------------
Author: ASF GitHub Bot
Created on: 06/Dec/22 08:07
Start Date: 06/Dec/22 08:07
Worklog Time Spent: 10m
Work Description: smolnar82 opened a new pull request, #699:
URL: https://github.com/apache/knox/pull/699
## What changes were proposed in this pull request?
From now on our end-users can define the username and password connection properties as `gateway_database_user` and `gateway_database_password` aliases so that this sensitive information should not be included in the supplied JDBC URL.
Additionally, SSL configuration happens for both connection types (JDBC URL or separate property declaration).
## How was this patch tested?
Updated existing JUnit test cases and conducted the following manual testing:
I configured my local PostgreSQL server to accept SSL connections, enforce passwords and created the following user/DB for testing:
```
postgres=# CREATE USER smolnar LOGIN PASSWORD 'cloudera';
CREATE ROLE
postgres=# CREATE DATABASE mydatabase WITH OWNER = smolnar;
CREATE DATABASE
```
```
$ psql "sslmode=verify-full host=localhost dbname=mydatabase sslcert=/usr/local/var/postgresql\@14/root.crt sslrootcert=/usr/local/var/postgresql\@14/server.crt sslkey=/usr/local/var/postgresql\@14/root.key user=smolnar"
Password for user smolnar:
psql: error: connection to server at "localhost" (::1), port 5432 failed: fe_sendauth: no password supplied
$ psql "sslmode=verify-full host=localhost dbname=mydatabase sslcert=/usr/local/var/postgresql\@14/root.crt sslrootcert=/usr/local/var/postgresql\@14/server.crt sslkey=/usr/local/var/postgresql\@14/root.key user=smolnar password=cloudera"
psql (14.6 (Homebrew))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
mydatabase=>
```
When all this was done, I configured Knox to use `JDBCTokenStateService` as follows and generated tokens:
```
<property>
<name>gateway.service.tokenstate.impl</name>
<value>org.apache.knox.gateway.services.token.impl.JDBCTokenStateService</value>
</property>
<property>
<name>gateway.database.type</name>
<value>postgresql</value>
</property>
<property>
<name>gateway.database.connection.url</name>
<value>jdbc:postgresql://localhost:5432/mydatabase?user=smolnar&password=cloudera</value>
</property>
<property>
<name>gateway.database.ssl.enabled</name>
<value>true</value>
</property>
<property>
<name>gateway.database.ssl.truststore.file</name>
<value>/usr/local/var/postgresql@14/root.crt</value>
</property>
<property>
<name>gateway.database.ssl.verify.server.cert</name>
<value>true</value>
</property>
```
**Test case 1:** username and password in the JDBC URL
<property>
<name>gateway.database.connection.url</name>
<value>jdbc:postgresql://localhost:5432/mydatabase?user=smolnar&password=cloudera</value>
</property>
**Test case 2:** username in the JDBC URL, password is saved as an alias
```
$ bin/knoxcli.sh create-alias gateway_database_password --value cloudera
gateway_database_password has been successfully created.
```
```
<property>
<name>gateway.database.connection.url</name>
<value>jdbc:postgresql://localhost:5432/mydatabase?user=smolnar</value>
</property>
```
**Test case 3:** username and password are saved as aliases (password was already saved, see Test case 2)
```
$ bin/knoxcli.sh create-alias gateway_database_user --value smolnar
gateway_database_user has been successfully created.
```
```
<property>
<name>gateway.database.connection.url</name>
<value>jdbc:postgresql://localhost:5432/mydatabase</value>
</property>
```
Confirmed that all tokens were created successfully:
```
mydatabase=> select * from knox_tokens kt, knox_token_metadata meta where kt.token_id = meta.token_id;
token_id | issue_time | expiration | max_lifetime | token_id | md_name | md_value
--------------------------------------+---------------+---------------+---------------+--------------------------------------+----------+----------------------------------------------------------------------------------
292a7da4-cc88-4485-88bb-5c3b1fcb6072 | 1670311875115 | 1670315475053 | 1670916675115 | 292a7da4-cc88-4485-88bb-5c3b1fcb6072 | passcode | JMa377+977+977+9A2JvwrVGagQp77+977+977+977+977+977+9azPvv73vv73NjWvvv71Q77+9
292a7da4-cc88-4485-88bb-5c3b1fcb6072 | 1670311875115 | 1670315475053 | 1670916675115 | 292a7da4-cc88-4485-88bb-5c3b1fcb6072 | enabled | true
292a7da4-cc88-4485-88bb-5c3b1fcb6072 | 1670311875115 | 1670315475053 | 1670916675115 | 292a7da4-cc88-4485-88bb-5c3b1fcb6072 | userName | admin
292a7da4-cc88-4485-88bb-5c3b1fcb6072 | 1670311875115 | 1670315475053 | 1670916675115 | 292a7da4-cc88-4485-88bb-5c3b1fcb6072 | comment | token 1
6c27e41c-6bab-4414-9a6c-595b9a63dc6a | 1670312088270 | 1670315688222 | 1670916888270 | 6c27e41c-6bab-4414-9a6c-595b9a63dc6a | passcode | S1nvv70XEXLvv71c77+977+9dO+/vSDvv73vv71Z77+9XiJBY++/vTrvv71+77+977+977+977+9PwQ=
6c27e41c-6bab-4414-9a6c-595b9a63dc6a | 1670312088270 | 1670315688222 | 1670916888270 | 6c27e41c-6bab-4414-9a6c-595b9a63dc6a | enabled | true
6c27e41c-6bab-4414-9a6c-595b9a63dc6a | 1670312088270 | 1670315688222 | 1670916888270 | 6c27e41c-6bab-4414-9a6c-595b9a63dc6a | userName | admin
6c27e41c-6bab-4414-9a6c-595b9a63dc6a | 1670312088270 | 1670315688222 | 1670916888270 | 6c27e41c-6bab-4414-9a6c-595b9a63dc6a | comment | token 2
9c625eb4-55ee-4fce-b52e-4df83a05417f | 1670312914318 | 1670316514281 | 1670917714318 | 9c625eb4-55ee-4fce-b52e-4df83a05417f | passcode | Thvvv717Lu+/vVAAFu+/ve+/vTjvv73vv70iSzvvv71877+9aO+/vQsZZCfvv73vv71PHO+/vU8=
9c625eb4-55ee-4fce-b52e-4df83a05417f | 1670312914318 | 1670316514281 | 1670917714318 | 9c625eb4-55ee-4fce-b52e-4df83a05417f | enabled | true
9c625eb4-55ee-4fce-b52e-4df83a05417f | 1670312914318 | 1670316514281 | 1670917714318 | 9c625eb4-55ee-4fce-b52e-4df83a05417f | userName | admin
9c625eb4-55ee-4fce-b52e-4df83a05417f | 1670312914318 | 1670316514281 | 1670917714318 | 9c625eb4-55ee-4fce-b52e-4df83a05417f | comment | token 3
```
Issue Time Tracking
-------------------
Worklog Id: (was: 831316)
Remaining Estimate: 0h
Time Spent: 10m
> Support additional username/password settings in PostgeSQL
> ----------------------------------------------------------
>
> Key: KNOX-2851
> URL: https://issues.apache.org/jira/browse/KNOX-2851
> Project: Apache Knox
> Issue Type: Task
> Reporter: Sandor Molnar
> Assignee: Sandor Molnar
> Priority: Major
> Fix For: 2.0.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Currently, there are two ways to set up a connection from Knox to a PostgreSQL instance:
> * either using the connection JDBC URL
> * or specifying the connection parameters (host, port, etc...) one by one
> In the case of the first option, the username and password information has to be part of the URL in plain text which should be avoided. Therefore these two sensitive parameters should be configured just like we do in the 2nd case.
> Moreover, SSL configuration (added in KNOX-2598) is only available for the second case. This has to be addressed as well.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)