Question 221556 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – It's not working -tried many ways still issues exists the same

Mine is Postgresql 13 version and could see some strange issues when I use Alter default privileges,let Me explain.

I have logged in with Postgres user and created database called testdb with schema name called lab and 2 tables called emp1 & emp2

and have created user test under the lab.testdb with select permission (this has give via role) — the complete steps is as follows.

CREATE ROLE read_role;
GRANT SELECT ON ALL TABLES IN SCHEMA lab TO read_role;
CREATE USER test WITH PASSWORD 'xxxxxx';
GRANT CONNECT ON DATABASE testdb TO test;
GRANT USAGE ON SCHEMA lab TO test;
GRANT read_role TO test;

Say have created first table called "emp1" under the lab.testdb,further I have connected to "testdb" using user which I created called "test" –

using that user I’m able to query the table called emp1 sucessfully.

Now the issue comes, say have created another table called "emp2" using the postgres as the user in the "lab.test" .

So if I use

select * from emp2

under the "test" user context Iam getting the error message called-

ERROR:  permission denied for table emp2

so there are manythings shows that if we use Alter default privileges- will fix all the future tables too so I have used below exuected under "postgres"

 ALTER DEFAULT PRIVILEGES FOR ROLE read_role IN SCHEMA lab GRANT SELECT ON TABLES TO read_role;

Post the exuection Iam getting same error messages, but If I Try-

 GRANT SELECT ON ALL TABLES IN SCHEMA lab TO read_role;

its fixing again.

but the same issues occurring if I created other new tables try same way it’s not working at all– Need some one help to resolve ,where am I missing or to fix.

Tags:

2

Answers


  1. 0

    Grant the “test” user select privileges on all tables in the lab schema.
    GRANT SELECT ON ALL TABLES IN SCHEMA lab TO test;

    Login or Signup to reply.
  2. 0

    Not sure but this works fine for me.

    CREATE ROLE read_role LOGIN PASSWORD 'read_role';

grant all privileges on schema lab  to read_role;

grant select on all tables in schema lab  to read_role;

    Login with the role read_role and try once.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search