6.PG数据库管理

1.PG数据库库属主

• PG中的数据库属主属于创建者，只要有cretedb权限就可以创建数据库，数据库属主不一定拥有存放在该数据库中其它用户创建的对象的访问权限。
• 数据库在创建后，允许public角色连接，即允许任何人连接
• 数据库在创建后，不允许除了超级用户和owner之外的任何人在数据库中创建schema
• 数据库在创建后，会自动创建名为public的schema,这个schema的all权限已经赋予给了public角色，即允许任何人在里面创建对象，但对已存在的其它用户的表不具有任何权限。

2.PG数据库权限(database)　

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

• Create：可以在指定的数据库中创建schema权限
• connect：可以连接到指定数据库的权限
• temporary：可以创建临时表的权限
• ALL：指定数据库所有的权限
• 由于数据库在创建后，允许public角色连接，即允许任何人连接，所以如果要取消某个用户对指定数据库连接的权限，需要先取消public的连接权限，再取消用户的连接权限。
  • 授权用户连接数据库的权限
  • grant  connect on database db_name to user_name;
  • 撤销用户连接数据库的权限
  • revoke connect on database db_name from user_name;
  • revoke connect on databaese db_name from public;
  • 查看哪些用户有某个数据库connect权限
  • select datname,datactl from pg_database  where datname='db_name';

3.PG数据库环境设置

• PG是参数设置为实例级，数据库级，用户级和会话级，而有些参数可以在所有级别中设置，优先顺序为会话级>用户级>数据库级>实例级。

ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL

　　1）设置数据库搜索路径：

　　　　alter database postgres  set search_path to "$user",public,schema_name;

　　2）配置连接某个库时可使用的工作内存

　　　　alter database postgres set work_mem = '8MB';

　　3）配置连接某个库时可使用的维护内存

　　　　alter database postgres set maintenance_work_mem to '256MB';

　　4）配置连接某个库后执行执行语句最多时长(执行1秒超时)

　　　　alter database postgres set statement_timeout=1000;

　　5）配置某个库使用日志记录级别(设置后，对这个数据库的访问不记录日志)

　　　　alter database postgres set log_statement=none;

　　6）配置连接某个库后的wal日志写盘级别(设置后，该库的更新操作只要求本地提交)

　　　　alter database postgres set synchronous_commit to local;

　　7）配置连接某个库后禁用某个规划器(禁用indexonlyscan扫描)

　　　　alter database postgres set enable_indexonlyscan to off;

　　8）查看所有个性化配置

　　　　\drds

　　9）查询数据的连接数限制只能查看数据字典表

　　　　select datname,datconnlimit from pg_database;

　　10）设置某个个性化设置为默认值

　　　　alter database postgres reset exit_on_error;

　　11）设置所有个性化设置为默认值

　　　　alter database postgres reset all;

4.PG数据库的属性　　　　　　

• 数据库的属性我们进行修改，修改范围是数据库名字，属主，表空间

ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER DATABASE name SET TABLESPACE new_tablespace

5.示例

　　创建一个测试库mydb，归属为用户pguser，同时为mydb库分配一个新表空间tbs_mydb.　

        --创建用户
        postgres=# CREATE ROLE pguser WITH ENCRYPTED PASSWORD 'pguser';
        CREATE ROLE

        --创建表空间目录
        [postgres@pghost1~]$ mkdir -p /database/pg10/pg_tbs/tbs_mydb

        --创建表空间
        postgres=# CREATE TABLESPACE tbs_mydb OWNER pguser LOCATION '/database/pg10/pg_
            tbs/tbs_mydb';
        CREATE TABLESPACE

        --创建数据库
        postgres=# CREATE DATABASE mydb
                      WITH  OWNER = pguser
                      TEMPLATE = template0
                      ENCODING = 'UTF8'
                      TABLESPACE = tbs_mydb;
        CREATE DATABASE

        --赋权
        GRANT ALL ON DATABASE  mydb TO pguser WITH GRANT OPTION;
        GRANT ALL ON TABLESPACE tbs_mydb TO pguser;