Postgres用户创建数据库的权限

用户创建数据库的权限

（1）检查确认超级用户highgo拥有创建数据库的权限，普通用户a没有创建数据库的权限

highgo=#\du

                             List of roles

 Role name |                   Attributes                   | Member of

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

 a        |                                               | {}

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

 

highgo=#select current_user;

 current_user

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

 highgo

(1row)

 

highgo=#create database highgo_t;

CREATEDATABASE

highgo=#\c highgo a

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

highgo=>create database a_t;

错误:  创建数据库权限不够

highgo=>

（2）赋予普通用户a创建数据库的权限，但并未赋予超级用户权限，此时用户a可创建数据库

highgo=#alter user a createdb ;

ALTERROLE

highgo=#\du

                             List of roles

 Role name |                   Attributes                   | Member of

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

 a        | Create DB                                      | {}

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

 

highgo=#\c highgo a

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

highgo=>create database a_t;

CREATEDATABASE

highgo=>\l

                              List of databases

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

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

 a_t      | a      | UTF8     | zh_CN.utf8 | zh_CN.utf8 |

 highgo   | highgo | UTF8     | zh_CN.utf8 |zh_CN.utf8 | =Tc/highgo       +

           |        |          |            |            | highgo=CTc/highgo

 highgo_t | 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

(5rows)

 

（3）赋予用户a超级用户的权限，但并未单独赋予创建数据库的权限，此时用户a可创建数据库

highgo=#alter user a nocreatedb ;

ALTERROLE

highgo=#\du

                             List of roles

 Role name |                   Attributes                   | Member of

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

 a        |                                               | {}

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

 

 

highgo=#alter user a superuser ;

ALTERROLE

highgo=#\du

                             List of roles

 Role name |                   Attributes                   | Member of

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

 a         | Superuser                                      | {}

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

 

highgo=#\c highgo a

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

highgo=#create database a_tb;

CREATEDATABASE

highgo=#\l

                              List of databases

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

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

 a_t      | a      | UTF8     | zh_CN.utf8 | zh_CN.utf8 |

 a_tb     | a      | UTF8     | zh_CN.utf8 | zh_CN.utf8 |

 highgo   | highgo | UTF8     | zh_CN.utf8 |zh_CN.utf8 | =Tc/highgo       +

           |        |          |            |            | highgo=CTc/highgo

 highgo_t | 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

(6rows)