Re: Regd. the Implementation of Wallet (in Oracle) config equivalent in postgreSQL whilst the database migration
> Am 22.12.2022 um 00:57 schrieb Benedict Holland > : > > Like, does oracle give you something more? Probably. It's also a ton of money > and I mean a geuine ton. At that point, you also need security audits, > security protocols, requirements, backup and retention policies, and > redundancy key locations. If someone has root, I don't know how they also > don't have your encryption keys. They are not on the same box. They are in a HSM. A dedicated piece of tamper-proof hardware that stores secrets (keys). The Oracle-server needs to talk to the HSM to get the keys. This is not a low-budget setup (well, it’s Oracle…) - rather, it’s for when the data is really very valuable so that the cost for redundant HSMs, Oracle, Data Guard etc.pp. is still lower than the value of the data. OP works for an outfit that typically does outsourcing for these kinds of clients. It’s all about having more degrees of separation between different functions, so you don’t have to trust the single, Dennis-Nedry-type of admin to not sell the information in the database to the highest bidder.
Re: Regd. the Implementation of Wallet (in Oracle) config equivalent in postgreSQL whilst the database migration
On 2022-12-22 09:17:18 +0100, Rainer Duffner wrote: > Am 22.12.2022 um 00:57 schrieb Benedict Holland < > benedict.m.holl...@gmail.com>: > > If someone has root, I don't know how they also don't have your > encryption keys. > > > They are not on the same box. They are in a HSM. A dedicated piece of > tamper-proof hardware that stores secrets (keys). > The Oracle-server needs to talk to the HSM to get the keys. If the hacker has root access: What prevents them from talking to the HSM? hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Regd. the Implementation of Wallet (in Oracle) config equivalent in postgreSQL whilst the database migration
> Am 22.12.2022 um 10:46 schrieb Peter J. Holzer : > > If the hacker has root access: What prevents them from talking to the > HSM? I wasn’t involved in setting it up here, but AFAIK you need to „enroll“ the client to the HSM. That is a one-time process that requires HSM credentials (via certificates and pass-phrases). Then, that client can talk to the HSM. The HSM-client is (or should be) engineered in such a way that you can’t extract the encryption-secret easily. I am not sure, but IIRC, you should not even be able to clone the VM without the HSM noticing or the clone not working at all to begin with (for lack of enrollment). Though most production databases are too large to just „clone“. Maybe someone who knows more about this subject can chime in before I make a fool of myself? ;-) Rainer
trouble writing plpgsql
Hi, I want to make a function to parsetext and return key-value pairs create or replace function extractinfo (text) returns table (key char[1], val text) language plpgsql as $$ I first tried declare xx record; begin xx.key = ; xx.val = ; return next xx: This is not possible because xx needs to know its structure before the fields can be assiged to. Could I declare xx as having these fields in the first place, do Ineedto create a type for key, val first? Wolfgang Hamann
Re: trouble writing plpgsql
On Thu, Dec 22, 2022 at 11:37:22AM -, haman...@t-online.de wrote: > I want to make a function to parsetext and return key-value pairs > create or replace function extractinfo (text) returns table (key char[1], val > text) Please don't use char datatype: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_char.28n.29 > language plpgsql as $$ > > I first tried > declare > xx record; > begin > > xx.key = ; xx.val = ; > return next xx: > This is not possible because xx needs to know its structure before the fields > can be assiged to. > Could I declare xx as having these fields in the first place, do Ineedto > create a type for key, val > first? select '...' as key, '...' as val into record; depesz
Re: Implementing foreign data wrappers and avoiding n+1 querying
I apologize that my post was not super clear, I am thinking about implementing a fdw from scratch, and the target database is one of those NoSQL databases where you have to send JSON over a HTTP connection for each query. I have reviewed the postgres fdw code to see how it works and to see what's possible. Although it probably wouldn't benefit as much from this sort of thing (yay to postgres' design!) It could possibly still benefit a bit, which makes me wonder if it can't be done with the current planner nodes it might be a worthy improvement to add support for this. On Wed, Dec 21, 2022, 10:57 PM David Rowley wrote: > On Thu, 22 Dec 2022 at 13:31, David Gilman wrote: > > > > When a fdw table participates in query planning and finds itself as > > part of a join it can output a parameterized path. If chosen, Postgres > > will dutifully call the fdw over and over via IterateForeignScan to > > fetch matching tuples. Many fdw extensions do network traffic, though, > > and it would be beneficial to reduce the total number of queries done > > or network connections established. > > Sounds like you might be looking for fdw_startup_cost [1]. > > David > > [1] https://www.postgresql.org/docs/current/postgres-fdw.html >
Re: Regd. the Implementation of Wallet (in Oracle) config equivalent in postgreSQL whilst the database migration
On 2022-12-22 11:15:57 +0100, Rainer Duffner wrote: > > > Am 22.12.2022 um 10:46 schrieb Peter J. Holzer : > > If the hacker has root access: What prevents them from talking to the > HSM? > > > > I wasn’t involved in setting it up here, but AFAIK you need to „enroll“ the > client to the HSM. > > That is a one-time process that requires HSM credentials (via certificates and > pass-phrases). > > Then, that client can talk to the HSM. Which means that some sort of access-token is stored on the client. So what prevents a hacker from using that access token? > The HSM-client is (or should be) engineered in such a way that you can’t > extract the encryption-secret easily. Security by obscurity? Just hope that nobody figures out how that access token is stored? That doesn't seem like a good strategy against high-level threats. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
