Mixing
how to encrypt integer and date time data type in postgresql using pgcrypto
Here is my code to encrypt the column:
UPDATE users
SET (userid, modifieddate) =
(
PGP_SYM_ENCRYPT('0', 'AES_KEY'),
PGP_SYM_ENCRYPT('2018-06-19 08:40:23', 'AES_KEY')
)
WHERE id='3';
but its throwing error:
column "userid" is of type integer but expression is of type byte
sql postgresql
edited Jan 1 at 6:22
Vishal Suthar
14.3k23988
asked Jan 1 at 6:20
adamsadams
667
add a comment |
Here is my code to encrypt the column:
UPDATE users
SET (userid, modifieddate) =
(
PGP_SYM_ENCRYPT('0', 'AES_KEY'),
PGP_SYM_ENCRYPT('2018-06-19 08:40:23', 'AES_KEY')
)
WHERE id='3';
but its throwing error:
column "userid" is of type integer but expression is of type byte
sql postgresql
edited Jan 1 at 6:22
Vishal Suthar
14.3k23988
asked Jan 1 at 6:20
adamsadams
667
add a comment |
Here is my code to encrypt the column:
UPDATE users
SET (userid, modifieddate) =
(
PGP_SYM_ENCRYPT('0', 'AES_KEY'),
PGP_SYM_ENCRYPT('2018-06-19 08:40:23', 'AES_KEY')
)
WHERE id='3';
but its throwing error:
column "userid" is of type integer but expression is of type byte
sql postgresql
edited Jan 1 at 6:22
Vishal Suthar
14.3k23988
asked Jan 1 at 6:20
adamsadams
667
Here is my code to encrypt the column:
UPDATE users
SET (userid, modifieddate) =
(
PGP_SYM_ENCRYPT('0', 'AES_KEY'),
PGP_SYM_ENCRYPT('2018-06-19 08:40:23', 'AES_KEY')
)
WHERE id='3';
but its throwing error:
column "userid" is of type integer but expression is of type byte
sql postgresql
sql postgresql
edited Jan 1 at 6:22
Vishal Suthar
14.3k23988
asked Jan 1 at 6:20
adamsadams
667
edited Jan 1 at 6:22
Vishal Suthar
14.3k23988
asked Jan 1 at 6:20
adamsadams
667
edited Jan 1 at 6:22
Vishal Suthar
14.3k23988
edited Jan 1 at 6:22
Vishal Suthar
14.3k23988
edited Jan 1 at 6:22
Vishal Suthar
14.3k23988
14.3k23988
asked Jan 1 at 6:20
adamsadams
667
asked Jan 1 at 6:20
adamsadams
667
asked Jan 1 at 6:20
adamsadams
667
667
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Your table columns users.userid and users.modifieddate should be type BYTEA, because pgcrypto module generates encrypted result with BYTEA type.
answered Jan 1 at 6:39
C.C. HsuC.C. Hsu
511
i cannot alter my table data types
– adams
Jan 1 at 6:41
add a comment |
You cannot encrypt only one user's id and put rest of it as it is , if you want to achieve that you need to alter table as mention by @C.C. Hsu , i.e. to converting data type of userid , modifieddate into bytea or text which is capable of storing both encrypted data and your normal data.
alter table users alter column userid type bytea using PGP_SYM_ENCRYPT(userid::text, 'AES_KEY');
alter table users alter column modifieddate type bytea using PGP_SYM_ENCRYPT(modifieddate::text, 'AES_KEY');
answered Jan 1 at 9:28
SABER - FICTIONAL CHARACTERSABER - FICTIONAL CHARACTER
232212
add a comment |
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%2f53993430%2fhow-to-encrypt-integer-and-date-time-data-type-in-postgresql-using-pgcrypto%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Your table columns users.userid and users.modifieddate should be type BYTEA, because pgcrypto module generates encrypted result with BYTEA type.
answered Jan 1 at 6:39
C.C. HsuC.C. Hsu
511
i cannot alter my table data types
– adams
Jan 1 at 6:41
add a comment |
Your table columns users.userid and users.modifieddate should be type BYTEA, because pgcrypto module generates encrypted result with BYTEA type.
answered Jan 1 at 6:39
C.C. HsuC.C. Hsu
511
i cannot alter my table data types
– adams
Jan 1 at 6:41
add a comment |
Your table columns users.userid and users.modifieddate should be type BYTEA, because pgcrypto module generates encrypted result with BYTEA type.
answered Jan 1 at 6:39
C.C. HsuC.C. Hsu
511
Your table columns users.userid and users.modifieddate should be type BYTEA, because pgcrypto module generates encrypted result with BYTEA type.
answered Jan 1 at 6:39
C.C. HsuC.C. Hsu
511
answered Jan 1 at 6:39
C.C. HsuC.C. Hsu
511
answered Jan 1 at 6:39
C.C. HsuC.C. Hsu
511
answered Jan 1 at 6:39
C.C. HsuC.C. Hsu
511
511
i cannot alter my table data types
– adams
Jan 1 at 6:41
add a comment |
i cannot alter my table data types
– adams
Jan 1 at 6:41
i cannot alter my table data types
– adams
Jan 1 at 6:41
i cannot alter my table data types
– adams
Jan 1 at 6:41
add a comment |
You cannot encrypt only one user's id and put rest of it as it is , if you want to achieve that you need to alter table as mention by @C.C. Hsu , i.e. to converting data type of userid , modifieddate into bytea or text which is capable of storing both encrypted data and your normal data.
alter table users alter column userid type bytea using PGP_SYM_ENCRYPT(userid::text, 'AES_KEY');
alter table users alter column modifieddate type bytea using PGP_SYM_ENCRYPT(modifieddate::text, 'AES_KEY');
answered Jan 1 at 9:28
SABER - FICTIONAL CHARACTERSABER - FICTIONAL CHARACTER
232212
add a comment |
You cannot encrypt only one user's id and put rest of it as it is , if you want to achieve that you need to alter table as mention by @C.C. Hsu , i.e. to converting data type of userid , modifieddate into bytea or text which is capable of storing both encrypted data and your normal data.
alter table users alter column userid type bytea using PGP_SYM_ENCRYPT(userid::text, 'AES_KEY');
alter table users alter column modifieddate type bytea using PGP_SYM_ENCRYPT(modifieddate::text, 'AES_KEY');
answered Jan 1 at 9:28
SABER - FICTIONAL CHARACTERSABER - FICTIONAL CHARACTER
232212
add a comment |
You cannot encrypt only one user's id and put rest of it as it is , if you want to achieve that you need to alter table as mention by @C.C. Hsu , i.e. to converting data type of userid , modifieddate into bytea or text which is capable of storing both encrypted data and your normal data.
alter table users alter column userid type bytea using PGP_SYM_ENCRYPT(userid::text, 'AES_KEY');
alter table users alter column modifieddate type bytea using PGP_SYM_ENCRYPT(modifieddate::text, 'AES_KEY');
answered Jan 1 at 9:28
SABER - FICTIONAL CHARACTERSABER - FICTIONAL CHARACTER
232212
You cannot encrypt only one user's id and put rest of it as it is , if you want to achieve that you need to alter table as mention by @C.C. Hsu , i.e. to converting data type of userid , modifieddate into bytea or text which is capable of storing both encrypted data and your normal data.
alter table users alter column userid type bytea using PGP_SYM_ENCRYPT(userid::text, 'AES_KEY');
alter table users alter column modifieddate type bytea using PGP_SYM_ENCRYPT(modifieddate::text, 'AES_KEY');
answered Jan 1 at 9:28
SABER - FICTIONAL CHARACTERSABER - FICTIONAL CHARACTER
232212
answered Jan 1 at 9:28
SABER - FICTIONAL CHARACTERSABER - FICTIONAL CHARACTER
232212
answered Jan 1 at 9:28
SABER - FICTIONAL CHARACTERSABER - FICTIONAL CHARACTER
232212
answered Jan 1 at 9:28
SABER - FICTIONAL CHARACTERSABER - FICTIONAL CHARACTER
232212
232212
add a comment |
add a comment |
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%2f53993430%2fhow-to-encrypt-integer-and-date-time-data-type-in-postgresql-using-pgcrypto%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
