postgresql 授权

I ended up here because my DB user saw only a few tables and not the newer ones. If this is your case, this has helped me.

1. Grant privileges to all existing tables:

   GRANT SELECT ON ALL TABLES IN SCHEMA public TO user;
   

2. Grant privileges to all new tables to be created in future (via default privileges):

   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO user;
   

3. You can also double-check that all tables are granted correctly.

   • Count all existing tables:

     SELECT COUNT(*)
     FROM pg_catalog.pg_tables
     WHERE schemaname != 'pg_catalog' AND
           schemaname != 'information_schema';
     

   • Count all tables the user has access to:

     SELECT COUNT(*)
     FROM information_schema.role_table_grants
     WHERE grantee = 'user';
     

   The count of last two queries must be the same.