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.
Hey @lukeclancy , I am working on Row Level Security also. It appears that I can use
SET ROLE <username>to switch users. Can you useSET ROLEinstead ofSET SESSION AUTHORIZATIONto achieve what you want? Did you figure this out?This comment has been deleted
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/