在 PostgreSql 中获取数据库所有者的名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17165254/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get DB owner's name in PostgreSql
提问by mindia
I have DB "test" in PostgreSql. I want to write sql to get owner my database.
我在 PostgreSql 中有数据库“测试”。我想编写 sql 来获取我的数据库的所有者。
回答by DrColossos
You can find such things in the system catalog
你可以在系统目录中找到这样的东西
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner"
FROM pg_catalog.pg_database d
WHERE d.datname = 'your_name'
ORDER BY 1;
回答by gilad mayani
回答by AndreaBoc
You can use the combination of pg_database, pg_userssystem tables and current_database()function in this way:
您可以通过这种方式使用pg_database,pg_users系统表和current_database()函数的组合：
SELECT u.usename
FROM pg_database d
JOIN pg_user u ON (d.datdba = u.usesysid)
WHERE d.datname = (SELECT current_database());
回答by jlandercy
This work with database owned by group role:
这适用于组角色拥有的数据库：
SELECT
U.rolname
,D.datname
FROM
pg_roles AS U JOIN pg_database AS D ON (D.datdba = U.oid)
WHERE
D.datname = current_database();
Using pg_authid(as I did in my previous version) instead of pg_rolesis limited to SuperUser because it holds password (see documentation):
使用pg_authid（就像我在以前的版本中所做的那样）而不是pg_roles仅限于 SuperUser，因为它保存密码（请参阅文档）：
Since this catalog contains passwords, it must not be publicly readable.
pg_rolesis a publicly readable view onpg_authidthat blanks out the password field.
由于此目录包含密码，因此不得公开读取。
pg_roles是一个公开可读的视图，pg_authid可以将密码字段留空。
回答by sushmitha shenoy
The follwing query displays info for all tables in the public schema:
以下查询显示公共架构中所有表的信息：
select t.table_name, t.table_type, c.relname, c.relowner, u.usename
from information_schema.tables t
join pg_catalog.pg_class c on (t.table_name = c.relname)
join pg_catalog.pg_user u on (c.relowner = u.usesysid)
where t.table_schema='public';
source :http://cully.biz/2013/12/11/postgresql-getting-the-owner-of-tables/
来源：http: //cully.biz/2013/12/11/postgresql-getting-the-owner-of-tables/
回答by mattpr
Remember in SQL including postgres that you have a heirarchy within a given sql server instance: catalog/db > schema > tables
请记住，在包含 postgres 的 SQL 中，您在给定的 sql server 实例中有一个层次结构： catalog/db > schema > tables
When looking for perms/metadata for withina catalog you want to look at information_schema
当寻找烫发/元数据中的一个目录，你想看看information_schema
Example:information_schema.role_table_grantsfor table perms
示例：information_schema.role_table_grants对于表烫发
Example:information_schema.role_usage_grantsfor SEQUENCE/schema perms
示例：information_schema.role_usage_grants对于 SEQUENCE/schema perms
https://www.postgresql.org/docs/current/information-schema.html
https://www.postgresql.org/docs/current/information-schema.html
For catalog/db-level config/meta, you need to look another level up in pg_catalog.
对于目录/数据库级配置/元，您需要在pg_catalog.
https://www.postgresql.org/docs/current/catalogs.html
https://www.postgresql.org/docs/current/catalogs.html
Example:
例子：
SELECT dbs.datname, roles.rolname
FROM pg_catalog.pg_database dbs, pg_catalog.pg_roles roles
WHERE dbs.datdba = roles.oid;
pg_catalog.pg_database.datdbahas ID of owner role.
pg_catalog.pg_database.datdba具有所有者角色的 ID。
pg_catalog.pg_roles.oidhas ID of owner role (join)
pg_catalog.pg_roles.oid拥有所有者角色的 ID（加入）
pg_catalog.pg_roles.rolnamehas name/string of owner role
pg_catalog.pg_roles.rolname具有所有者角色的名称/字符串
