Get DB owner's name in PostgreSql
PostgresqlPostgresql Problem Overview
I have DB "test" in PostgreSql. I want to write sql to get owner my database.
Postgresql Solutions
Solution 1 - Postgresql
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 = 'database_name'
ORDER BY 1;
Solution 2 - Postgresql
If you use the psql command-line tool, you can simply use \l
Solution 3 - Postgresql
You can use the combination of pg_database
, pg_users
system tables and current_database()
function in this way:
SELECT u.usename
FROM pg_database d
JOIN pg_user u ON (d.datdba = u.usesysid)
WHERE d.datname = (SELECT current_database());
Solution 4 - Postgresql
can just cast the role OID with magic ::regrole to give the role name of owner:
SELECT datdba::regrole FROM pg_database WHERE datname = 'test' ;
Solution 5 - Postgresql
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_roles
is limited to SuperUser because it holds password (see documentation):
> Since this catalog contains passwords, it must not be publicly
> readable. pg_roles
is a publicly readable view on pg_authid
that
> blanks out the password field.
Solution 6 - Postgresql
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/
Solution 7 - Postgresql
Remember in SQL including postgres that you have a heirarchy within a given sql server instance: catalog/db > schema > tables
When looking for perms/metadata for within a catalog you want to look at information_schema
Example: information_schema.role_table_grants
for table perms
Example: information_schema.role_usage_grants
for SEQUENCE/schema perms
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
.
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.datdba
has ID of owner role.
pg_catalog.pg_roles.oid
has ID of owner role (join)
pg_catalog.pg_roles.rolname
has name/string of owner role