Follow dba-ninja.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

dba-ninja.com Links

Dba_db2_button

Sqlserver_dba_button

How to fix permission denied for schema in PostgreSQL

16 June,2022 by Rambler

Question: When attempting to create a PostgreSQL table with the CREATE TABLE command I get an error message:

ERROR: permission denied for schema northland_claims LINE 1: create table mytest(ID int); ^ SQL state: 42501 Character: 14

How can I fix? 

This is the current DDL of the user:

 

CREATE USER myuser WITH ENCRYPTED PASSWORD 'xxxxxxx';
GRANT CONNECT ON DATABASE mydb TO  myuser;
GRANT USAGE ON SCHEMA myschema TO myuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO myuser;

Answer:   To fix this error you'll need to add an extra permission on the schema.    In the example you've given above you'll need to change the GRANT USAGE statement & add the CREATE  command

GRANT USAGE,CREATE ON SCHEMA myschema TO myuser;

 

From the PostgreSQL documentation - "There is no need to grant privileges to the owner of an object (usually the user that created it), as the owner has all privileges by default. (The owner could, however, choose to revoke some of their own privileges for safety.)"

 

If you want some more specific details on How to create a PostgreSQL user for CRUD  then go to the code and customize for your requirements 

 

 

 

 


Author: Rambler (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to fix permission denied for schema in PostgreSQL


dba-ninja.com