Granted all privileges on my PostGres table, but still am getting a "Permission denied" error when attempting to insert/select
Solution 1
Your first command give you the ability to list table (you can just know that there are existing)
GRANT USAGE ON SCHEMA public TO myapp
Then you have to grant SELECT, INSERT, etc... to all the tables in the schema public
GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA public TO myapp
I recommand not giving all privileges to a specific app.
If you have sequences :
GRANT SELECT, UPDATE, USAGE ON ALL SEQUENCES IN SCHEMA public to myapp
If you have functions :
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO myapp
Then your example will work.
But you still have to apply some command if you want futur created table to be able to be accessed :
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, UPDATE, INSERT, DELETE, TRIGGER ON TABLES TO myapp
Postgresql have a very weird mecanism it took me a while to understand it !
Solution 2
At first you have to login your new database using postgres user
psql your_db_name -U postgres -h your_host_name
give the ability to list table
GRANT USAGE ON SCHEMA public TO your_user_name
Then you have to grant permission to all the tables in the schema public
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_user_name
If you have sequences then give permission
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO your_user_name
Solution 3
You're granting permissions in the postgres database instead of the myapp database.
Change the first PSQL command to
psql -Upostgres myapp
And then issue the grants
Solution 4
Take a look at https://dba.stackexchange.com/questions/33943/granting-access-to-all-tables-for-a-user.
I think your issue is that GRANTs are different for the database, schemas, and tables so you need to set permissions on the tables explicitly.
Dave
Updated on June 04, 2022Comments
-
Dave about 2 years
I'm using PostGres 9.5. I'm having trouble inserting data into a table I just created. I'm getting "permission denied" errors despite granting all privileges to the database user. See below ...
localhost:myapp davea$ psql -Upostgres Password for user postgres: psql (9.5.0, server 9.5.1) Type "help" for help. postgres=# GRANT ALL ON schema public TO myapp; GRANT localhost:myapp davea$ psql -Upostgres Password for user postgres: psql (9.5.0, server 9.5.1) Type "help" for help. postgres=# GRANT USAGE ON schema public TO myapp; GRANT postgres=# \q localhost:myapp davea$ myapp psql (9.5.0, server 9.5.1) Type "help" for help. myapp=> insert into search_codes (id, code, address_id) values (1, 'atlanta', 'GA'); ERROR: permission denied for relation search_codes myapp=> select * FROM search_codes; ERROR: permission denied for relation search_codes myapp=> \d search_codes; Table "public.search_codes" Column | Type | Modifiers ------------+-------------------+----------------------------------------------------------- id | integer | not null default nextval('search_codes_id_seq'::regclass) code | character varying | address_id | character varying | Indexes: "search_codes_pkey" PRIMARY KEY, btree (id) "index_search_codes_on_code" UNIQUE, btree (code) "index_search_codes_on_address_id" btree (address_id)
What's the proper way to grant privileges so that my user can insert and select from the table?
-
Dave over 6 yearsNot understanding you. What statements do I ened to run and as what user?
-
Jake about 5 yearsAny other suggestions if you've done this and it still doesn't work?
-
Rémi Desgrange about 5 yearsI think you should ask a new question then
-
Neeraj Sharma about 5 yearsI am getting syntax error on "ALL" in all above queries with ALL in greenplum. ERROR: syntax error at or near "ALL" LINE 1: GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO neera... ^ ********** Error ********** ERROR: syntax error at or near "ALL" SQL state: 42601 Character: 25
-
Kamrujjaman Khan about 5 yearswhat database actually u are using mysql or postgres??Cause your command seems work for me in postgres or may be you are doing something wrong in your previous command
-
Neeraj Sharma about 5 yearsI am using greenplum
-
xonya almost 4 yearsI tried everything but it didn't work. I had to change the ownership of tables and schema.
ALTER TABLE myschema.mytable OWNER TO username;
,ALTER SCHEMA myschema OWNER TO username;