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;

   1
   2
   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;

   1
   2
   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';

     1
     2
     3
     4
     5
     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';

     1
     2
     3
     4
     SELECT COUNT(*)
     FROM information_schema.role_table_grants
     WHERE grantee = 'user';
     

   The count of last two queries must be the same.

最后

以上就是无限彩虹最近收集整理的关于postgresql 授权的全部内容，更多相关postgresql内容请搜索靠谱客的其他文章。