[sqlalchemy] is it possible to implement this anti-pattern in SQLAlchemy?

[Jonathan Vanasco]

I have two classes where one f-keys onto another.

Things work perfectly:

 class Foo(Base):
id = Column(Integer, primary_key=True)
bar_id = Column(Integer, ForeignKey("bar.id"), nullable=True)
bar = relationship(
"bar", 
primaryjoin="Foo.bar_id==Bar.id",
uselist=False,
back_populates="foo",
)

 class Bar(Base):
id = Column(Integer, primary_key=True)
foo = relationship(
"Foo", 
primaryjoin="Bar.id==Foo.bar_id",
uselist=False,
back_populates="bar",
)
Thanks to SQLAlchemy, I can do this:

myFoo.bar = myBar
As expected `myFoo.bar_id` is updated.  Wonderful.

I am working on some new functionality, and hit a potential performance 
issue.  While my `Foo` objects inherently know if there is an associated 
`Bar`, I have to query (direct or via lazy-load) the database to find out 
if a `Bar` has an associate `Foo`.  In most situations, this is not an 
issue. In a few contexts, the lazyloading or joins are a bit burdonsome.

Is there a way to set up the `relationship` so I could do cache the 
`foo_id` on Bar? Something like this:

 class Foo(Base):
id = Column(Integer, primary_key=True)
bar_id = Column(Integer, ForeignKey("bar.id"), nullable=True)
bar = relationship("Bar", ???)
 
  class Bar(Base):
id = Column(Integer, primary_key=True)
foo_id = Column(Integer, ForeignKey("foo.id"), nullable=True)
foo = relationship("Foo", ???)

This is obviously an anti-pattern in database design, as I only need to 
duplicate this data to improve performance in a few places.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/171b01dc-787e-43b6-bd8c-afdceb211543%40googlegroups.com.

Re: [sqlalchemy] hiding/encrypting the Oracle database connection information?

[Jonathan Vanasco]

There are two related concerns on this concept:

* protecting your credentials in source code
* protecting your credentials on the server

For the first concern, I like to use encryption management tools like 
Blackbox (https://github.com/StackExchange/blackbox)

With an encryption management system, you "enroll" certain files to be 
managed by the system.  Instead of saving the plaintext files to version 
control, the encrypted files are saved.  Approved users (via GPG keys in 
blackbox) are able to decrypt or edit (decrypt+edit+encrypt) the files.  
**When a project is deployed to a server, the files are decrypted and the 
plaintext version is left on the server**

For the second concern, I've never seen a foolproof way to safeguard the 
plaintext "secrets".  You can force stuff into environment variables, but 
hackers can still get to those.  You can trash files after starting an 
application... but then you can't restart the application unless you have 
an external service that logs into the machine and 
decrypts/reloads/deletes.  

I would focus on safeguarding your secrets from versioncontrol, and 
constructing them in ways that are prepared for leaks (for example, 
rotating credentials periodically, using ip whitelisting to limit where 
they can be used, using ACLs on the various services to limit what each 
credential can do)

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/5c0f526f-299b-44bc-827e-1e0b41813586%40googlegroups.com.

Re: [sqlalchemy] hiding/encrypting the Oracle database connection information?

[Mike Bayer]

that issue is unfortunately one of the great mythological stories of business 
application development, how to configure an application such that the database 
credentials are not present in a config file where they can be viewed. 

the scope of that issue is way outside of SQLAlchemy and personally I don't 
think there is really any feasible solution to that problem - not that the 
credentials can't be encrypted, but it implies that there's a decryption key 
right nearby, which renders the whole situation basically security theater. 
Even if you have some super sophisticated remote-server kind of approach, if an 
attacker is on the machine where the software is, the Python code is right 
there; they can run whatever routines your Python code uses to get these 
credentials into memory and then they have them.

However, anyone that's worked in app dev for more than 5 years has had to deal 
with managerial teams that are trying to make it happen and perhaps theater is 
all you need. There's a good stackoverflow rundown of the full issue I found at 
https://security.stackexchange.com/a/22858 .


On Fri, May 15, 2020, at 12:30 PM, Terrence-Monroe: Brannon wrote:
> Hello, what is the recommended way to encrypt/hide the connection information 
> that SA will use to connect to an Oracle database?
> 
> Related gitter discussion - 
> https://gitter.im/sqlalchemy/community?at=5ebec23f20d9bf305768a247
> 
> 
> 

> --
>  SQLAlchemy - 
>  The Python SQL Toolkit and Object Relational Mapper
> 
> http://www.sqlalchemy.org/
> 
>  To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
>  --- 
>  You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
>  To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
>  To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/95561686-5404-423a-9453-fd0625111423%40googlegroups.com
>  
> .

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/0775f8c2-4dde-45f9-b7af-a9b5b24f9bb1%40www.fastmail.com.

Re: [sqlalchemy] test_types.py

[Mike Bayer]

test/sql/test_types.py is part of SQLAlchemy's internal testing for the "types" 
system.

The testing/suite/test_types.py suite is part of SQLAlchemy's exported third 
party dialect test system which is described at 
https://github.com/sqlalchemy/sqlalchemy/blob/master/README.dialects.rst .

if you are authoring a new dialect you'd be concerned about the tests in the 
latter file (testing/suite/test_types.py).



On Fri, May 15, 2020, at 10:24 AM, Yeongseon Choe wrote:
> Hi,
> 
> When I took over a sqlalchemy, I'm little bit confused about test_types.py
> It looks likes there are 2 test_types.py in the repository.
> 
> - sqlalchemy/test/sql/test_types.py /
> - sqlalchemy/lib/sqlalchemy/testing/suite/test_types.py
> 
> Could you please explain the difference between these files.
> 
> Thank you in advance.
> 
> Best regards,
> Yeongseon
> 

> --
>  SQLAlchemy - 
>  The Python SQL Toolkit and Object Relational Mapper
> 
> http://www.sqlalchemy.org/
> 
>  To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
>  --- 
>  You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
>  To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
>  To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/2e6774de-638f-4b64-83a1-2abe76edb298%40googlegroups.com
>  
> .

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/28a587e9-c5e4-4bdd-ac78-de65a8291084%40www.fastmail.com.

[sqlalchemy] hiding/encrypting the Oracle database connection information?

[Terrence-Monroe: Brannon]

Hello, what is the recommended way to encrypt/hide the connection 
information that SA will use to connect to an Oracle database?

Related gitter discussion - 
https://gitter.im/sqlalchemy/community?at=5ebec23f20d9bf305768a247


-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/95561686-5404-423a-9453-fd0625111423%40googlegroups.com.

[sqlalchemy] test_types.py

[Yeongseon Choe]

Hi,

When I took over a sqlalchemy, I'm little bit confused about test_types.py
It looks likes there are 2 test_types.py in the repository.

- sqlalchemy/test/sql/test_types.py /
- sqlalchemy/lib/sqlalchemy/testing/suite/test_types.py

Could you please explain the difference between these files.

Thank you in advance.

Best regards,
Yeongseon

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/2e6774de-638f-4b64-83a1-2abe76edb298%40googlegroups.com.