如何查询Postgres数据字典以查找特定用户拥有的所有权限。
我一直在寻找解决方案,但是找不到任何内容。谢谢,祝愉快的一天。
我一直在寻找解决方案,但是找不到任何内容。谢谢,祝愉快的一天。
SELECT *
FROM information_schema.role_table_grants
WHERE grantee = 'YOUR_USER';
所有权:
SELECT *
FROM pg_tables
WHERE tableowner = 'YOUR_USER';
架构权限:
SELECT r.usename AS grantor,
e.usename AS grantee,
nspname,
privilege_type,
is_grantable
FROM pg_namespace
JOIN LATERAL (SELECT *
FROM aclexplode(nspacl) AS x) a
ON true
JOIN pg_user e
ON a.grantee = e.usesysid
JOIN pg_user r
ON a.grantor = r.usesysid
WHERE e.usename = 'YOUR_USER';
这是我认为最好的方法,简短而干净。
\du
列出所有用户账户和角色,\du+
是扩展版本,显示更多信息。
# \du
List of roles
Role name | Attributes | Member of
--------------------+------------------------------------------------------------+-----------
padmin | Superuser, Create role, Create DB | {}
test | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
root | Superuser, Create role, Create DB | {}
# \du+
List of roles
Role name | Attributes | Member of | Description
--------------------+------------------------------------------------------------+-----------+-------------
padmin | Superuser, Create role, Create DB | {} |
test | | {} |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
root | Superuser, Create role, Create DB | {} |
这个命令对我很有帮助:
\l
这是我如何使用它的方法:
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------------------------+-----------------+----------+---------+-------+-------------------------------------
mydb1 | postgres | UTF8 | en_NG | en_NG | =Tc/postgres +
| | | | | postgres=CTc/postgres +
| | | | | myuser=CTc/postgres
mydb2 | postgres | UTF8 | en_NG | en_NG | =Tc/postgres +
| | | | | postgres=CTc/postgres +
| | | | | my_user=CTc/postgres
就这些了。
希望对您有所帮助。
您也可以使用这个方法来查看用户是否拥有除了SELECT之外的其他权限
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'username' AND with_hierarchy = 'YES'
... FROM pg_namespace, aclexplode(nspacl) AS a ...
即可。 - JeffJOIN LATERAL
在 Postgres 9.3 版本中可用,因此上面的schema permissions
查询在 AWS Redshift 中不起作用。 - Gergely M