RHEL6_64上PostgreSQL 9.6.5无密码表访问授权方案咨询

针对你的场景，我整理了一套可行的解决方案，刚好适配你没有超级用户但有createrole权限的约束，结合PostgreSQL的认证机制和Perl脚本的身份验证来实现需求：

1. 数据库权限基础配置（用你的createrole权限即可完成） #

首先咱们先把数据库层面的权限框架搭起来：

• 让所有用户都能读取approvals表：直接给public角色赋予SELECT权限，这样所有数据库用户默认都能执行读操作

  GRANT SELECT ON approvals TO public;
  

• 创建一个专门的写操作角色，集中管理INSERT/UPDATE/DELETE权限：

  CREATE ROLE approval_writer NOLOGIN;
  GRANT INSERT, UPDATE, DELETE ON approvals TO approval_writer;
  

• 给granters表中记录的每个linux_uid创建对应的数据库登录角色，并把写权限角色赋予他们：

  -- 示例：假设granters里有用户alice和bob
  CREATE ROLE alice LOGIN;
  GRANT approval_writer TO alice;
  
  CREATE ROLE bob LOGIN;
  GRANT approval_writer TO bob;
  

  如果granters表会动态更新，后续可以写个简单的Perl脚本定时同步角色权限，不需要超级用户权限。

2. 配置PostgreSQL Peer认证（需要管理员协助，这是无密码登录的关键） #

因为你没有超级用户权限，得联系数据库管理员修改pg_hba.conf文件，添加一行本地连接的Peer认证规则：

local   你的数据库名   all             peer

Peer认证的逻辑是：本地Linux用户登录PostgreSQL时，会自动匹配同名的数据库角色，不需要输入密码，完美符合你“无需输入密码”的需求。如果管理员不愿意修改全局配置，只针对你的数据库配置这条规则就行。

3. Perl/DBI脚本实现身份验证与操作控制 #

脚本的核心逻辑是：用getlogin()获取当前Linux用户身份，通过Peer认证连接数据库，再验证该用户是否在granters表中，最后根据权限执行对应操作。示例脚本如下：

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

# 获取当前Linux登录用户名（对应granters表的linux_uid）
my $current_user = getlogin() or die "无法获取当前登录用户，请检查系统环境";

# 用Peer认证连接数据库（无需密码，直接用当前Linux用户名作为数据库角色）
my $dbh = DBI->connect(
    "dbi:Pg:dbname=你的数据库名;host=/var/run/postgresql", # 本地socket路径根据你的PostgreSQL配置调整
    $current_user,
    "", # 密码留空，Peer认证不需要
    { RaiseError => 1, AutoCommit => 1 }
) or die "数据库连接失败: " . DBI->errstr;

# 验证当前用户是否在granters表中（双重保障，即使数据库角色权限配置有误也能拦截）
my $has_write_perm = 0;
my $check_sth = $dbh->prepare("SELECT 1 FROM granters WHERE linux_uid = ?");
$check_sth->execute($current_user);
$has_write_perm = 1 if $check_sth->fetchrow_array;
$check_sth->finish;

# 封装操作函数
sub query_approvals {
    my $sth = $dbh->prepare("SELECT approval_id FROM approvals");
    $sth->execute;
    print "查询结果:\n";
    while (my ($id) = $sth->fetchrow_array) {
        print "- $id\n";
    }
    $sth->finish;
}

sub add_approval {
    die "错误：你没有执行INSERT操作的权限\n" unless $has_write_perm;
    my ($approval_id) = @_;
    my $sth = $dbh->prepare("INSERT INTO approvals (approval_id) VALUES (?)");
    $sth->execute($approval_id);
    print "成功插入审批记录: $approval_id\n";
    $sth->finish;
}

# 示例执行逻辑（可以根据脚本传入的参数来决定执行哪种操作）
query_approvals();

# 如果是授权用户，执行插入测试（实际场景可以改成接收命令行参数）
if ($has_write_perm) {
    add_approval("APP_".time());
}

$dbh->disconnect;

4. 适配你的约束条件的说明 #

• 所有操作都不需要超级用户权限：创建角色、赋权的操作你用createrole权限就能完成，只有修改pg_hba.conf需要管理员协助，这是PostgreSQL认证机制的必要步骤。
• 无密码登录：Peer认证直接绑定Linux系统用户身份，不需要输入密码，完全符合需求。
• 权限控制双重保障：数据库层面只有授权角色能执行写操作，脚本层面再通过granters表验证，避免权限配置失误导致的越权操作。

内容的提问来源于stack exchange，提问作者daveg