PostgreSQL 学习笔记015 —— PostgreSQL 常用命令（5）

查询PostgreSQL中的Schema

postgres=# select schema_name from information_schema.schemata;
    schema_name
--------------------
 pg_toast
 pg_catalog
 information_schema
 public
(4 rows)

postgres=# select nspname from pg_catalog.pg_namespace;
      nspname
--------------------
 pg_toast
 pg_catalog
 information_schema
 public
(4 rows)

查看当前数据库所有的用户以及对应的权限

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 user1     |                                                            | {}

查看schema的相关信息

postgres=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)

查询当前的模式搜索路径

postgres=# show search_path ;
   search_path
-----------------
 "$user", public
(1 row)

查看哪些用户具有权限访问表

-- \dp或者\z

postgres=# \z test1
                             Access privileges
 Schema | Name  | Type  | Access privileges | Column privileges | Policies
--------+-------+-------+-------------------+-------------------+----------
 public | test1 | table |                   |                   |
(1 row)

# 赋给 user1 用户对表 test1 的select权限。

postgres=# grant select on test1 to user1;
GRANT
postgres=# \z test1
                                 Access privileges
 Schema | Name  | Type  |     Access privileges     | Column privileges | Policies
--------+-------+-------+---------------------------+-------------------+----------
 public | test1 | table | postgres=arwdDxt/postgres+|                   |
        |       |       | user1=r/postgres          |                   |
(1 row)

获取当前db中所有表的信息

postgres=# select * from pg_tables;
     schemaname     |        tablename        | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
--------------------+-------------------------+------------+------------+------------+----------+-------------+-------------
 public             | test1                   | postgres   |            | f          | f        | f           | f
 pg_catalog         | pg_statistic            | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_type                 | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_foreign_table        | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_authid               | postgres   | pg_global  | t          | f        | f           | f
 pg_catalog         | pg_statistic_ext_data   | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_user_mapping         | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_subscription         | postgres   | pg_global  | t          | f        | f           | f
 pg_catalog         | pg_attribute            | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_proc                 | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_class                | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_attrdef              | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_constraint           | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_inherits             | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_index                | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_operator             | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_opfamily             | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_opclass              | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_am                   | postgres   |            | t          | f        | f           | f
……

查看当前连接的用户名

postgres=# select user;
   user
----------
 postgres
(1 row)

postgres=# select * from current_user;
 current_user
--------------
 postgres
(1 row)

postgres=# select current_user;
 current_user
--------------
 postgres
(1 row)

postgres=# select session_user;
 session_user
--------------
 postgres
(1 row)