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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |