I have DB "test" in PostgreSql. I want to write sql to get owner my database.
Add a comment
|
7 Answers
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;
4 Comments
ManoDestra
@DanielMöller You would know the DB name. It's just the owner of the given DB that you're trying to ascertain. If you don't know the DB name, then just omit that WHERE clause.
ManoDestra
I meant to say "name" of the given DB above.
Eerik Sven Puudist
You can use
current_database() if you need to dynamically find out which database you are connected to and which owner you are interested in.
Code-Apprentice
You can remove the
where clause to get owners of all databases in the current cluster.If you use the psql command-line tool, you can simply use \l
3 Comments
Flimm
This returns a list of databases, but the owners of the databases.
gilad905
@Flimm I didn't understand your comment - this returns a list of all existing DBs on the server (that current user has permission to view) with the owner of each DB.
Flimm
You're right. I was using
pgcli which for some reason gives a different result.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());
2 Comments
JC Boggio
This won't work if database is owned by a role (a group of users) and not a user.
ExcessOperatorHeadspace
current_catalog is a synonym for current_database()
SELECT u.usename FROM pg_database d JOIN pg_user u ON (d.datdba = u.usesysid) WHERE d.datname = current_catalog;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' ;
Comments
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_rolesis a publicly readable view onpg_authidthat blanks out the password field.
2 Comments
Steven Yong
When running this, it gave permission denied for relation pg_authid. What's required?
jlandercy
@StevenYong, Thank you for pointing this out, I have updated my answer. Reading the doc, it is sufficient to replace
pg_authid by pg_roles. Have a nice day.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/
Comments
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
