You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by "Meeks, Andrew" <an...@vt.edu> on 2018/07/18 18:25:58 UTC
RE: Redacting db user name and password from XML
I feel as though a recent bit of code is closely related to the topic in this old thread. The Cayenne <data-source> xml element supports a password with a path external to the application. The observed environment properties appear to only include JDBC_PASSWORD_PROPERTY which seems to only support a literal password. For Spring developers, an integration suggestion is included below.
I would like to say, in agreement with the earlier thread, that the documentation at: https://cayenne.apache.org/docs/4.1/cayenne-guide/#appendix-a-configuration-properties is unclear in one respect. I think it needs to state that all cayenne.jdbc.* properties must be defined in order for environment properties to override (or correctly override) the definition of data-source in the xml file. That would have saved me a lot of stepping into code before I found what was happening in DelegatingDataSourceFactory. shouldConfigureDataSourceFromProperties(DataNodeDescriptor dataNodeDescriptor);
If you have a Spring Project and would like to control your <data-source> through a more spring-like configuration, here are relevant snippets from files of how I set it up based on examples in the cayenne documentation:
-- file: application.properties --
cayenne.jdbc.driver=oracle.jdbc.driver.OracleDriver
cayenne.jdbc.max_connections=20
cayenne.jdbc.min_connections=1
cayenne.jdbc.username=SCOTT
--file: application-dev.properties--
cayenne.jdbc.passwordSource=C:\\home\\apps-config\\testProject\\cayenne_pw.txt
cayenne.jdbc.url=jdbc:oracle:thin:@//localhost:1521/xe
--file: application-prod.properties--
cayenne.jdbc.passwordSource=C:\\home\\apps-config\\testProject\\cayenne_pw_prod.txt
cayenne.jdbc.url=jdbc:oracle:thin:@//prod.db.example.com:1521/prod_svc
--file: SpringMvcConfig.java--
@Configuration
public class MvcConfig extends WebMvcConfigurerAdapter {
@Value("${cayenne.jdbc.url}")
String cayenneClientUrl;
@Value("${cayenne.jdbc.driver}")
String cayenneDriverProperty;
@Value("${cayenne.jdbc.passwordSource}")
String cayennePasswordSource;
@Value("${cayenne.jdbc.username}")
String cayenneUsername;
@Value("${cayenne.jdbc.min_connections}")
String cayenneMinConnections;
@Value("${cayenne.jdbc.max_connections}")
String cayenneMaxConnections;
@Bean(name="serverRuntime")
public ServerRuntime serverRuntime(){
String url = cayenneClientUrl;
String driver = cayenneDriverProperty;
String username = cayenneUsername;
String pwSource = cayennePasswordSource;
String maxConnections = cayenneMaxConnections;
String minConnections = cayenneMinConnections;
String actualPassword = getPassword(pwSource);
Module cayenneModule = binder ->
ServerModule.contributeProperties(binder).put(Constants.JDBC_DRIVER_PROPERTY, driver).put(Constants.JDBC_URL_PROPERTY, url)
.put(Constants.JDBC_USERNAME_PROPERTY, username).put(Constants.JDBC_PASSWORD_PROPERTY, actualPassword)
.put(Constants.JDBC_MIN_CONNECTIONS_PROPERTY, minConnections).put(Constants.JDBC_MAX_CONNECTIONS_PROPERTY, maxConnections);
ServerRuntimeBuilder builder = ServerRuntime.builder();
builder.addConfig("cayenne-testProject.xml");
builder.addModule(cayenneModule);
ServerRuntime createdRuntime = builder.build();
try {
// this will tell us on startup if it cannot connect to the database
createdRuntime.getDataSource().getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return createdRuntime;
}
private String getPassword(String passwordSourcePath) {
try {
return new String(Files.readAllBytes(Paths.get(passwordSourcePath)));
} catch (IOException e) {
return null;
}
}
}
--End of code snippets--
The primary advantage with this is that server admins can play around with the database parameters and developers will not need to build with a new cayenne xml, while still leaving the database password in an external file. Once each Spring profile has its default values, then the server admins need only specify a parameter rather than specifying all parameters. We have somewhat regular datasource url changes for production which precipitated this refactor. The code creating the ServerRuntime bean is intentionally verbose here to make it clearer in debugging what values are getting passed through. Obviously application.properties would contain variables that are known to be the same in different profiles, your situation may vary and some may need to go in to application-PROFILE.properties or vice-versa.
I hope that is useful to someone,
Andrew
-----Original Message-----
From: Pascal Robert <pr...@druide.com>
Sent: Friday, January 19, 2018 10:01 AM
To: user@cayenne.apache.org
Subject: Re: Redacting db user name and password from XML
I confirm this behaviour, and I switched back to XMLPoolingDataSourceFactory.
> Le 18 janv. 2018 à 03:59, Andrus Adamchik <an...@objectstyle.org> a écrit :
>
> Ah, mystery solved. Looking at the code, more specifically to use properties for a given DataSource Cayenne would expect you to specify at least DB URL and DB driver. Username/password are optional.
>
> Andrus
>
>
>> On Jan 18, 2018, at 11:46 AM, Nikita Timofeev <nt...@objectstyle.com> wrote:
>>
>> Hi all,
>>
>> Made some research, and here is what I've found.
>>
>> Cayenne do switch to System properties (defined via -D or with DI
>> binding) automatically but it do so only when all properties are
>> defined.
>>
>> So this code will work as expected, and use overridden DataSource properties:
>>
>> ServerRuntime cayenneRuntime =
>> ServerRuntime.builder().addConfig("cayenne-project.xml")
>> .addModule(binder -> ServerModule.contributeProperties(binder)
>> .put(Constants.JDBC_DRIVER_PROPERTY, "com.mysql.jdbc.Driver")
>> .put(Constants.JDBC_URL_PROPERTY,
>> "jdbc:mysql://localhost:3306/test")
>> .put(Constants.JDBC_USERNAME_PROPERTY, "user")
>> .put(Constants.JDBC_PASSWORD_PROPERTY, "password"))
>> .build();
>>
>> While this will ignore password and use DataSource properties from XML:
>>
>> ServerRuntime cayenneRuntime =
>> ServerRuntime.builder().addConfig("cayenne-project.xml")
>> .addModule(binder -> ServerModule.contributeProperties(binder)
>> .put(Constants.JDBC_PASSWORD_PROPERTY, "password"))
>> .build();
>>
>> I will add some information to logs, so at least it wouldn't be
>> surprising as it is now.
>> But maybe we should change this to enable override of separate properties.
>>
>> On Thu, Jan 18, 2018 at 8:27 AM, Andrus Adamchik <an...@objectstyle.org> wrote:
>>>
>>>
>>>> On Jan 17, 2018, at 11:05 PM, Pascal Robert <pr...@druide.com> wrote:
>>>>
>>>> Ahhh… If I change the factory in the node definition to org.apache.cayenne.configuration.server.PropertyDataSourceFactory, it does read the command-line properties.
>>>
>>> That should sorta happen automatically. We are still looking why it doesn't.
>>>
>>> Andrus
>>>
>>
>>
>>
>> --
>> Best regards,
>> Nikita Timofeev
>
RE: Redacting db user name and password from XML
Posted by "Meeks, Andrew" <an...@vt.edu>.
Hi John,
Well, when I tried only providing the url and driver the username and password from the xml file. So, rather than failing with an exception during startup, Cayenne would just have a bad username password error later on when it first tried to establish a connection. Min connection pool and max connection pool were also undefined during that attempt even though everything was still define in my xml. Therefore I extrapolated that once Cayenne had TRUE for shouldConfigureDataSourceFromProperties, it ignored anything within the <data-source> element.
You are correct, it would take more debugging to see if there is a different piece of logic causing things other than url and driver to be ignored.
-----Original Message-----
From: John Huss <jo...@gmail.com>
Sent: Wednesday, July 18, 2018 2:33 PM
To: user@cayenne.apache.org
Subject: Re: Redacting db user name and password from XML
On Wed, Jul 18, 2018 at 1:26 PM Meeks, Andrew <an...@vt.edu> wrote:
> I feel as though a recent bit of code is closely related to the topic in
> this old thread. The Cayenne <data-source> xml element supports a
> password with a path external to the application. The observed
> environment properties appear to only include JDBC_PASSWORD_PROPERTY
> which seems to only support a literal password. For Spring
> developers, an integration suggestion is included below.
>
> I would like to say, in agreement with the earlier thread, that the
> documentation at:
> https://cayenne.apache.org/docs/4.1/cayenne-guide/#appendix-a-configur
> ation-properties is unclear in one respect. I think it needs to state
> that all
> cayenne.jdbc.* properties must be defined in order for environment
> properties to override (or correctly override) the definition of
> data-source in the xml file. That would have saved me a lot of
> stepping into code before I found what was happening in DelegatingDataSourceFactory.
> shouldConfigureDataSourceFromProperties(DataNodeDescriptor
> dataNodeDescriptor);
>
I believe only the URL and driver have to be specified via properties in order for them to override the values in the cayenne project file. This is still not really intuitive, but it's better than all of them being required.
>
> If you have a Spring Project and would like to control your
> <data-source> through a more spring-like configuration, here are
> relevant snippets from files of how I set it up based on examples in the cayenne documentation:
>
> -- file: application.properties --
> cayenne.jdbc.driver=oracle.jdbc.driver.OracleDriver
> cayenne.jdbc.max_connections=20
> cayenne.jdbc.min_connections=1
> cayenne.jdbc.username=SCOTT
>
> --file: application-dev.properties--
>
> cayenne.jdbc.passwordSource=C:\\home\\apps-config\\testProject\\cayenn
> e_pw.txt cayenne.jdbc.url=jdbc:oracle:thin:@//localhost:1521/xe
>
> --file: application-prod.properties--
>
> cayenne.jdbc.passwordSource=C:\\home\\apps-config\\testProject\\cayenn
> e_pw_prod.txt
> cayenne.jdbc.url=jdbc:oracle:thin:@//prod.db.example.com:1521/prod_svc
>
> --file: SpringMvcConfig.java--
> @Configuration
> public class MvcConfig extends WebMvcConfigurerAdapter {
> @Value("${cayenne.jdbc.url}")
> String cayenneClientUrl;
>
> @Value("${cayenne.jdbc.driver}")
> String cayenneDriverProperty;
>
> @Value("${cayenne.jdbc.passwordSource}")
> String cayennePasswordSource;
>
> @Value("${cayenne.jdbc.username}")
> String cayenneUsername;
>
> @Value("${cayenne.jdbc.min_connections}")
> String cayenneMinConnections;
>
> @Value("${cayenne.jdbc.max_connections}")
> String cayenneMaxConnections;
>
> @Bean(name="serverRuntime")
> public ServerRuntime serverRuntime(){
>
> String url = cayenneClientUrl;
> String driver = cayenneDriverProperty;
> String username = cayenneUsername;
> String pwSource = cayennePasswordSource;
> String maxConnections = cayenneMaxConnections;
> String minConnections = cayenneMinConnections;
>
> String actualPassword = getPassword(pwSource);
>
> Module cayenneModule = binder ->
>
> ServerModule.contributeProperties(binder).put(Constants.JDBC_DRIVER_PR
> OPERTY, driver).put(Constants.JDBC_URL_PROPERTY, url)
> .put(Constants.JDBC_USERNAME_PROPERTY,
> username).put(Constants.JDBC_PASSWORD_PROPERTY, actualPassword)
>
> .put(Constants.JDBC_MIN_CONNECTIONS_PROPERTY,
> minConnections).put(Constants.JDBC_MAX_CONNECTIONS_PROPERTY,
> maxConnections);
>
> ServerRuntimeBuilder builder = ServerRuntime.builder();
> builder.addConfig("cayenne-testProject.xml");
> builder.addModule(cayenneModule);
> ServerRuntime createdRuntime = builder.build();
>
> try {
> // this will tell us on startup if it cannot
> connect to the database
> createdRuntime.getDataSource().getConnection();
> } catch (SQLException e) {
> e.printStackTrace();
> }
>
> return createdRuntime;
> }
>
> private String getPassword(String passwordSourcePath) {
> try {
> return new
> String(Files.readAllBytes(Paths.get(passwordSourcePath)));
> } catch (IOException e) {
> return null;
> }
> }
> }
>
> --End of code snippets--
> The primary advantage with this is that server admins can play around
> with the database parameters and developers will not need to build
> with a new cayenne xml, while still leaving the database password in
> an external file. Once each Spring profile has its default values,
> then the server admins need only specify a parameter rather than
> specifying all parameters. We have somewhat regular datasource url changes for production
> which precipitated this refactor. The code creating the ServerRuntime
> bean is intentionally verbose here to make it clearer in debugging
> what values are getting passed through. Obviously
> application.properties would contain variables that are known to be
> the same in different profiles, your situation may vary and some may
> need to go in to application-PROFILE.properties or vice-versa.
>
> I hope that is useful to someone,
> Andrew
>
> -----Original Message-----
> From: Pascal Robert <pr...@druide.com>
> Sent: Friday, January 19, 2018 10:01 AM
> To: user@cayenne.apache.org
> Subject: Re: Redacting db user name and password from XML
>
> I confirm this behaviour, and I switched back to
> XMLPoolingDataSourceFactory.
>
> > Le 18 janv. 2018 à 03:59, Andrus Adamchik <an...@objectstyle.org> a
> écrit :
> >
> > Ah, mystery solved. Looking at the code, more specifically to use
> properties for a given DataSource Cayenne would expect you to specify
> at least DB URL and DB driver. Username/password are optional.
> >
> > Andrus
> >
> >
> >> On Jan 18, 2018, at 11:46 AM, Nikita Timofeev <
> ntimofeev@objectstyle.com> wrote:
> >>
> >> Hi all,
> >>
> >> Made some research, and here is what I've found.
> >>
> >> Cayenne do switch to System properties (defined via -D or with DI
> >> binding) automatically but it do so only when all properties are
> >> defined.
> >>
> >> So this code will work as expected, and use overridden DataSource
> properties:
> >>
> >> ServerRuntime cayenneRuntime =
> >> ServerRuntime.builder().addConfig("cayenne-project.xml")
> >> .addModule(binder -> ServerModule.contributeProperties(binder)
> >> .put(Constants.JDBC_DRIVER_PROPERTY,
> "com.mysql.jdbc.Driver")
> >> .put(Constants.JDBC_URL_PROPERTY,
> >> "jdbc:mysql://localhost:3306/test")
> >> .put(Constants.JDBC_USERNAME_PROPERTY, "user")
> >> .put(Constants.JDBC_PASSWORD_PROPERTY, "password"))
> >> .build();
> >>
> >> While this will ignore password and use DataSource properties from XML:
> >>
> >> ServerRuntime cayenneRuntime =
> >> ServerRuntime.builder().addConfig("cayenne-project.xml")
> >> .addModule(binder -> ServerModule.contributeProperties(binder)
> >> .put(Constants.JDBC_PASSWORD_PROPERTY, "password"))
> >> .build();
> >>
> >> I will add some information to logs, so at least it wouldn't be
> >> surprising as it is now.
> >> But maybe we should change this to enable override of separate
> properties.
> >>
> >> On Thu, Jan 18, 2018 at 8:27 AM, Andrus Adamchik <
> andrus@objectstyle.org> wrote:
> >>>
> >>>
> >>>> On Jan 17, 2018, at 11:05 PM, Pascal Robert <pr...@druide.com>
> wrote:
> >>>>
> >>>> Ahhh… If I change the factory in the node definition to
> org.apache.cayenne.configuration.server.PropertyDataSourceFactory, it
> does read the command-line properties.
> >>>
> >>> That should sorta happen automatically. We are still looking why
> >>> it
> doesn't.
> >>>
> >>> Andrus
> >>>
> >>
> >>
> >>
> >> --
> >> Best regards,
> >> Nikita Timofeev
> >
>
>
Re: Redacting db user name and password from XML
Posted by John Huss <jo...@gmail.com>.
On Wed, Jul 18, 2018 at 1:26 PM Meeks, Andrew <an...@vt.edu> wrote:
> I feel as though a recent bit of code is closely related to the topic in
> this old thread. The Cayenne <data-source> xml element supports a
> password with a path external to the application. The observed environment
> properties appear to only include JDBC_PASSWORD_PROPERTY which seems to
> only support a literal password. For Spring developers, an integration
> suggestion is included below.
>
> I would like to say, in agreement with the earlier thread, that the
> documentation at:
> https://cayenne.apache.org/docs/4.1/cayenne-guide/#appendix-a-configuration-properties
> is unclear in one respect. I think it needs to state that all
> cayenne.jdbc.* properties must be defined in order for environment
> properties to override (or correctly override) the definition of
> data-source in the xml file. That would have saved me a lot of stepping
> into code before I found what was happening in DelegatingDataSourceFactory.
> shouldConfigureDataSourceFromProperties(DataNodeDescriptor
> dataNodeDescriptor);
>
I believe only the URL and driver have to be specified via properties in
order for them to override the values in the cayenne project file. This is
still not really intuitive, but it's better than all of them being required.
>
> If you have a Spring Project and would like to control your <data-source>
> through a more spring-like configuration, here are relevant snippets from
> files of how I set it up based on examples in the cayenne documentation:
>
> -- file: application.properties --
> cayenne.jdbc.driver=oracle.jdbc.driver.OracleDriver
> cayenne.jdbc.max_connections=20
> cayenne.jdbc.min_connections=1
> cayenne.jdbc.username=SCOTT
>
> --file: application-dev.properties--
>
> cayenne.jdbc.passwordSource=C:\\home\\apps-config\\testProject\\cayenne_pw.txt
> cayenne.jdbc.url=jdbc:oracle:thin:@//localhost:1521/xe
>
> --file: application-prod.properties--
>
> cayenne.jdbc.passwordSource=C:\\home\\apps-config\\testProject\\cayenne_pw_prod.txt
> cayenne.jdbc.url=jdbc:oracle:thin:@//prod.db.example.com:1521/prod_svc
>
> --file: SpringMvcConfig.java--
> @Configuration
> public class MvcConfig extends WebMvcConfigurerAdapter {
> @Value("${cayenne.jdbc.url}")
> String cayenneClientUrl;
>
> @Value("${cayenne.jdbc.driver}")
> String cayenneDriverProperty;
>
> @Value("${cayenne.jdbc.passwordSource}")
> String cayennePasswordSource;
>
> @Value("${cayenne.jdbc.username}")
> String cayenneUsername;
>
> @Value("${cayenne.jdbc.min_connections}")
> String cayenneMinConnections;
>
> @Value("${cayenne.jdbc.max_connections}")
> String cayenneMaxConnections;
>
> @Bean(name="serverRuntime")
> public ServerRuntime serverRuntime(){
>
> String url = cayenneClientUrl;
> String driver = cayenneDriverProperty;
> String username = cayenneUsername;
> String pwSource = cayennePasswordSource;
> String maxConnections = cayenneMaxConnections;
> String minConnections = cayenneMinConnections;
>
> String actualPassword = getPassword(pwSource);
>
> Module cayenneModule = binder ->
>
> ServerModule.contributeProperties(binder).put(Constants.JDBC_DRIVER_PROPERTY,
> driver).put(Constants.JDBC_URL_PROPERTY, url)
> .put(Constants.JDBC_USERNAME_PROPERTY,
> username).put(Constants.JDBC_PASSWORD_PROPERTY, actualPassword)
> .put(Constants.JDBC_MIN_CONNECTIONS_PROPERTY,
> minConnections).put(Constants.JDBC_MAX_CONNECTIONS_PROPERTY,
> maxConnections);
>
> ServerRuntimeBuilder builder = ServerRuntime.builder();
> builder.addConfig("cayenne-testProject.xml");
> builder.addModule(cayenneModule);
> ServerRuntime createdRuntime = builder.build();
>
> try {
> // this will tell us on startup if it cannot
> connect to the database
> createdRuntime.getDataSource().getConnection();
> } catch (SQLException e) {
> e.printStackTrace();
> }
>
> return createdRuntime;
> }
>
> private String getPassword(String passwordSourcePath) {
> try {
> return new
> String(Files.readAllBytes(Paths.get(passwordSourcePath)));
> } catch (IOException e) {
> return null;
> }
> }
> }
>
> --End of code snippets--
> The primary advantage with this is that server admins can play around with
> the database parameters and developers will not need to build with a new
> cayenne xml, while still leaving the database password in an external
> file. Once each Spring profile has its default values, then the server
> admins need only specify a parameter rather than specifying all
> parameters. We have somewhat regular datasource url changes for production
> which precipitated this refactor. The code creating the ServerRuntime
> bean is intentionally verbose here to make it clearer in debugging what
> values are getting passed through. Obviously application.properties would
> contain variables that are known to be the same in different profiles, your
> situation may vary and some may need to go in to
> application-PROFILE.properties or vice-versa.
>
> I hope that is useful to someone,
> Andrew
>
> -----Original Message-----
> From: Pascal Robert <pr...@druide.com>
> Sent: Friday, January 19, 2018 10:01 AM
> To: user@cayenne.apache.org
> Subject: Re: Redacting db user name and password from XML
>
> I confirm this behaviour, and I switched back to
> XMLPoolingDataSourceFactory.
>
> > Le 18 janv. 2018 à 03:59, Andrus Adamchik <an...@objectstyle.org> a
> écrit :
> >
> > Ah, mystery solved. Looking at the code, more specifically to use
> properties for a given DataSource Cayenne would expect you to specify at
> least DB URL and DB driver. Username/password are optional.
> >
> > Andrus
> >
> >
> >> On Jan 18, 2018, at 11:46 AM, Nikita Timofeev <
> ntimofeev@objectstyle.com> wrote:
> >>
> >> Hi all,
> >>
> >> Made some research, and here is what I've found.
> >>
> >> Cayenne do switch to System properties (defined via -D or with DI
> >> binding) automatically but it do so only when all properties are
> >> defined.
> >>
> >> So this code will work as expected, and use overridden DataSource
> properties:
> >>
> >> ServerRuntime cayenneRuntime =
> >> ServerRuntime.builder().addConfig("cayenne-project.xml")
> >> .addModule(binder -> ServerModule.contributeProperties(binder)
> >> .put(Constants.JDBC_DRIVER_PROPERTY,
> "com.mysql.jdbc.Driver")
> >> .put(Constants.JDBC_URL_PROPERTY,
> >> "jdbc:mysql://localhost:3306/test")
> >> .put(Constants.JDBC_USERNAME_PROPERTY, "user")
> >> .put(Constants.JDBC_PASSWORD_PROPERTY, "password"))
> >> .build();
> >>
> >> While this will ignore password and use DataSource properties from XML:
> >>
> >> ServerRuntime cayenneRuntime =
> >> ServerRuntime.builder().addConfig("cayenne-project.xml")
> >> .addModule(binder -> ServerModule.contributeProperties(binder)
> >> .put(Constants.JDBC_PASSWORD_PROPERTY, "password"))
> >> .build();
> >>
> >> I will add some information to logs, so at least it wouldn't be
> >> surprising as it is now.
> >> But maybe we should change this to enable override of separate
> properties.
> >>
> >> On Thu, Jan 18, 2018 at 8:27 AM, Andrus Adamchik <
> andrus@objectstyle.org> wrote:
> >>>
> >>>
> >>>> On Jan 17, 2018, at 11:05 PM, Pascal Robert <pr...@druide.com>
> wrote:
> >>>>
> >>>> Ahhh… If I change the factory in the node definition to
> org.apache.cayenne.configuration.server.PropertyDataSourceFactory, it does
> read the command-line properties.
> >>>
> >>> That should sorta happen automatically. We are still looking why it
> doesn't.
> >>>
> >>> Andrus
> >>>
> >>
> >>
> >>
> >> --
> >> Best regards,
> >> Nikita Timofeev
> >
>
>