Postgres用户对数据库的权限

用户对数据库的权限（登录、超级用户权限）

（1）查看当前数据库中有用户highgo和用户a

highgo=#\du

                             List of roles

 Role name |                   Attributes                   | Member of

-----------+------------------------------------------------+-----------

 a         |                                               | {}

 highgo   | Superuser, Create role, Create DB, Replication | {}

（2）查看确认当前连接的用户为超级用户highgo，且该用户后创建角色和数据库的权限等

highgo=#select current_user;

 current_user

--------------

 highgo

(1row)

（3）查看当前集群中的数据库

highgo=#\l

                              List of databases

   Name   | Owner  | Encoding |  Collate  |   Ctype    | Access privileges

-----------+--------+----------+------------+------------+-------------------

 highgo   | highgo | UTF8     | zh_CN.utf8 |zh_CN.utf8 |

 template0 | highgo | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =c/highgo        +

           |        |          |            |            | highgo=CTc/highgo

 template1 | highgo | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =c/highgo        +

           |        |          |            |            | highgo=CTc/highgo

(3rows)

（4）使用普通用户a连接数据库正常

highgo=#\c highgo a

Youare now connected to database "highgo" as user "a".

highgo=>select current_user;

 current_user

--------------

 a

(1row)

（5）使用超级用户highgo连接数据库正常

highgo=>\c highgo highgo

Youare now connected to database "highgo" as user "highgo".

highgo=#select current_user;

 current_user

--------------

 highgo

(1row)

（6）在超级用户连接highgo后，设置不允许普通用户a连接数据库

highgo=#alter role a nologin;

ALTER ROLE

highgo=#\c highgo a

致命错误:  不允许角色"a" 进行登录

Previousconnection kept

highgo=#

（7）在超级用户连接highgo后，设置不允许普通用户a连接数据库后，赋予用户a超级用户权限后仍然无法连接数据库

highgo=#alter role a superuser;

ALTERROLE

highgo=#\du

                             List of roles

 Role name |                   Attributes                   | Member of

-----------+------------------------------------------------+-----------

 a        | Superuser, Cannot login                        | {}

 highgo   | Superuser, Create role, Create DB, Replication | {}

 

highgo=#\c highgo a

致命错误:  不允许角色"a" 进行登录

Previousconnection kept

（8）将登录数据库的权限赋予用户a后，用户a可登录数据库

highgo=#alter role a login;

ALTERROLE

highgo=#\c highgo a

Youare now connected to database "highgo" as user "a".

highgo=#select current_user;

 current_user

--------------

 a

(1row)