database►Postgresql►postgresql 数据库管理

postgresql 数据库管理

postgrsql 数据库管理

  1. postgrsql登录

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    // postgresql 登录和退出
    -bash-4.2$ psql -U postgres -d postgres
    postgres=# \q

    注意: 如果登录PG时,只指定了用户"psql -U postgres",未指定数据库"-d postgres",默认数据库会跟用户名一致(也就是说默认连接的数据库名='postgres')

    //切换用户进行连接
    postgres=# \c - role

    //切换到数据库
    postgres=# \c database

  2. 连接管理

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    //查看活动连接表,以及其进程ID
    postgres=# select * from pg_stat_activity;
    或者:
    postgres=# \x
    postgres=# select * from pg_stat_activity;
    postgres=# \x

    //取消连接上的活动查询(该操作不会终止连接本身(不会中断session,事务回滚))
    postgres=# select pg_cancel_backend(12950);

    //终止连接(中断session)
    postgres=# select pg_terminate_backend(12950);

    //批量删除
    postgres=# select pg_terminate_backend(pid) from pg_stat_activity where usename = 'some_role';

  3. 创建可登录角色

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    角色相当于岗位,用户就是具体的人.
    Postgresql中没有区分用户和角色,"create user"为"create role"的别名,这两个命令几乎是完全相同的,唯一的区别是"create user"命令创建的用户默认带有LOGIN属性,而"create role"命令创建的用户默认不带LOGIN属性
    我们建议使用"create role"来创建用户!

    //创建角色
    postgres=# create role penn;

    //删除角色
    postgres=# drop role penn;

    //查看角色
    postgres=# \du
    或者
    postgres=# select * from pg_roles;

  4. 为角色授权

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    //为角色授和取消LOGIN权限
    postgres=# alter role penn login;
    postgres=# alter role penn nologin;

    //为角色授权和取消SUPERUSER权限
    postgres=# alter role penn superuser;
    postgres=# alter role penn nosuperuser;

    //为角色授权和取消CREATEDB
    postgres=# alter role penn createdb;
    postgres=# alter role penn nocreatedb;

    //为角色授权和取消CREATEROLE
    postgres=# alter role penn createrole;
    postgres=# alter role penn nocreaterole;

    //为角色授权和取消REPLICATION
    postgres=# alter role penn replication;
    postgres=# alter role penn noreplication;

  5. 创建角色并授权

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    //创建角色并授权LOGIN
    postgres=# create role penn login;

    //创建角色并授权SUPERUSER
    postgres=# create role penn superuser;

    //创建角色并授权CREATEDB
    postgres=# create role penn createdb;

    //创建角色并授权CREATEROLE
    postgres=# create role penn createrole;

    //创建角色并授权REPLICATION
    postgres=# create role penn replication login;

  6. 创建角色,授权,并为角色设置密码

    1
    2
    3
    4
    5
    6
    //创建角色并授权LOGIN,并设置密码
    postgres=# create role penn login password '123456';
    或者:
    postgres=# create role penn;
    postgres=# alter role penn login;
    postgres=# alter role penn password '123456'

  7. 创建组角色

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    //创建组角色
    postgres=# create role group_1 inherit;
    inherit 表示组角色group_1内的任何成员都会自动继承其除"超级用户权限"之外的所有权限
    noinherit 表示禁止组角色group_1将其权限授予其成员角色

    //授权组角色
    postgres=# alter role group_1 login;

    //将组角色权限授权给组成员(将用户加入到组角色中,继承权限)
    postgres=# grant group_1 to penn;

    //撤销组角色权限
    postgres=# revoke group_1 from penn;

    //一个角色成员使用组角色的权限
    两种方式:
    方式一: 每个组成员都可以用"SET ROLE"命令将自己临时"变成"该组成员,此后再创建的任何对象的所有者将属于该组,而不是原有的登录用户
    方式二: 拥有INHERIT属性的角色成员自动继承它们所属角色的权限

    postgres=# create role penn inherit;
    postgres=# create role haha noinherit;
    postgres=# create role xixi noinherit;
    postgres=# grant haha to penn;
    postgres=# grant xixi to haha;

    用用角色penn与数据库建立连接,那么该会话同时拥有角色penn和角色haha的权限(inherit权限继承),然而赋予角色xixi的权限在会话中不可用,因为角色penn只是角色xixi的一个间接成员,它是通过haha角色间接传递过来的,这样xixi权限无法被penn用户继承

    如果想让penn继承xixi权限,我们在会话中这么操作:
    set role haha;
    set role xixi;

    如果想会话恢复原有角色权限,我们在会话中这么操作:
    set role penn;
    or:
    set role none;
    or:
    reset role;