授予数据库权限的不授予表的访问权限

Question

使用此命令授予访问权限的用户后：

GRANT ALL PRIVILEGES ON DATABASE my_newly_created_db_name TO my_user_name;

我发现，授予用户仍然不具有对数据库中的表的访问权限。我查了使用此命令表的特权：

SELECT grantee, string_agg(privilege_type, ', ') AS privileges 
FROM information_schema.role_table_grants 
WHERE table_name='a_table' 
GROUP BY grantee; 

我不知道这是第一次grant on database命令的默认行为。 official doc

我是否需要重新启动数据库以启用新的权限？

Answer 1

数据库和表是两回事。看看数据库的语法。

 
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] } 
    ON DATABASE database_name [, ...] 
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] 

授予所有权限on database database_name包括特权来创建数据库，连接到它们，并创建临时表。就这样。

grant all使用对单个表，或上all tables in schema schema_name授予选择，插入，更新等

 
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } 
    [, ...] | ALL [ PRIVILEGES ] } 
    ON { [ TABLE ] table_name [, ...] 
     | ALL TABLES IN SCHEMA schema_name [, ...] } 
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] 

一种替代方法是change the default privileges in one or more schemas。

 
ALTER DEFAULT PRIVILEGES 
    [ FOR { ROLE | USER } target_role [, ...] ] 
    [ IN SCHEMA schema_name [, ...] ] 
    abbreviated_grant_or_revoke 

where abbreviated_grant_or_revoke is one of: 

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } 
    [, ...] | ALL [ PRIVILEGES ] } 
    ON TABLES 
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]