PostgreSQL创建库、表空间、角色、SCHEMA

前置

sudo su - postgres // 切换到postgres用户
1、PostgreSQL会在安装阶段默认创建一个超级用户角色和一个database，均是postgres，
a、修改PostgreSQL数据库默认用户postgres的密码
sudo  passwd -d postgres //删除用户postgres的密码
sudo -u postgres passwd //设置用户postgres的密码
// 系统提示输入新的密码
New password:
Retype new password:
passwd: password updated successfully

库

 PG中可以采用分库的方式，即不同的模块创建不同的库；也可以使用同一个库下创建多个SCHEMA的方式；但一个数据连接只能访问一个库，不能跨库访问，即不同数据库之间不能关联查询，参考：
https://www.postgresql.org/docs/9.6/static/ddl-schemas.html
       A PostgreSQL database cluster contains one or more named databases. Users and groups of users are shared across the entire cluster, but no other data is shared across databases. Any given client connection to the server can access only the data in a single database, the one specified in the connection request.
      建议采用同一个库下创建不同SCHEMA的方式。

表空间

建表时，可以指定该表存放在哪个表空间上， 如：create table tbl(id int, name text) tablespace mytablespace
如果不指定，创建在默认的表空间上；
PG中可以为某个库设置默认表空间，也可以为某个用户设置默认表空间；
如：ALTER USER mydb_role SET default_tablespace='mytablespace'

注意：

• PostgreSQL中创建表空间时，需要指定的是路径，不是文件，且不需要设置表空间的最大大小（Oracle需要根据表空间的最大大小计算需要生成多少个最大4G的数据文件）

         PG中创建表空间的方式如：
          mkdir /usr/local/pgdata
          chown postgres:postgres /usr/local/pgdata/
          create tablespace tbs_test owner postgres location '/usr/local/pgdata';
   

• 数据库中不建议使用双引号，最好还是在建表和查询时都不要使用双引号，不推荐在DDL里用双引号的方式创建区分大小写的对象。默认会把不带双引号的表名转成小写表名去进行操作。

角色

// CREATE ROLE 角色术语来表示用户账户的概念， CREATE USER （可登录角色）和 CREATE GROUP（组角色）不建议使用。
CREATE ROLE mydb_admin login password 'abc123';
drop DATABASE if exists mydb;
CREATE DATEBASE mydb WITH owner = mydb_admin; 
// 这样就可以用mydb_admin登录创建schema和表等，否则如下
drop schema if exists mydb_schema cascade;
drop tablespace if exists mydb_dbname;
drop user if exists mydb_role;
--user
CREATE USER mydb_role WITH
    LOGIN
    NOSUPERUSER
    CREATEDB
    NOCREATEROLE
    INHERIT
    REPLICATION
    CONNECTION LIMIT -1
    PASSWORD '%$MY-PWD$%';
--create tablespace
CREATE TABLESPACE mydb_dbname
OWNER mydb_role
LOCATION mydb_dbpath;
--default_tablespace
ALTER USER mydb_role SET default_tablespace='mydb_dbname';
--schema
CREATE SCHEMA mydb_schema AUTHORIZATION mydb_role;
--default schema
ALTER USER mydb_role SET search_path=mydb_schema;
GRANT USAGE ON SCHEMA mydb_schema TO PUBLIC;
ALTER DEFAULT PRIVILEGES for user mydb_role in schema mydb_schema 
GRANT SELECT ON TABLES TO public;

SCHEMA

                 CREATE SCHEMA %$MY-SCHEMA$% AUTHORIZATION %$MY-USER$%;
         也可以省略schema_name，如：
                CREATE SCHEMA  AUTHORIZATION %$MY-USER$%;

省略schema_name的情况下，schema_name和user相同，且通过该user连接上来时，默认就在该schema下，参考：
https://www.postgresql.org/docs/9.6/static/ddl-schemas.html
You can even omit the schema name, in which case the schema name will be the same as the user name.
Recall that the default search path starts with $user, which resolves to the user name. Therefore, if each user has a separate schema, they access their own schemas by default.

后置

MARK: 红色部分很重要。