Mixing
Postgres table with encrypted fields and the psql copy command. ERROR: Wrong key or corrupt data SQL...
Problem after postgres psql copy to csv and psql copy back field is no longer encrypted. After loading back it says ERROR: Wrong key or corrupt data SQL state:39000
I have a PostreSQL table with a encrypted field in it (using pgcrypto extension). The field is defined with bytea. I used the command:
psql -d dbname -U username -w -c "copy (select * from sch.table) to 'C:/directory/subdirectory/file.csv' '|' CSV HEADER;"
command to put it in a csv file.
Then I tried to put it in another table defined the exact way (same create definitions with bytea on same fields), because I am creating a new table with this data and more to be added.
Then I used:
psql -d dbname -U username -w -c "copy sch.newtable FROM 'C:/directory/subdirectory/file.csv' DELIMITER '|' CSV HEADER;" to reload it.
I assumed the bytea column would be dumped and loaded back the same, but it is not. After loading back it says ERROR: Wrong key or corrupt data SQL state:39000
Do I need some extra options on the psql commands to take this into account?
postgresql csv copy psql
asked Nov 21 '18 at 16:37
klmbearklmbear
213
add a comment |
Problem after postgres psql copy to csv and psql copy back field is no longer encrypted. After loading back it says ERROR: Wrong key or corrupt data SQL state:39000
I have a PostreSQL table with a encrypted field in it (using pgcrypto extension). The field is defined with bytea. I used the command:
psql -d dbname -U username -w -c "copy (select * from sch.table) to 'C:/directory/subdirectory/file.csv' '|' CSV HEADER;"
command to put it in a csv file.
Then I tried to put it in another table defined the exact way (same create definitions with bytea on same fields), because I am creating a new table with this data and more to be added.
Then I used:
psql -d dbname -U username -w -c "copy sch.newtable FROM 'C:/directory/subdirectory/file.csv' DELIMITER '|' CSV HEADER;" to reload it.
I assumed the bytea column would be dumped and loaded back the same, but it is not. After loading back it says ERROR: Wrong key or corrupt data SQL state:39000
Do I need some extra options on the psql commands to take this into account?
postgresql csv copy psql
asked Nov 21 '18 at 16:37
klmbearklmbear
213
Most of all, you need to tell up theCREATE TABLEstatements and the data in there. In other words, we need a reproducible test case.
– Laurenz Albe
Nov 21 '18 at 18:44
add a comment |
Problem after postgres psql copy to csv and psql copy back field is no longer encrypted. After loading back it says ERROR: Wrong key or corrupt data SQL state:39000
I have a PostreSQL table with a encrypted field in it (using pgcrypto extension). The field is defined with bytea. I used the command:
psql -d dbname -U username -w -c "copy (select * from sch.table) to 'C:/directory/subdirectory/file.csv' '|' CSV HEADER;"
command to put it in a csv file.
Then I tried to put it in another table defined the exact way (same create definitions with bytea on same fields), because I am creating a new table with this data and more to be added.
Then I used:
psql -d dbname -U username -w -c "copy sch.newtable FROM 'C:/directory/subdirectory/file.csv' DELIMITER '|' CSV HEADER;" to reload it.
I assumed the bytea column would be dumped and loaded back the same, but it is not. After loading back it says ERROR: Wrong key or corrupt data SQL state:39000
Do I need some extra options on the psql commands to take this into account?
postgresql csv copy psql
asked Nov 21 '18 at 16:37
klmbearklmbear
213
Problem after postgres psql copy to csv and psql copy back field is no longer encrypted. After loading back it says ERROR: Wrong key or corrupt data SQL state:39000
I have a PostreSQL table with a encrypted field in it (using pgcrypto extension). The field is defined with bytea. I used the command:
psql -d dbname -U username -w -c "copy (select * from sch.table) to 'C:/directory/subdirectory/file.csv' '|' CSV HEADER;"
command to put it in a csv file.
Then I tried to put it in another table defined the exact way (same create definitions with bytea on same fields), because I am creating a new table with this data and more to be added.
Then I used:
psql -d dbname -U username -w -c "copy sch.newtable FROM 'C:/directory/subdirectory/file.csv' DELIMITER '|' CSV HEADER;" to reload it.
I assumed the bytea column would be dumped and loaded back the same, but it is not. After loading back it says ERROR: Wrong key or corrupt data SQL state:39000
Do I need some extra options on the psql commands to take this into account?
postgresql csv copy psql
postgresql csv copy psql
asked Nov 21 '18 at 16:37
klmbearklmbear
213
asked Nov 21 '18 at 16:37
klmbearklmbear
213
asked Nov 21 '18 at 16:37
klmbearklmbear
213
asked Nov 21 '18 at 16:37
klmbearklmbear
213
asked Nov 21 '18 at 16:37
klmbearklmbear
213
213
Most of all, you need to tell up theCREATE TABLEstatements and the data in there. In other words, we need a reproducible test case.
– Laurenz Albe
Nov 21 '18 at 18:44
add a comment |
Most of all, you need to tell up theCREATE TABLEstatements and the data in there. In other words, we need a reproducible test case.
– Laurenz Albe
Nov 21 '18 at 18:44
Most of all, you need to tell up the
CREATE TABLE statements and the data in there. In other words, we need a reproducible test case.– Laurenz Albe
Nov 21 '18 at 18:44
Most of all, you need to tell up the
CREATE TABLE statements and the data in there. In other words, we need a reproducible test case.– Laurenz Albe
Nov 21 '18 at 18:44
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53416710%2fpostgres-table-with-encrypted-fields-and-the-psql-copy-command-error-wrong-key%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53416710%2fpostgres-table-with-encrypted-fields-and-the-psql-copy-command-error-wrong-key%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Most of all, you need to tell up the
CREATE TABLEstatements and the data in there. In other words, we need a reproducible test case.– Laurenz Albe
Nov 21 '18 at 18:44