Question
permission denied on "SET SESSION AUTHORIZATION [username]"
- Posted on February 15, 2024
- DigitalOcean Managed PostgreSQL Database
Asked by LukeClancy
I am utilizing row-level-security policies to create different ‘areas’ depending on which database user is currently connected. On development this has been working fine.
But in production with digital ocean databases I was unable to use the “SET SESSION AUTHORIZATION” command. This command changes which role the connection uses for future requests.
This means I am unable to switch the connection’s role (aka switch which area is accessible) depending on the request’s context.
This was not expected and is causing major issues. I believe that this is a feature that is not supported - but I would like to be thorough. Am I missing something? Is there a way around this? Or will I have to (bleh) manage my own database on a VM or something?
Error message: “PG::InsufficientPrivilege: ERROR: permission denied to set session authorization (ActiveRecord::StatementInvalid)”
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.
Featured on Community
Get our biweekly newsletter
Sign up for Infrastructure as a Newsletter.
Hollie's Hub for Good
Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.
Become a contributor
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
Heya @lukeclancy,
You are entirely correct, this is not something that is supported however it’s an easy fix, just contact DigitalOcean’s support on
https://www.digitalocean.com/support/
and they should be able to assist you out. Additionally, if you have a ticket reference, If you provide it here I can try and speed things up for you.
Lastly, try and use the ideas board to create a way in the future for you and other users like you to use the feature without you having to contacting support. https://ideas.digitalocean.com/
Hey @lukeclancy,
Indeed, this does not look to be supported out of the box, what you could do is reach out to the DigitalOcean support team who might be able to help you out here:
Though it is possible that this might not be available, so the best thing to do to get your voice heard regarding this would be to head over to our Product Ideas board and post a new idea, including as much information as possible for what you’d like to see implemented.
If this is the case, what you could do is, as you mentioned, use a Droplet and install Postgres there. That way you will have root access to the server and full control over all of the Postgres configurations:
Hope that helps!
- Bobby.