42501 Insufficient Privilege

If you encounter an error message like following, it means that you don’t have permissions to access that particular table or view.

mydb=> select * from students;
ERROR: permission denied for relation students

We have to grant all privileges for the problematic user. Also, run as superuser or user who is having grantable privileges.

1. Connect with user who is superuser or user with grantable privileges.
2. Connect to the database in which table exists.
3. Then, run the following command to grant all privileges on table ‘students’ to user david.

GRANT ALL PRIVILEGES ON TABLE students TO david;

If you encounter an error message like following, it means that you don’t have permissions to access that particular schema, even you have select privilege on the table of that schema.

mydb=> select * from scott.students;
ERROR: permission denied for schema scott

By default, users cannot access any objects in schemas they do not own. To allow that, the owner of the schema must grant the USAGE privilege on the schema. We have to grant usage privilege on that schema to the problematic user. Also, run as superuser or user who is having grantable privileges.

1. Connect with user who is superuser or user with grantable privileges.
2. Connect to the database in which schema exists.
3. Then, run the following command to grant usage privilege on that schema to user david.

GRANT USAGE ON SCHEMA scott TO david;

If you encounter an error message like following, it means that you are not the owner of that particular function, when you try to replace the current definition of an existing function.

CREATE OR REPLACE FUNCTION increment(i integer)
  RETURNS integer AS $$
BEGIN
  RETURN i + 1;
END $$ LANGUAGE plpgsql;

ERROR: must be owner of function increment

You must own the function to replace it (this includes being a member of the owning role). To replace an existing definition, connect to the database with user who is the owner of that function.