permission denied to create extension "uuid-ossp"

Question

I'm building a Rails project on postgresql, but when I

rake db:migrate

I get the following error:

 permission denied to create extension "uuid-ossp"

Here's how my database.yml looks like:

app_common: &app_common
adapter: postgresql
host: 127.0.0.1
port: 5432
encoding: unicode
pool: 5
username: root
password:

Pamphleteer

Answer 1

Figured it out, I need to postegresql and change the root role to superuser:

$psql

ALTER USER root WITH SUPERUSER;

Pamphleteer

It seems this will work, but I may want to advice caution. Adding the user as a superuser allows him to create other users, thereby this can become a security concern. – Spinthariscope

become? This is reckless. you're willingly opening a security hole for a simple task. If you have access to alter users, just enable the extension. – Armoured

Giving attention to the issue of security concern, maybe you could consider revoking the SUPERUSER privilege once the extension is successfully created, or better, you create another user with the minimal privileges and use the root to create the objects you need, but never user root (as SUPERUSER) in your application. Hope it goes well. – Pederasty

Answer 2

ALTER USER root WITH SUPERUSER if the user of the database was root but in case it wasn't, you need to run psql as a superuser first before you run this command so you run

sudo -u postgres psql postgres

then

ALTER USER mydb_user WITH SUPERUSER;

Hammad

Answer 3

Alternatively, starting with Postgresql 13, a role having the CREATE privilege on the current database can install trusted extensions without being a SUPERUSER.

From Postgresql documentation :

Loading an extension ordinarily requires the same privileges that would be required to create its component objects. For many extensions this means superuser privileges are needed.
However, if the extension is marked trusted in its control file, then it can be installed by any user who has CREATE privilege on the current database.
In this case the extension object itself will be owned by the calling user, but the contained objects will be owned by the bootstrap superuser (unless the extension's script explicitly assigns them to the calling user). This configuration gives the calling user the right to drop the extension, but not to modify individual objects within it.

Depending on your installation, trusted extensions should at least be the following :

btree_gin btree_gist citext cube dict_int fuzzystrmatch hstore intarray isn lo ltree pgcrypto pg_trgm plpgsql seg tablefunc tcn tsm_system_rows tsm_system_time unaccent uuid-ossp

You can check or switch the trusted flag in the /usr/local/share/postgresql/extension/*.control files, again depending on your installation.

Giving a PostgreSQL Instance with a database maindb and two roles:

• postgres (default superuser)
• jack (limited privileges)

$ psql -U jack -d maindb

maindb=>CREATE EXTENSION plpgsql;

ERROR:  permission denied to create extension "plpgsql"

HINT:  Must have CREATE privilege on current database to create this extension.

maindb=> \q

Obviously jack can't add the extension to maindb, now let's give them the right privilege and try again :

$ psql -U postgres 

postgres=# GRANT CREATE ON DATABASE maindb to jack;
GRANT
postgres=# \q

$ psql -U jack -d maindb

maindb=>CREATE EXTENSION plpgsql;
CREATE EXTENSION

And voilà, no need to give the SUPERUSER privileges anymore (which you should never do anyway).

Vollmer

This is not working on Amazon RDS. I which to create database using UUIDs (CREATE EXTENSION IF NOT EXISTS "uuid-ossp";) on RDS without using prostgres. Any idea how to do this there? – Ward

I can confirm the same for Amazon RDS. – Flor

Answer 4

solution:

1. ALTER USER root WITH SUPERUSER;

   OR

2. using PGADMIN, Steps:

   localhost -> Login Role -> Right click role i.e. postgres -> ** in Role privileges tab** -> check SuperUser -> OK.

Enjoy.

Antionetteantioxidant

Answer 5

follow these commands:

1. psql -U "user_with_create_privilege" -d "db"
2. CREATE EXTENSION "uuid-ossp";
3. \q

should solve the issue!

Westcott

Answer 6

The user who owns the postgresql database needs to be have superuser role. So you need to get into postgres and alter the user have super user role.

In your case the owner of the DB is root

run

sudo su - postgres

then run:

psql

Then run

ALTER ROLE root SUPERUSER;

quit postgres

\q

and exit.

exit

cd back into your app directory and your rake migrate command will work

Ileana

Answer 7

I found a safer way. Just log into the database as postgres superuser and create the extension like this:

$ psql -U postgres -d yourdatabase
dbname=# CREATE EXTENSION "extension";

or

dbname=# \i \path\to\file.sql

Your dbname is going to show up but you will be logged as the postgres superuser. No need to grant anything to anyone.

Ever

Answer 8

For DBeaver,

Right click on DB -> view database -> under Properties go to the tab Roles -> select user and assign role to it.

For superuser role, the user A which is assigning superuser role to other user B, user A should be superuser itself.

Melbourne