Rails: Pg::Insufficientprivilege: Error: Permission Denied for Relation Schema_Migrations

Rails: PG::InsufficientPrivilege: ERROR: permission denied for relation schema_migrations

I guess you missed create password for your user. Try to create password as following:

CREATE USER root WITH PASSWORD 'your_new_password';
CREATE DATABASE svp-chicago_development;
GRANT ALL PRIVILEGES ON DATABASE svp-chicago_development to root;
ALTER DATABASE svp-chicago_development OWNER TO root;

PG::InsufficientPrivilege at / ERROR: permission denied for relation schema_migrations

Worked fine finally

logged as superuser

ALTER TABLE schema_migrations OWNER TO menuquizz

\q

logged as menuquizz

menuquizz_development=# \d
                    List of relations
 Schema |           Name           |   Type   |   Owner   
--------+--------------------------+----------+-----------
 public | .schema_migrations       | table    | denis
 public | menus                    | table    | menuquizz
 public | menus_id_seq             | sequence | menuquizz
 public | schema_migrations        | table    | menuquizz
(4 rows)

menuquizz_development=# \q

:~/Projects/menuquizz$ rake db:fixtures:load FIXTURES=menus
:~/Projects/menuquizz$ psql -U menuquizz menuquizz_development

Password for user menuquizz: 
psql (9.3.10)
Type "help" for help.

menuquizz_development=> select count(*) from menus;
 count 
-------
    45
(1 row)

OK

Rails: InsufficientPrivilege: ERROR: permission denied for relation schema_migrations

The database has changed ownership, but the objects in it have not. So the schema still belongs to the old user, and the new user has no permissions on it.

When it comes to permissions and ownership, databasea are objects like all other objects. To use an analogy, you don't own everything in a house just because you own the house.

The REASSIGN OWNED SQL command can change ownership of all objects in a database.

Rails: permission denied for relation schema_migrations

It seems you have to create a DB user with all needed privileges on your DB.
For example I think you could do the trick by log in your DB console then do something like:

CREATE USER your_new_username WITH PASSWORD 'your_new_password';
CREATE DATABASE whiteboard;
GRANT ALL PRIVILEGES ON DATABASE whiteboard to your_new_username;
ALTER DATABASE whiteboard OWNER TO your_new_username;

Then update you database.yml like this:

adapter: postgresql
database: whiteboard
username: your_new_username
password: your_new_password
pool: 5
timeout: 5000

Hope it helps!

ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR: permission denied for relation schema_migrations

I'm not sure if you're running rake db:migrate with RAILS_ENV as production or development. Whichever it is (development by default), in your config/database.yml it will say the user, password, and database it is running on. That user must have ALL privileges on the public.schema_migrations table. If it does, and it's still not working, make sure that user has ALL privileges on the public schema.

Read more about manipulating postgres database privileges here. Postgres has excellent documentation.

One more thing: if you're creating this database locally, and trying to create the initial database instead of actually running a migration, use rake db:schema:load instead of rake db:migrate. Never run this on production, as it will delete your data!

ERROR: permission denied for relation tablename on Postgres while trying a SELECT as a readonly user

Here is the complete solution for PostgreSQL 9+, updated recently.

CREATE USER readonly  WITH ENCRYPTED PASSWORD 'readonly';
GRANT USAGE ON SCHEMA public to readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

-- repeat code below for each database:

GRANT CONNECT ON DATABASE foo to readonly;
\c foo
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly; --- this grants privileges on new tables generated in new database "foo"
GRANT USAGE ON SCHEMA public to readonly; 
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Thanks to https://jamie.curle.io/creating-a-read-only-user-in-postgres/ for several important aspects

If anyone find shorter code, and preferably one that is able to perform this for all existing databases, extra kudos.

---

Related Topics