1. 用户和角色的关系 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 1.创建角色 postgres= CREATE ROLE 2.创建用户 postgres= CREATE ROLE 3.使用角色登录 [smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d postgres -U irole psql: FATAL: role "irole" is not permitted to log in 4.使用用户登录 [smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d postgres -U iuser psql (9.6.1) Type "help" for help . postgres=> \q
由上,我们可以看出用户和角色的差别在于：用户有登录权限,角色没有登录权限 2. 数据库和模式的关系 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 1. 创建数据库 [smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d postgres psql (9.6.1) Type "help" for help . postgres= CREATE DATABASE postgres= List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- mydb | smallasa | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | smallasa | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | smallasa | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/smallasa + | | | | | smallasa=CTc/smallasa template1 | smallasa | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/smallasa + | | | | | smallasa=CTc/smallasa (4 rows) postgres= 2.对用户和角色授权 postgres= GRANT postgres= GRANT 3.使用用户登录数据库mydb,查看当前的模式 [smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d mydb -U iuser psql (9.6.1) Type "help" for help . mydb=> \dn List of schemas Name | Owner --------+---------- public | smallasa (1 row) 说明: 数据库默认有一个public模式 4.在mydb中创建一张表,并查看表的映射关系 mydb=> CREATE TABLE test (id integer not null); CREATE TABLE mydb=> \d List of relations Schema | Name | Type | Owner --------+------+-------+------- public | test | table | iuser (1 row) 说明:默认表创建在了public模式中 5.创建一个新模式 mydb=> create schema myschema authorization iuser; CREATE SCHEMA mydb=> create schema mydb authorization irole; ERROR: must be member of role "irole" 说明: 我们创建一个模式myschema,并给它授权iuser的权限; 上面创建模式时为它授权irole的权限失败,是因为当前登录用户为iuser,iuser用户没有在irole中 重要说明: role相当于一个组,理想创建方式为:首先创建一个role,其次对role授权,然后创建N个用户,将用户加入到role.此时加入到role的用户拥有role的权限 6.在指定的模式内创建表 mydb=> create table myschema.test(id integer not null); CREATE TABLE 7.切换到创建的新模式 mydb=> set search_path to myschema; SET mydb=> show search_path; search_path ------------- myschema (1 row) 说明,如果不切换到新模式下,无法看到表的列表信息 8.查看列表信息 mydb=> \dt List of relations Schema | Name | Type | Owner ----------+------+-------+------- myschema | test | table | iuser (1 row) 9.补充一:删除schema中的表 mydb=> drop table myschema.test; or: mydb=> set search_path to myschema; mydb=> drop table test ; 10.补充二: 删除schema mydb=> drop schema myschema; //删除空的schema or mydb=> drop schema myschema cascade; //强制删除非空schema
综上,模式是数据库的逻辑分割.一个数据库可以有多个模式.数据库内的对象是被创建在模式内的
注意: 用户登录到系统,连接到一个数据库后,是通过该数据库的search_path来寻找schema的搜索顺序,可以通过命令”show search_path;”.具体的顺序,也可以通过”SET search_path TO ‘schema_name’” 来修改顺序. 3. 表空间和数据库关系 数据库创建语句”CREATE DATABASE dbname”默认的数据库所有者是当前创建数据库的角色,默认的表空间是系统的默认表空间–”pg_default” 在PostgreSQL中,数据的创建是通过克隆数据库模板来实现的,这与SQL SERVER是同样的机制 由于”CREATE DATABASE dbname”并没有指明数据库模板,所以系统将默认克隆template1数据库,得到新的数据库dbname.而template1数据库的默认表空间是pg_default,这个表空间是在数据库初始化时创建的,所以所有template1中的对象将被同步克隆到新的数据库中. 相对完整的语法应该是这样的：”CREATE DATABASE dbname OWNER kanon TEMPLATE template1 TABLESPACE tablespacename;” 1 2 3 4 5 6 7 8 9 1.创建表空间存放位置 [smallasa@localhost ~]$ mkdir /mnt/data/pgsql/tablespace 2.创建表空间 [smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d postgres postgres= 3.创建数据库 postgres=
总结
表空间是一个存储区域,在一个表空间中可以存储多个数据库,尽管PostgreSQL不建议这么做,但我们这么做完全可行
一个数据库并不知直接存储表结构等对象的,而是在数据库中逻辑创建了至少一个模式,在模式中创建了表等对象,将不同的模式指派该不同的角色,可以实现权限分离,又可以通过授权,实现模式间对象的共享,并且,还有一个特点就是：public模式可以存储大家都需要访问的对象.
注意: 既然一个表在创建的时候可以指定表空间,那么,是否可以给一个表指定它所在的数据库表空间之外的表空间呢？ 完全可以,但这不是违背了表属于模式,而模式属于数据库,数据库最终存在于指定表空间这个网的模型了吗?是的,看上去这确实是不合常理的,但这么做又是有它的道理的,而且现实中,我们往往需要这么做:将表的数据存在一个较慢的磁盘上的表空间，而将表的索引存在于一个快速的磁盘上的表空间.但我们再查看表所属的模式还是没变的,它依然属于指定的模式.所以这并不违反常理.实际上,PostgreSQL并没有限制一张表必须属于某个特定的表空间,我们之所以会这么认为,是因为在关系递进时,偷换了一个概念:模式是逻辑存在的,它不受表空间的限制!!!