All threads / PG::InsufficientPrivilege: ERROR: permission denied to create extension "pgcrypto" HINT: Must be superuser to create this extension. : CREATE EXTENSION IF NOT EXISTS "pgcrypto"

Ask A Question

Notifications

You’re not receiving notifications from this thread.

PG::InsufficientPrivilege: ERROR: permission denied to create extension "pgcrypto" HINT: Must be superuser to create this extension. : CREATE EXTENSION IF NOT EXISTS "pgcrypto"

Juan S. Caro asked in Databases

Hi everyone, I have been deploying following the guides of this site. However, I had introduced the use of UUID and so I have set the use of extensions for PostgreSQL. Now, when I deploy, I get the following error:

PG::InsufficientPrivilege: ERROR:  permission denied to create extension "pgcrypto"
HINT:  Must be superuser to create this extension.
: CREATE EXTENSION IF NOT EXISTS "pgcrypto"

It's crear that I need to be superuser, but I gave superuser power to deploy user, and I wouldn't know what to do next. Any help on that? Thanks

For anyone having this issue in 2020, the problem is that the user deploy was not created with SUPERUSER privileges which are required to create/enable extensions in Postgres.

In order to solve that, you have to give this privilege to the user deploy by doing the following:

1 - ssh [email protected]
2 - sudo su - postgres
3 - psql
4 - ALTER ROLE deploy SUPERUSER;
5 - \q
6 - exit

Now, re-run the deployment command: bundle exec cap production deploy and it should work.

If you wan't to revoke the SUPERUSER privilate later for the deploy user, repeat steps 1 to 6 above, replacing step for by:

ALTER ROLE deploy NOSUPERUSER;

I hope this helps someone :)

Join the discussion

Want to stay up-to-date with Ruby on Rails?

Join 33,665+ developers who get early access to new tutorials, screencasts, articles, and more.

    We care about the protection of your data. Read our Privacy Policy.

    logo Created with Sketch.

    Ruby on Rails tutorials, guides, and screencasts for web developers learning Ruby, Rails, Javascript, Turbolinks, Stimulus.js, Vue.js, and more. Icons by Icons8

    © 2020 GoRails, LLC. All rights reserved.