42501: INSUFFICIENT PRIVILEGE ERROR while querying in Postgresql

Question

I am trying to query a database table in postgresql, but every time I run the below query it gives me the INSUFFICIENT PRIVILEGE error. What possibly could be the reason for such permission denied error. Also, I am using pgadmin tool in windows to connect the database which is in Linux environment. Below is the query I am running

> > SELECT appid,hash 
>       FROM app
>       WHERE appid=1;

While running the same query I am getting the below Error

ERROR:  permission denied for relation app

********** Error **********

ERROR: permission denied for relation app
SQL state: 42501

Clover

Yes I am running on Linux – Clover

Answer 1

The user running the query will need permissions to that table. You can grant them to that user with the GRANT statement. The below is an example that grants to PUBLIC

GRANT SELECT ON tablename TO PUBLIC;

Also I have seen SELinux cause isses and places such as here mention it. I am not exactly sure of the command to turn SELinux off but you can see if it is running by using

selinuxenabled && echo enabled || echo disabled

Gainless

Thanks harper89, I have tried the GRANT statement it still did not gave me the privilege,instead showing me the same error as before (42501) and also I have run the command you mentioned above, it says "enabled" What possibly be the reason, is it like the admin or "root" have to run things? – Clover

@akiiddweeber NP. You can try GRANT while running as admin on the database. Running as the same user who cannot access the table won't allow the GRANT to go through. I would address the GRANT situation first with someone with admin rights before looking into the SELinux issue. – Gainless

Thanks harper89 for the comment, I am trying to catch the same. – Clover

Answer 2

It simply means that you have no permission to access app table. Request your root or database administrator to grant you the permission to access app table. if your are the root or have granting privilege you can use grant command to grant your self permission to use all sql statements on table or database
For Example:

               grant all privileges on database money to cashier;

before that you have to login as root or user that have granting privileges
for more details on this command refer to http://www.postgresql.org/docs/8.1/static/sql-grant.html

Miliary

Answer 3

If it's DB2 then go to command console of DB2, select your respective Database and select Authorities option by right click on the Database then add your respective DB2 user and grant required access.

Augmenter

Answer 4

You need to make sure that the user with which you are connecting with also has the "USAGE" access on the schema you are trying to access with the user. I have recently faced an error where I got the dump restored into a database and then had some users to whom I was only supposed to provide the read-only access. I have followed the following steps -

CREATE ROLE myapp_readonly;
GRANT CONNECT ON DATABASE {database} TO myapp_readonly;
GRANT USAGE ON SCHEMA {schema} TO myapp_readonly;
GRANT SELECT ON TABLE {schema}.{table_name} TO myapp_readonly;
GRANT myapp_readonly TO {usre};

After performing these steps when I tried to access the table, had received the following error -

SQL Error [42501]: ERROR: permission denied for schema {schema}

In my case, my users were available already and the schemas and the database were restored recently. After I have provided the "USAGE" access to the schema to the user the error was resolved.

Metaphase