首页 > 数据库 >揭开 PostgreSQL 默认权限的神秘面纱

揭开 PostgreSQL 默认权限的神秘面纱

时间:2024-08-13 13:27:27浏览次数:15  
标签:obj postgres creator 默认 role user test PostgreSQL 面纱

alter default privileges 命令允许我们设置将应用于将来创建的对象的权限。需要注意的是,这不会影响分配给现有对象的权限;默认权限可以全局设置在当前数据库上,或者指定的 schema 上。

 

数据库使用者对默认权限的行为有很多误解,我经常听到一些抱怨,说即使为特定 schema 分配了默认权限,用户也没有访问权限。

 

了解默认权限

创建对象时,会给它分配一个所有者。最初,所有者是执行创建对象语句的角色(后期可以更改)。对于大多数类型的对象,只有所有者(或超级用户)拥有对象的所有权。不过,可以通过 alter default privileges 语句更改默认权限来改变这种行为。这样,我们就可以在任何类型的对象上为任何预期用户分配任何默认权限。目前,只有 schema 、表(包括视图和 foreign 表)、序列、函数和类型(包括域)的权限可以使用 alter default privileges 进行更改。

 

示例:

postgres=# select current_user;
 current_user 
--------------
 postgres
(1 row)
postgres=# create schema test;
CREATE SCHEMA
postgres=# grant usage on schema test to obj_user;
GRANT
postgres=# create table test.city(id int, name varchar);
CREATE TABLE
postgres=# insert into test.city values(1, 'didu');
INSERT 0 1
postgres=# 

现在测试角色 obj_user 的权限:

postgres=# set role obj_user;
SET
postgres=> select * from test.city;
ERROR:  permission denied for table city
postgres=> 

需要显式授权:

postgres=> set role postgres;
SET
postgres=# grant select on table test.city to obj_user;
GRANT
postgres=# 

postgres=# set role obj_user;
SET
postgres=> select * from test.city;
 id | name 
----+------
  1 | didu
(1 row)

postgres=> 

这里,用户 obj_user 缺省时没有 select 权限,必须显式授予。如果总是碰到这种场景,就该考虑自动授予 select 权限了。这可以通过 alter default privileges 实现。

 

设置默认权限

postgres=# select current_user;
 current_user 
--------------
 postgres
(1 row)

postgres=# alter default privileges in schema test grant select on tables to obj_user;
ALTER DEFAULT PRIVILEGES
postgres=# create table test.state(id int, name varchar);
CREATE TABLE
postgres=# insert into test.state values(1, 'myabc');
INSERT 0 1
postgres=# 
postgres=# set role obj_user;
SET
postgres=> select * from test.state;
 id | name  
----+-------
  1 | myabc
(1 row)

postgres=> 

可以查询了。

 

现在,考虑不用 postgres 用户创建一个对象。用另一个用户 obj_creator 创建一个表,并测试 obj_user 的权限:

postgres=> set role obj_creator;
set
postgres=> create table test.nationality(id int, name varchar);
create table
postgres=> insert into test.nationality values(1,'USA');
insert 0 1

postgres=> set role obj_user;
set
postgres=> select * from test.nationality;
error:  permission denied for table nationality

这里就执行失败了。但这是 PostgreSQL 默认权限的实际行为。许多用户认为,既然我们授予了默认权限,那么无论对象的创建者是谁,它都会正常工作。

 

 

不生效的原因

用户 obj_user 并没有自动获得新建的表的权限,因为默认权限只有在对象创建者与 alter default privileges 语句的执行者(默认情况下为当前用户,在本例中为 postgres)相同时才会起作用。

 

破解迷思

为了解决这个问题,执行下面的语句,假设 obj_creator 将是test模式中对象的创建者:

postgres=# SELECT current_user;
 current_user 
--------------
 postgres
(1 row)

postgres=# ALTER DEFAULT PRIVILEGES IN SCHEMA test FOR ROLE obj_creator GRANT SELECT ON TABLES to obj_user;
ALTER DEFAULT PRIVILEGES
postgres=# set role obj_creator;
SET
postgres=# create table test.citizen(id int, name varchar);
CREATE TABLE
postgres=# insert into test.citizen values(1, 'abc');
INSERT 0 1
postgres=# set role obj_user;
SET
postgres=> select * from test.citizen;
 id | name  
----+-------
  1 | David
(1 row)

postgres=> 

生效了。通过在 alter default privieges 语句中指定了对象的创建者,我们可以保证obj_user自动获取到obj_creator用户在test schema中创建的对象上的 select 权限。

 

更多示例语句

授权:

alter default privileges in schema test for role obj_creator grant select, insert, update, delete on tables to obj_user;
alter default privileges in schema test for role obj_creator grant usage on types to obj_user;
alter default privileges in schema test for role obj_creator grant execute on functions to obj_user;
alter default privileges in schema test for role obj_creator grant select on sequences to obj_user;
alter default privileges for role obj_creator grant usage on schemas to obj_user;

取消授权:

alter default privileges in schema test for role obj_creator revoke insert,update,delete on tables from obj_user;
alter default privileges in schema test for role obj_creator revoke usage on types from obj_user;
alter default privileges in schema test for role obj_creator revoke execute on functions from obj_user;
alter default privileges in schema test for role obj_creator revoke select on sequences from obj_user;
alter default privileges for role obj_creator revoke usage on schemas from obj_user;

列出默认权限

可以使用\ddp密令查看列出默认权限

postgres-> \ddp
               Default access privileges
    Owner    | Schema | Type  |   Access privileges    
-------------+--------+-------+------------------------
 obj_creator | test   | table | obj_user=r/obj_creator
 postgres    | test   | table | obj_user=r/postgres
(2 rows)

postgres-> 

也可以使用以下查询:

postgres=> SELECT
defaclnamespace::regnamespace AS schema,
CASE defaclobjtype WHEN 'r' THEN 'table' WHEN 'S' THEN 'sequence' WHEN 'T' THEN 'type' WHEN 'n' THEN 'schema' END AS obj_type,
(aclexplode(defaclacl)).privilege_type AS privilege_type,
(aclexplode(defaclacl)).grantor::regrole AS for_role,
(aclexplode(defaclacl)).grantee::regrole AS to_user
FROM pg_default_acl;
 schema | obj_type | privilege_type |  for_role   | to_user  
--------+----------+----------------+-------------+----------
 test   | table    | SELECT         | postgres    | obj_user
 test   | table    | SELECT         | obj_creator | obj_user
(2 rows)

postgres=> 

使用 alter default privileges,我们可以自动简化权限管理,确保正确的用户可以访问新对象,而无需人工干预。

标签:obj,postgres,creator,默认,role,user,test,PostgreSQL,面纱
From: https://www.cnblogs.com/abclife/p/18354425

相关文章

  • PostgreSQL Linux下安装与配置
    1.PostgreSQL安装Linux版本这里使用的是7.x版本,最好是7.6或者7.8。下载地址:https://www.postgresql.org/download/选择好PGSQL的版本,Linux的发行版本直接执行命令即可:#下载PGSQL的rpm包sudoyuminstall-yhttps://download.postgresql.org/pub/repos/yum/reporp......
  • js默认行为以及阻止默认行为的方法
      js默认行为以及阻止默认行为的方法咔咔哎嘛2023-02-04228阅读1分钟 关注 默认行为 代码解读复制代码不用我们注册,但是自己存在的事情比如: css代码解读复制代码鼠标右键点击,会弹出一个菜单点击a标签后,自己......
  • Ubuntu22 安装和恢复 Hexo,解决nodejs默认版本较低的问题。
    因为Ubuntu自带的nodejs版本和我之前搭建好的hexo环境不符合,从github上面拉取之后不能够直接使用hexo配置,要重新下载和配置nodejs的版本。安装nvm安装步骤sudoaptinstallcurlcurlhttps://raw.githubusercontent.com/creationix/nvm/master/install.sh|bash出现问题......
  • VS Code 修改默认插件安装位置
    先将扩展插件extensions从默认安装路径剪切到你真正想要存放的位置将C:\Users\(用户名)\.vscode\extensions\剪切到存放的位置,此处以D:\extensions为例✨注意不要与VSCode安装目录放在一起,vscode更新时会删除的使用mklink命令创建快捷方式,使用\D参数创建软链接Micr......
  • 【PostgreSQL教程】PostgreSQL GROUP BY 语句
    博主介绍:✌全网粉丝20W+,CSDN博客专家、Java领域优质创作者,掘金/华为云/阿里云/InfoQ等平台优质作者、专注于Java技术领域✌技术范围:SpringBoot、SpringCloud、Vue、SSM、HTML、Nodejs、Python、MySQL、PostgreSQL、大数据、物联网、机器学习等设计与开发。感兴趣的可以先......
  • 改变IntelliJ IDEA 中的system和config/plugins的默认C盘的路径
    1,问题,在为idea在线安装插件时,如JProfiler,会默认安装到C盘,而本人则是希望安装到软件所在的D盘目录下,那么如何修改呢:C:\Users\xxx.IntelliJIdea\config\plugins2,修改方法:打开IntelliJIDEA的安装目录,如本人的为D:\JetBrains\IntelliJIDEA2018.2然后在bin目录下找到idea.pr......
  • 安防监控/视频汇聚平台EasyCVR如何配置,实现默认获取设备的子码流?
    安防视频监控/视频集中存储/云存储/磁盘阵列EasyCVR平台基于云边端一体化架构,兼容性强、支持多协议接入,包括国标GB/T28181协议、部标JT808、GA/T1400协议、RTMP、RTSP/Onvif协议、海康Ehome、海康SDK、大华SDK、华为SDK、宇视SDK、乐橙SDK、萤石云SDK等。有用户反馈,选用HIKSDK......
  • 修改『Visual Studio Code(VS Code)』插件默认安装路径的方法
    前言作者希望将『VisualStudioCode(以下简称为“VSCode”)』的插件安装在数据盘(D盘),用于统一管理,因此需要修改VSCode插件安装路径。VSCode插件默认的安装位置为:C:\Users\{个人用户名}\.vscode\extensions。方法一:修改快捷方式目标路径(★★☆)1.确保『code』快捷命令的可用......
  • Nginx负载均衡的max_fails和fail_timeout的默认配置问题
    今天发现一个奇怪的现象,前端请求后端服务多次后会超时一次,经过多次验证确定是大概10s左右就会超时一次,检查后端服务,发现其中一个节点已经夯死。但是我们的nginx负载均衡策略是轮询机制,按照配置来看应该是每隔一次请求轮询到失败的节点时超时一次才对。为什么是每隔10s超时一次呢?......
  • 032.Vue3入门,插槽Slot的作用域和默认内容
    1、App.vue代码如下:<template><div><h3>插槽学习</h3><!--插槽1--><Slot001><p>{{msg}}</p></Slot001><!--插槽2--><Slot001><!--<p>{{m......