PostgreSQL用户与角色（postgresql数据库）

用户和角色

PostgreSQL使用角色的概念管理数据库访问权限。角色时一系列相关权限的集合。为了管理方便，通常会把一系列相关的数据库权限赋给一个角色，如果哪个用户需要这些权限，就把角色赋给相应的用户。由于用户也拥有一系列的相关权限，为了简化管理，在PostgreSQL中，角色与用户时没有区别的，一个用户也是角色，我们可以把一个用户的权限赋给另一个用户。

用户和角色在整个数据库实例中都是全局的，且在同一个实例中的不同数据库中，看到的用户也都是相同的。

在初始化数据库系统时，有一个预定义的超级用户，这个用户的名称与初始化该数据库的操作系统用户名相同。如果数据库是建在操作系统用户postgres，这个数据库超级用户的名称也会叫"postgres"。可以用这个超级用户连接数据库，然后创建出更多的用户。

创建用户和角色

 CREATE ROLE name [ [ WITH ] option [ ... ] ]
 CREATE USER name [ [ WITH ] option [ ... ] ]
 
 where option can be:
     
       SUPERUSER | NOSUPERUSER    超级用户
     | CREATEDB | NOCREATEDB      建库权限
     | CREATEROLE | NOCREATEROLE  创建角色权限
     | CREATEUSER | NOCREATEUSER  创建用户权限
     | INHERIT | NOINHERIT        继承权限
     | LOGIN | NOLOGIN            登陆权限
     | CONNECTION LIMIT connlimit  指定用户可以使用的并发连接数量
     | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'  密码是否加密
     | VALID UNTIL 'timestamp'     密码失效时间
     | IN ROLE rolename [, ...]    指定用户成为哪些角色的成员
     | IN GROUP rolename [, ...]
     | ROLE rolename [, ...]       role_name将成为这个新建的角色的成员
     | ADMIN rolename [, ...]      
     | USER rolename [, ...]
     | SYSID uid 

在PostgreSQL中，用户与角色是没有区别的。除了"CREATE USER"默认创建出来的用户有"LOGIN"权限，而"CREATE ROLE"创建出来的用户没有"LOGIN"权限之外，没有其他任何区别。

权限的管理

在PostgreSQL数据库中，每个数据库的逻辑结构对象(包括数据库)都有一个所有者，也就是说任何数据库对象都是属于某个用户的，所有者默认就拥有所有权限。

所以不需要把对象的权限再赋给所有者。自己创建的数据库，自己当然有全部的权限。当然，所有者处于安全考虑也可以选择废弃一些自己的权限。在PostgreSQL数据库中，删除一个对象及任意修改它的权限都不能赋予别人，它是所有者固有的，不能被赋予或撤销。

一个用户的权限分为两类，一类是在创建用户时就指定的权限，这些权限如下:

• 超级用户的权限
• 创建用户的权限
• 是否允许LOGIN的权限

这些权限是创建用户时指定的，后面可使用ALTER ROLE来修改。

还有一类权限，是由命令GRANT和REVOKE来管理的，这些权限如下：

• 在数据库中创建模式(SCHEMA)
• 允许在指定的数据库中创建临时表
• 连接某个数据库
• 在模式中创建数据库对象，如创建表、视图、函数等
• 在一些表中做SELECT、UPDATE、INSERT、DELETE操作等
• 在一张表的具体列上进行SELECT、UPDATE、INSERT操作
• 对序列进行查询(执行序列的currval函数)、使用(执行序列的currval函数和nextval函数)、更新等操作
• 在声明表上创建触发器
• 可以把表、索引等建到指定的表空间

在使用时，需要分清楚上述两类权限，如果要给用户赋予创建数据库的权限，则需要使用"ALTER ROLE"命令。

如果要给用户赋予创建模式的权限时，需要使用"GRANT"命令

 ALTER ROLE 格式
 ALTER ROLE name [ [ WITH ] option [ ... ] ]
 
 修改角色的密码
 ALTER ROLE davide WITH PASSWORD 'hu8jmn3';
 
 修改角色密码过期时间
 ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';
 
 修改密码永不过期
 ALTER ROLE fred VALID UNTIL 'infinity';
 
 赋予创建数据库创建角色权限
 ALTER ROLE miriam CREATEROLE CREATEDB;
 
 给角色设置参数
 ALTER ROLE worker_bee SET maintenance_work_mem = 100000;

GRANT命令有两个作用，一个是让某个用户成为某个角色成员，从而使其拥有角色的权限：

 GRANT role [, ...] TO username [, ...] [ WITH ADMIN OPTION ]

另一个作用是把某些数据库逻辑结构对象的操作权限赋予某个用户(或角色)

 GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
     [,...] | ALL [ PRIVILEGES ] }
     ON [ TABLE ] tablename [, ...]
     TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
 GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
     ON DATABASE dbname [, ...]
     TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
 GRANT { EXECUTE | ALL [ PRIVILEGES ] }
     ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
     TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
 GRANT { USAGE | ALL [ PRIVILEGES ] }
     ON LANGUAGE langname [, ...]
     TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
 GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
     ON SCHEMA schemaname [, ...]
     TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
 GRANT { CREATE | ALL [ PRIVILEGES ] }
     ON TABLESPACE tablespacename [, ...]
     TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

创建一个只读用户

 REVOKE CREATE ON SCHEMA public from public;
 这是因为PostgreSQL中默认任何用户都可以在名称为public的schema中创建表，而只读用户是不允许建表的，所以先要把这个权限收回。
 
 创建一个readonly用户
 CREATE USER readonly with password 'query';
 
 把public schema下的所有表的SELECT权限赋给用户readonly
 GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
 
 上面命令只是把现有表的权限给了用户readonly，如果此时创建了表，readonly用户还是不能读，需要使用下面的SQL把所建表的SELECT权限也给用户readonly
 ALTER DEFAULT PRIVILEGES IN SCHEMA public grant select on tables to readonly;
 
 注意，上面的过程只是给schema下的名为public表赋予了只读权限，如果想让用户访问其他schema下的表，执行下面SQL
 GRANT SELECT ON ALL TABLES IN SCHEMA other_schema TO readonly;
 ALTER DEFAULT PRIVILEGES IN SCHEMA other_schema grant select on tables to readonly;