PostgreSQL权限修改 : ALTER DEFAULT PRIVILEGES

最新推荐文章于 2024-07-25 09:07:53 发布

黄杏波

最新推荐文章于 2024-07-25 09:07:53 发布

阅读量6.7k

点赞数 3

分类专栏： postgresql 文章标签： postgresql 修改权限

postgresql 专栏收录该内容

5 篇文章 1 订阅

订阅专栏

关于权限的问题，曾经有个问题一直困扰着，由于关系不是很大一直没有深究，今天偶然的一次机会
看手册时看到 "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

(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

(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.