--创建schemacreate schema rudy;--创建一个他人拥有的schemacreate schema rudy_schema authorization rudy;--在创建表时,如果没有指定模式则在默认为public模式create table rudy.t(id serial);--设置模式的搜索路径show search_path;set search_path to rudy_schema,public;--创建用户,用户和用户组在整个集群范围内共享create user rudy with password '123456';select * from pg_roles;select * from pg_user;--赋予权限角色CREATE ROLE postgres SUPERUSER;grant postgres to wcs_p;grant all privileges on database wcsdb to postgres;GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO wcsdb;--创建只读账号create user rudy with password '123456';create schema rudy;--切换连接到super用户下，其中横线(-)表示仍然连接当前数据库，如果是其它的数据库需要带上数据库的名字\c - postgresgrant USAGE on SCHEMA rudy to rudy;grant SELECT ON all tables in schema rudy to rudy;--查询可以mydb=> select * from rudy.t; id ----(0 rows)--但没有insert权限mydb=> insert into rudy.t values(1);ERROR:  permission denied for relation t--赋予插入权限grant insert on table rudy.t to rudy;--设置user/role为只读，注意它只对之后建立的连接起作用，对当前连接不起作用alter user rudy set default_transaction_read_only=true;alter role rudy reset default_transaction_read_only;--此时用户即使有插入权限也不能执行插入mydb=> insert into rudy.t values(1);ERROR:  cannot execute INSERT in a read-only transaction--查看用户的隔离级别show default_transaction_read_only;--查看哪些用户对表有权限\z rudy.t 或者 \dp rudy.tselect relname,relacl from pg_class where relname='t';--权限字母代表的权限的意思如下：a: insertr: selectw: updated: deletex: referencest: triggerD: truncate--查看系统表，查看角色权限select * from INFORMATION_SCHEMA.role_table_grants where grantee='ztravel';--查看一个表被授予了哪些权限给用户select * from information_schema.role_table_grants where grantee='ztravel' and table_name='new_year_user_award_record';--查看用户与角色之间的关系select pr1.rolname 角色名,pr2.rolname 成员名 from pg_auth_members pam,pg_roles pr1,pg_roles pr2 where pam.roleid=pr1.oid and pam.member=pr2.oid;select pr1.rolname 角色名,pr2.usename 成员名 from pg_auth_members pam,pg_roles pr1,pg_user pr2 where pam.roleid=pr1.oid and pam.member=pr2.usesysid; --对于USAGE权限，必须赋予用户，否则用户不能使用相对应的schema权限（即使给用户赋予了其它权限）postgres=# revoke usage on SCHEMA statsrepo from dba; REVOKEpostgres=# grant select on table statsrepo.test to dba;GRANT--用户拥有select权限但没有usage权限，不能查询表postgres=> select * from statsrepo.test;ERROR:  permission denied for schema statsrepoLINE 1: select * from statsrepo.test;--给用户usage权限，能够正确查询postgres=# grant USAGE on SCHEMA statsrepo to dba;  postgres=> select * from statsrepo.test; id ----(0 rows)--查看某个sequence的权限给了哪些用户或role角色SELECT n.nspname as "Schema",  c.relname as "Name",  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'foreign table' END as "Type",  pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",  pg_catalog.array_to_string(ARRAY(    SELECT attname || E':\n  ' || pg_catalog.array_to_string(attacl, E'\n  ')    FROM pg_catalog.pg_attribute a    WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL  ), E'\n') AS "Column access privileges"FROM pg_catalog.pg_class c     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespaceWHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')  AND c.relname ~ '^(t_seq)$'  AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)ORDER BY 1, 2--如果有以下错误，请查看数据库的监听地址psql: could not connect to server: Connection refused        Is the server running on host "rudy_01" (10.10.10.219) and accepting        TCP/IP connections on port 5431?		listen_addresses = '*'    --如果有 psql: FATAL:  no pg_hba.conf entry for host "10.10.10.219", user "repuser", database "repuser" 错误--请检查连接的ip以及连接的数据库与连接的用户名 host   all     repuser          192.168.174.0/24         md5 host   all     repuser          0.0.0.0/32         md5  ##匹配唯一的ip host   all     repuser          0.0.0.0/0         md5   ##匹配所有的ip   --查看用户是否有表列上的权限postgres=> select has_any_column_privilege('dba','t2','insert,update'); has_any_column_privilege -------------------------- t   --查看用户是否有role postgres=> select pg_has_role('rudy','dba','member');                   pg_has_role ------------- f  --查看用户能否看到某张表的内容，pg_table_is_visible可用于表，视图，索引等 postgres=> select pg_table_is_visible('t'::regclass); pg_table_is_visible --------------------- t(1 row)postgres=> SELECT relname FROM pg_class WHERE pg_table_is_visible(oid);                 relname                 ----------------------------------------- pg_statistic pg_type