20

I'm trying to view the raw data from the AACT Database in pgAdmin 4. I'm on a Mac computer. When I just try to view the first 100 rows from the 'complete_oncology' table, I get the below error:

ERROR: permission denied for schema public
LINE 1: SELECT * FROM public.complete_oncology
                      ^
SQL state: 42501
Character: 15

Do I have insufficient permissions? If yes, how do I grant myself permissions to view this table? I am able to see other tables from different Schemas in the AACT database. I've read some users suggest granting myself permissions with something like the below, but no luck:

GRANT SELECT ON complete_oncology TO PUBLIC

This just turns up an error:

ERROR:  relation "complete_oncology" does not exist
SQL state: 42P01
cpburke94
  • 467
  • 1
  • 6
  • 13

3 Answers3

32

With PostgreSQL 15, there has been a change in the way table creation permissions are handled for users. Not directly related to pgAdmin, but I think people may run into this.

Normally, after allowing a user to CREATE tables within a database, you didn't have to specifically define that they had the permission to do that within a SCHEMA, since public would be the default one.

With PostgreSQL 15, this has changed (source):

PostgreSQL 15 also revokes the CREATE permission from all users except a database owner from the public (or default) schema.

So one would get the following error:

ERROR: permission denied for schema public

Now your flow should look like this:

CREATE DATABASE EXAMPLE_DB;
CREATE USER EXAMPLE_USER WITH ENCRYPTED PASSWORD 'Sup3rS3cret';
GRANT ALL PRIVILEGES ON DATABASE EXAMPLE_DB TO EXAMPLE_USER;
\c EXAMPLE_DB postgres
# You are now connected to database "EXAMPLE_DB" as user "postgres".
GRANT ALL ON SCHEMA public TO EXAMPLE_USER;

Obviously change the privileges as you need.

The last step, where we explicitly tell that EXAMPLE_USER has privileges on schema public within EXAMPLE_DB is introduced in PostgreSQL 15.

eaydin
  • 1,434
  • 1
  • 13
  • 11
16

If you get a "permission denied" for public.complete_oncology, but a "relation does not exist" for oncology, that can only mean only one thing: you do not have USAGE permissions on the public schema.

Get the owner of the schema to run

GRANT USAGE ON SCHEMA public TO your_user;

Then you should be able to see the table. If you still lack permissions on the table itself, get the owner to grant you SELECT on the table as well.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Ah thank you, that's what I was afraid of. This schema is owned by Duke, so I sent a message off to their Contact Us form, fingers crossed someone checks it frequently haha. Thank you for your help! – cpburke94 Apr 27 '21 at 03:37
-3
  1. go to preferences setting on popsql
  2. connections
  3. delete connection that you have
  4. add new connection fill the form -connection name ( up to you) -Turn on Connection type "connect directly from my computer" -hostname : localhost (before you can type localhost, turn on the connection type "connect directly from mycomputer") -port : 3306 -database name : same as you create before on cmd sql -username : root
Tienho
  • 1
  • 1