0

I'm trying to create a new user in postgresql which have read only access to Databases. I followed this document I will just list out the commands I used
CREATE ROLE readaccess;
GRANT CONNECT ON DATABASE db_name TO readaccess;
GRANT USAGE ON SCHEMA public TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;
For this I got the output as ERROR: permission denied for table log

I executed this commands as root user. I do check other blogs, all of them mentioned steps similar to this.

I tried SELECT * from log;
I was getting the output as ERROR: permission denied for table log

The root user have attributes : Create role, Create DB and
member of : {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables,azure_pg_admin}

Can anyone help me on this, or share any of your thoughts

Alexy Pulivelil
  • 87
  • 10

1 Answers1

0

Please try below and see if it helps:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user_name;
ShaktiSingh-MSFT
  • 314
  • 1
  • 4
  • Hi, Thanks for adding the comment. I tried this command and it executed without any error. But on executing `SELECT * FROM ` I'm getting error as `ERROR: permission denied for table ` If the user is having readpermission theis command should work right ? Or I'm I missing something ? – Alexy Pulivelil Mar 16 '23 at 12:17
  • Try this: ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC; – ShaktiSingh-MSFT Mar 17 '23 at 08:15
  • Thanks, I will try and give an update.what I'm not getting is, even the root user is not able to execute pg_dumb this is the error pg_dump: error: query failed: ERROR: permission denied for table log pg_dump: error: query was: LOCK TABLE public.log IN ACCESS SHARE MODE – Alexy Pulivelil Mar 18 '23 at 09:04
  • 1) [ sudo ] su postgres 2) psql 3) GRANT USAGE ON SCHEMA public TO ; 4) GRANT SELECT ON ALL TABLES IN SCHEMA public TO ; – ShaktiSingh-MSFT Mar 20 '23 at 10:47
  • I have tried this too, but getting same error as I mentioned above. If my application is not running, there is no such error, otherwise there will issues. – Alexy Pulivelil Mar 21 '23 at 18:31
  • Please try afresh and see if this happens for other users too. – ShaktiSingh-MSFT Mar 22 '23 at 05:13
  • I tried with a user having full PRIVILEGES, still having same issue – Alexy Pulivelil Mar 31 '23 at 07:43