How to switch session role securely?
I'd like to setup a service through which my users can submit SQL queries to a database wherein I store some data. Users are assigned to organizations, and I've setup views that provide read access only to rows that belong to a given organization, and roles that have SELECT on those views.
I'd like to not have to manage a ton of usernames and passwords. Instead, my plan was to connect to the DB as a more privileged role, and before executing a query, run SET SESSION ROLE <relevant_organization_role> and then execute the user's query.
However, in testing, it seems like it's possible for the user to just RESET ROLE and then run their query as the more privileged role, which is a non-starter.
I tried using SET SESSION AUTHORIZATION, but the docs say
The session user identifier can be changed only if the initial session user (the authenticated user) had the superuser privilege.
And I'd rather not use a superuser role for the initial connection. Plus, when I've tested this approach in my client, it lets me SET SESSION AUTHORIZATION DEFAULT afterwards and return to the superuser role -- and I definitely don't want to risk that.
The PostreSQL GRANT docs say
[A role] may grant or revoke membership in itself from a database session where the session user matches the role.
I take that to suggest that I should be able to do something like
-- from a connection to the "service" role
SET SESSION ROLE tenant_1; -- switch to a new role
REVOKE SESSION ROLE service; -- prevent subsequent use of the "service" role
-- use this connection to execute queries as "tenant_1" without being able to use RESET ROLE to restore the "service" role privileges
But the only information I can really find on session roles is SET ROLE which doesn't have any info on revoking a role from a session.
Is it possible to revoke a role from a session, such that the rest of the session has to run in a more restricted role?
Or do I just have to be prepared to manage a whole bunch of username + password pairs?
Or is there some other way to accomplish what I'm trying?
postgresql security role
asked 12 mins ago
DathanDathan
1011
New contributor
Dathan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I'd like to setup a service through which my users can submit SQL queries to a database wherein I store some data. Users are assigned to organizations, and I've setup views that provide read access only to rows that belong to a given organization, and roles that have SELECT on those views.
I'd like to not have to manage a ton of usernames and passwords. Instead, my plan was to connect to the DB as a more privileged role, and before executing a query, run SET SESSION ROLE <relevant_organization_role> and then execute the user's query.
However, in testing, it seems like it's possible for the user to just RESET ROLE and then run their query as the more privileged role, which is a non-starter.
I tried using SET SESSION AUTHORIZATION, but the docs say
The session user identifier can be changed only if the initial session user (the authenticated user) had the superuser privilege.
And I'd rather not use a superuser role for the initial connection. Plus, when I've tested this approach in my client, it lets me SET SESSION AUTHORIZATION DEFAULT afterwards and return to the superuser role -- and I definitely don't want to risk that.
The PostreSQL GRANT docs say
[A role] may grant or revoke membership in itself from a database session where the session user matches the role.
I take that to suggest that I should be able to do something like
-- from a connection to the "service" role
SET SESSION ROLE tenant_1; -- switch to a new role
REVOKE SESSION ROLE service; -- prevent subsequent use of the "service" role
-- use this connection to execute queries as "tenant_1" without being able to use RESET ROLE to restore the "service" role privileges
But the only information I can really find on session roles is SET ROLE which doesn't have any info on revoking a role from a session.
Is it possible to revoke a role from a session, such that the rest of the session has to run in a more restricted role?
Or do I just have to be prepared to manage a whole bunch of username + password pairs?
Or is there some other way to accomplish what I'm trying?
postgresql security role
asked 12 mins ago
DathanDathan
1011
New contributor
Dathan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I'd like to setup a service through which my users can submit SQL queries to a database wherein I store some data. Users are assigned to organizations, and I've setup views that provide read access only to rows that belong to a given organization, and roles that have SELECT on those views.
I'd like to not have to manage a ton of usernames and passwords. Instead, my plan was to connect to the DB as a more privileged role, and before executing a query, run SET SESSION ROLE <relevant_organization_role> and then execute the user's query.
However, in testing, it seems like it's possible for the user to just RESET ROLE and then run their query as the more privileged role, which is a non-starter.
I tried using SET SESSION AUTHORIZATION, but the docs say
The session user identifier can be changed only if the initial session user (the authenticated user) had the superuser privilege.
And I'd rather not use a superuser role for the initial connection. Plus, when I've tested this approach in my client, it lets me SET SESSION AUTHORIZATION DEFAULT afterwards and return to the superuser role -- and I definitely don't want to risk that.
The PostreSQL GRANT docs say
[A role] may grant or revoke membership in itself from a database session where the session user matches the role.
I take that to suggest that I should be able to do something like
-- from a connection to the "service" role
SET SESSION ROLE tenant_1; -- switch to a new role
REVOKE SESSION ROLE service; -- prevent subsequent use of the "service" role
-- use this connection to execute queries as "tenant_1" without being able to use RESET ROLE to restore the "service" role privileges
But the only information I can really find on session roles is SET ROLE which doesn't have any info on revoking a role from a session.
Is it possible to revoke a role from a session, such that the rest of the session has to run in a more restricted role?
Or do I just have to be prepared to manage a whole bunch of username + password pairs?
Or is there some other way to accomplish what I'm trying?
postgresql security role
asked 12 mins ago
DathanDathan
1011
New contributor
Dathan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I'd like to setup a service through which my users can submit SQL queries to a database wherein I store some data. Users are assigned to organizations, and I've setup views that provide read access only to rows that belong to a given organization, and roles that have SELECT on those views.
I'd like to not have to manage a ton of usernames and passwords. Instead, my plan was to connect to the DB as a more privileged role, and before executing a query, run SET SESSION ROLE <relevant_organization_role> and then execute the user's query.
However, in testing, it seems like it's possible for the user to just RESET ROLE and then run their query as the more privileged role, which is a non-starter.
I tried using SET SESSION AUTHORIZATION, but the docs say
The session user identifier can be changed only if the initial session user (the authenticated user) had the superuser privilege.
And I'd rather not use a superuser role for the initial connection. Plus, when I've tested this approach in my client, it lets me SET SESSION AUTHORIZATION DEFAULT afterwards and return to the superuser role -- and I definitely don't want to risk that.
The PostreSQL GRANT docs say
[A role] may grant or revoke membership in itself from a database session where the session user matches the role.
I take that to suggest that I should be able to do something like
-- from a connection to the "service" role
SET SESSION ROLE tenant_1; -- switch to a new role
REVOKE SESSION ROLE service; -- prevent subsequent use of the "service" role
-- use this connection to execute queries as "tenant_1" without being able to use RESET ROLE to restore the "service" role privileges
But the only information I can really find on session roles is SET ROLE which doesn't have any info on revoking a role from a session.
Is it possible to revoke a role from a session, such that the rest of the session has to run in a more restricted role?
Or do I just have to be prepared to manage a whole bunch of username + password pairs?
Or is there some other way to accomplish what I'm trying?
postgresql security role
postgresql security role
asked 12 mins ago
DathanDathan
1011
New contributor
Dathan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 12 mins ago
DathanDathan
1011
New contributor
Dathan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 12 mins ago
DathanDathan
1011
New contributor
Dathan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 12 mins ago
DathanDathan
1011
asked 12 mins ago
DathanDathan
1011
1011
New contributor
Dathan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Dathan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Dathan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});
}
});
Dathan is a new contributor. Be nice, and check out our Code of Conduct.
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%2fdba.stackexchange.com%2fquestions%2f229331%2fhow-to-switch-session-role-securely%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
Dathan is a new contributor. Be nice, and check out our Code of Conduct.
Dathan is a new contributor. Be nice, and check out our Code of Conduct.
Dathan is a new contributor. Be nice, and check out our Code of Conduct.
Dathan is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f229331%2fhow-to-switch-session-role-securely%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
