postgres schema访问权限设置

查看schemas

[postgres@node1 ~]$ /usr/pgsql-9.6/bin/psql  -h 51.0.1.213 -Ufire firedb

查看schemas

firedb=# \dn+

或

firedb=# SELECT nspname FROM pg_namespace;

Owner表示schemas 属于哪个用户,Access privileges 表示是否可以访问。

授权schema

firedb=# grant USAGE on SCHEMA big_emergency_auth to fire;
GRANT

设置set search_path,这里是搜索路径，如果搜索路径没有这个SCHEMA 我们也无法查看到

set search_path TO "$user",public,amp,apolloconfigdb

查看当前SCHEMA

firedb=# show search_path;

"$user", public, amp, apolloconfigdb,

查看表

firedb=#

firedb=# \dt
                         List of relations
        Schema       |            Name             | Type  | Owner
--------------------+-----------------------------+-------+-------
  amp                | amp_auth                    | table | fire
  amp                | amp_change_log              | table | fire
  amp                | amp_login_log               | table | fire
  amp                | amp_org                     | table | fire
  amp                | amp_org_personnel           | table | fire
  amp                | amp_pw_chg_type             | table | fire
  amp                | amp_role                    | table | fire
  amp                | amp_staff                   | table | fire
  amp                | amp_staff_auth              | table | fire
  amp                | amp_staff_org               | table | fire
  amp                | amp_staff_role              | table | fire
  amp                | ss_org                      | table | fire

……………………………….