[
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)
