Question

permission denied on "SET SESSION AUTHORIZATION [username]"

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)”


Submit an answer


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!

Sign In or Sign Up to Answer

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.

KFSys
Site Moderator
Site Moderator badge
February 16, 2024

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/

Bobby Iliev
Site Moderator
Site Moderator badge
February 16, 2024

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:

https://www.digitalocean.com/support/

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.

https://ideas.digitalocean.com/

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:

https://www.digitalocean.com/community/tutorials/how-to-install-postgresql-on-ubuntu-20-04-quickstart

Hope that helps!

- Bobby.

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

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.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel