数据库

PostgreSQL权限修改 : ALTER DEFAULT PRIVILEGES

关于权限的问题，曾经有个问题一直困扰着，由于关系不是很大一直没有深究，今天偶然的一次机会
看手册时看到 "ALTER DEFAULT PRIVILEGES" 命令，立即解决了困扰已久的问题。

--1 问题描述

        在生产数据库中通常不允许开发人员使用生产帐号连接数据库，那么有时候开发人员又需要查询数据库
权限，用于日常问题排查，通常的做法是创建一个查询帐号，并把数据库中指定表的查询权限开放给给查询
帐号，这也是目前我们生产系统维护过程中使用较多的方法，但是如果开发人员希望对整库所有表的查询权
限均开放，而且以后生产库中新建的表也需要默认的开放此帐号的查询权限，这时如何处理？这时，今天学
习到的命令即可解决这个问题，命令为 "ALTER DEFAULT PRIVILEGES"，下面简单测试下。

--2 环境信息
PostgreSQL 版本：  9.1.0
数据库名:          mydb
数据库属主:        mydb
查询帐号           mydb_select

  备注：假设数据库 mydb 库中用户仅创建 mydb  schema。

--3 创建只读帐号

[postgres@pgb ~]$ psql psql (9.1.0) Type "help" for help. postgres=# create role  mydb_select LOGIN  NOSUPERUSER NOCREATEDB NOCREATEROLE  encrypted password 'mydb_select'; CREATE ROLE

  备注：上面创建帐号 mydb_select。

--4 给帐号 mydb_select 赋权 

postgres=# c mydb mydb You are now connected to database "mydb" as user "mydb". mydb=> grant connect on database mydb to mydb_select; GRANT mydb=> grant usage on schema mydb to mydb_select; GRANT mydb=> grant select on all tables in schema mydb to mydb_select; GRANT

   备注：上面命令给帐号 mydb_select 开通了数据库 mydb 的只读权限，可以访问mydb 库中 mydb schema 下的所有表。
 

--5 权限测试

mydb=> c mydb mydb_select; You are now connected to database "mydb" as user "mydb_select". mydb=> select * from mydb.test limit 1;    id   | name --------+------  831681 | AAA (1 row)

      备注： mydb_select 用户查询 mydb.test 表成功，没有问题。
--6 在 mydb 库中创建一张新表

mydb=>  create table test_33 as select * From test limit 10 ; SELECT 10 mydb=> dp test_33 Access privileges  Schema |  Name   | Type  | Access privileges | Column access privileges --------+---------+-------+-------------------+--------------------------  mydb   | test_33 | table | mydb=arwdDxt/mydb+| (1 row) mydb=> c mydb mydb_select; You are now connected to database "mydb" as user "mydb_select". mydb=> select * from mydb.test_33; ERROR:  permission denied for relation test_33

      备注：此时用户 mydb_select 没有权限查询，因为 mydb.test_33 是赋权后新建的表。
--7 使用 "ALTER DEFAULT PRIVILEGES"  赋权

mydb=> ALTER DEFAULT PRIVILEGES IN SCHEMA mydb grant select on tables to mydb_select; ALTER DEFAULT PRIVILEGES

  备注：我们更改用户 mydb_select 的默认权限，使得在数据库 mydb中 mydb 模式下创建的新表，
默认给用户 mydb_select 开通查询权限，这个命令仅对未来创建的数据库对像生效，对
当前存在的数据库对像不起作用， 这个命令的详细信息，参考本文末尾的附一。

--8 权限测试

mydb=>  create table test_34 as select * From test limit 10 ; SELECT 10 mydb=> dp test_34 Access privileges  Schema |  Name   | Type  | Access privileges  | Column access privileges --------+---------+-------+--------------------+--------------------------  mydb   | test_34 | table | mydb=arwdDxt/mydb +| |         |       | mydb_select=r/mydb | (1 row) mydb=> c mydb mydb_select You are now connected to database "mydb" as user "mydb_select". mydb=> select * From mydb.test_34 limit 1;    id   | name --------+------  831681 | AAA (1 row) mydb=> select * From mydb.test_33 limit 1; ERROR:  permission denied for relation test_33

  备注：这时用户 mydb_select 果然拥有之后创建表(mydb.test_34) 的查询权限了，而对之前的表
 mydb.test_33 依然没有查询权限，这也正好验证子这前关于这个命令的说明，即仅对未来
 创建的数据库对像生效。

附一 ALTER DEFAULT PRIVILEGE 命令

Name

ALTER DEFAULT PRIVILEGES -- define default access privileges

Synopsis

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 ]

GRANT { { USAGE | SELECT | UPDATE }
    [,...] | ALL [ PRIVILEGES ] }
    ON SEQUENCES
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON FUNCTIONS
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [,...] | ALL [ PRIVILEGES ] }
    ON TABLES
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { { USAGE | SELECT | UPDATE }
    [,...] | ALL [ PRIVILEGES ] }
    ON SEQUENCES
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { EXECUTE | ALL [ PRIVILEGES ] }
    ON FUNCTIONS
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

Description
     ALTER DEFAULT PRIVILEGES allows you to set the privileges that will be applied to objects created in
     the future. (It does not affect privileges assigned to already-existing objects.) Currently, only the
     privileges for tables (including views), sequences, and functions can be altered.

PostgreSQL权限修改 : ALTER DEFAULT PRIVILEGES