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 — jgmh
– jgmh, Commented Jun 22, 2017 at 22:05
It's a duplicate, and the solutions in that question will work for the asker. — FuzzyChef
– FuzzyChef, Commented Jun 22, 2017 at 22:11

Kamrujjaman Khan · Accepted Answer · 2019-01-07 06:30:54Z

2

ALTER DATABASE name OWNER TO new_owner;

you have to change database my_db owner to your username user1

edited Jan 7, 2019 at 6:30

answered Jan 7, 2019 at 5:58

Kamrujjaman Khan

811 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Deepak Krishna · Accepted Answer · 2019-11-29 06:20:53Z

0

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;

answered Nov 29, 2019 at 6:20

Deepak Krishna

384 bronze badges

Collectives™ on Stack Overflow

Postgres user permission is not working

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related