Postgres user permission is not working

Question

I have created a Postgres user user1, and granted all permission to my_db, when I try to select a table from the database, I'm getting a permission denied error.

Create user1

>>zya$ psql -d postgres
psql (9.6.3)
Type "help" for help.
postgres=# CREATE USER user1  WITH PASSWORD 'password1';
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE my_db to user1;
GRANT
postgres=# \q

Login as user1

>>zya$ psql -d my_db --username=user1
psql (9.6.3)
Type "help" for help.

    my_db=> SELECT DISTINCT  name FROM user_tbl order by id;
ERROR:  permission denied for relation user_tbl

Possible duplicate of [Give all the permissions to a user on a DB](https://stackoverflow.com/questions/22483555/give-all-the-permissions-to-a-user-on-a-db) — jgmh, Jun 22 '17 at 22:05
It's a duplicate, and the solutions in that question will work for the asker. — FuzzyChef, Jun 22 '17 at 22:11

Kamrujjaman Khan · Answer 1 · 2019-01-07T06:30:54.510

1

ALTER DATABASE name OWNER TO new_owner;

you have to change database my_db owner to your username user1

edited Jan 07 '19 at 06:30

answered Jan 07 '19 at 05:58

Kamrujjaman Khan

71
1
2

score 0 · Answer 2 · answered Nov 29 '19 at 06:20

I know this might be late but what you might want to do is to assign This is from my trials and I was able to fix similar issue. It seems you have to set similar privileges to tables function and sequences like shown below

    GRANT ALL PRIVILEGES ON DATABASE yourdb TO yourusr;
    GRANT ALL ON ALL TABLES IN SCHEMA your_schema TO yourusr;
    GRANT ALL ON ALL SEQUENCES IN SCHEMA your_schema TO yourusr;
    GRANT ALL ON ALL FUNCTIONS IN SCHEMA your_schema TO yourusr;

Postgres user permission is not working

2 Answers2