CODEWITHSUNDEEP
postgresqlselectpermissionsinsertsql-grant
Dave
Dave

Reputation: 19150

Granted all privileges on my PostGres table, but still am getting a "Permission denied" error when attempting to insert/select

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?

Upvotes: 12

Views: 19681

Answers (4)

Kamrujjaman Khan
Kamrujjaman Khan

Reputation: 81

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

Upvotes: 6

Rémi Desgrange
Rémi Desgrange

Reputation: 908

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 !

Upvotes: 23

Jared Sohn
Jared Sohn

Reputation: 444

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.

Upvotes: 1

Gary - Stand with Ukraine
Gary - Stand with Ukraine

Reputation: 7541

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

Upvotes: 2

Related Questions