CODEWITHSUNDEEP
databasepostgresqlpgadminpermission-deniedpgadmin-4
cpburke94
cpburke94

Reputation: 1297

Why am I getting a permission denied error for schema public on pgAdmin 4?

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

Upvotes: 103

Views: 153284

Answers (9)

FlyingV
FlyingV

Reputation: 3565

TLDR:

ALTER DATABASE my_database OWNER TO my_database_user;

Justification: There have been recent changes to improve the security of POSTGRES depending on HOW and WHO created the database. The long and short is now you may NEED to specify that you are also the OWNER of the database in addition to having the permissions.

Upvotes: 82

Daniil Palii
Daniil Palii

Reputation: 1230

Except other answers here check if tables owner set to the same user as schema and database and who make query.

Upvotes: 0

Krishna Nadagoudra
Krishna Nadagoudra

Reputation: 121

Try these two commands for Postgres 15+ versions, after creating database and user as admin role.

  • GRANT ALL ON DATABASE db_name TO user_name;
  • ALTER DATABASE db_name OWNER TO user_name;

Upvotes: 12

Ashutosh
Ashutosh

Reputation: 481

In PostgreSQL 14 and in prior versions, by default anybody can create a table. but not after In PostgreSQL 15+. So the best option is to connect your database using master user of postgres database and give the grant permission. db_user is the user which require to connect your mydb database.

postgres=# \c mydb postgres
You are now connected to database "mydb" as user "postgres".
mydb=# GRANT ALL ON SCHEMA public TO db_user;
GRANT
mydb=#

Upvotes: 6

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

For example, you can grant the user john on public schema with GRANT statement as shown below:

GRANT ALL PRIVILEGES ON SCHEMA public TO john;

Or, you can alter public schema's owner to the user john with ALTER SCHEMA statement as shown below:

ALTER SCHEMA public OWNER TO john;

Or, you can alter apple database's owner to the user john on apple database with ALTER DATABASE statement as shown below:

ALTER DATABASE apple OWNER TO john;

Memos:

  • You can omit PRIVILEGES which is optional.

  • You must log in with any superusers(e.g., postgres).

Be careful, even if you grant the user john on apple database with GRANT statement as shown below:

GRANT ALL PRIVILEGES ON DATABASE apple TO john;

Then, you cannot solve the error as shown below:

apple=> CREATE TABLE person (
apple(> id INTEGER,
apple(> name VARCHAR(20)
apple(> );
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE person (
                     ^

Upvotes: 4

Laurenz Albe
Laurenz Albe

Reputation: 247625

If you get a "permission denied" for public.complete_oncology, but a "relation does not exist" for complete_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.

Upvotes: 52

Manjunath Huddar
Manjunath Huddar

Reputation: 11

in PostgreSQL 15, there has been a change in giving the permission. i was also facing the issue but it is now solved jus we have to do is :

Switch to the PostgreSQL User: su postgres Access PostgreSQL Interactive Shell: psql Connect to the Desired Database: \c DATABASE_NAME Grant All Privileges on the Schema: GRANT ALL ON SCHEMA public TO db_user; Exit the PostgreSQL Shell: \q Restart the PostgreSQL Service:sudo service postgresql restart

Upvotes: 1

eaydin
eaydin

Reputation: 3301

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.

Upvotes: 207

Tienho
Tienho

Reputation: 1

  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

Upvotes: -5

Related Questions