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?
Best Answer
Your first command give you the ability to list table (you can just know that there are existing)
Then you have to grant SELECT, INSERT, etc... to all the tables in the schema public
I recommand not giving all privileges to a specific app.
If you have sequences :
If you have functions :
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 :
Postgresql have a very weird mecanism it took me a while to understand it !