如何在PostgreSQL中列出角色名称拥有的数据库 [英] How to list databases owned by rolename in postgresql

问题描述

我在bash中尝试过的代码由root执行.

The code i tried in bash executed by root.

#!/bin/bash
su - postgres <<EOF1 
F="$(psql -d postgres --tuples-only -P format=unaligned -c "SELECT datname FROM pg_database JOIN pg_authid ON pg_database.datdba = pg_authid.oid WHERE rolname = 'username'")"
EOF1 
echo $F

输出为错误:关系pg_authid的权限被拒绝

It gives output as ERROR: permission denied for relation pg_authid

但是当我尝试

su - postgres <<EOF1 
psql -d postgres --tuples-only -P format=unaligned -c "SELECT datname FROM pg_database JOIN pg_authid ON pg_database.datdba = pg_authid.oid WHERE rolname = 'username'"
EOF1 

这将打印该用户名的所有数据库.为什么这样?

This prints all db of that username. Why so?

我需要将输出存储到bash变量中以进行进一步处理.

I need to store the ouput to a bash variable for further processing.

是否有任何错误或其他尝试方法.

Is there any mistake or anyother way to try this out..

谢谢.

推荐答案

内部 $(...)表达式在 su 部分之前执行，因此它将不能以 postgres 的身份运行，而是以当前用户的身份运行.最好写成这样:

The inner $(...) expression gets executed before the su part, so it will not be run as postgres but as the current user. This is probably better written as:

command="psql -d postgres --tuples-only -P format=unaligned -c \"SELECT datname FROM pg_database JOIN pg_authid ON pg_database.datdba = pg_authid.oid WHERE rolname = 'username'\""
F=$( su - postgres -c "$command" )

您可以将所有内容放在一起，

You could put it all together, however:

F=$( su - postgres -c "psql -d postgres --tuples-only -P format=unaligned -c \"SELECT datname FROM pg_database JOIN pg_authid ON pg_database.datdba = pg_authid.oid WHERE rolname = 'username'\"" )

我还要注意，第一个对您失败的示例可能不会将 F 设置为您可以在 su 之外读取的任何内容.但是，我和Ubuntu假定其他现代Linux系统不允许您以这种方式使用 su .您应该使用例如 sudo -l -u postrges 并适当地配置/etc/sudoers ，以使人们有权运行 psql 或其他.作为 postgres 用户.

I should also note that the first example that failed for you probably would not set F to anything you could read outside of the su. However, Ubuntu and I presume other modern Linux systems do not allow you to use su in this way. You should use, e.g., sudo -l -u postrges and configure /etc/sudoers appropriately for people to have permission to run psql or whatnot as the postgres user.

这篇关于如何在PostgreSQL中列出角色名称拥有的数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！