postgres 数据库

最新推荐文章于 2024-07-28 22:09:49 发布

fax5201314

最新推荐文章于 2024-07-28 22:09:49 发布

阅读量1.5k

点赞数

分类专栏：开源软件使用数据库文章标签： postgresql database

开源软件使用同时被 2 个专栏收录

12 篇文章 0 订阅

订阅专栏

数据库

4 篇文章 0 订阅

订阅专栏

转自http://openwares.net/database/postgresql_tablespace_database_user_schema_table-2.html

表空间、数据库、角色、模式及表之间的关系

表空间用于定义数据库对象在物理存储设备上的位置，不特定于某个单独的数据库。

数据库是数据库对象的物理集合，而schema则是数据库内部用于组织管理数据库对象的逻辑集合，
schema名字空间之下则是各种应用程序会接触到的对象，比如表，索引，数据类型，函数，操作符等。

角色(用户)则是数据库服务器(集群)全局范围内的权限控制系统，用于各种集群范围内所有的对象权限管理。
因此角色不特定于某个单独的数据库，但角色如果需要登录数据库管理系统则必须连接到一个数据库上。

角色可以拥有各种数据库对象。

数据库管理模式

对于一个应用程序分很多相对独立的模块，每个模块有相对独立的数据结构，可以采用每个模块一个数据库用户及与其名字相同的schema来组织数据库，并且整个的物理数据库放在一个单独的表空间中。

使用这种数据库管理模式，可以撤销掉对public schema的访问许可，甚至把public schema直接移除，这样每个用户就真正的限定在了他们自己的schema里。

大致的数据库创建流程如下：

创建数据库拥有者角色

用超级用户postgres登录postgres数据库，然后创建角色foo并使其有CREATEDB CREATEROLE LOGIN权限

 
        $ psql -U postgres -h localhost 
       
        postgres=#  
        CREATE 
        ROLE foo CREATEDB CREATEROLE LOGIN 
        PASSWORD 
        'password' 
        ;

创建表空间并将其所有权赋予新创建的用户

创建表空间ts_foo并将其所有权赋予角色foo

 
        postgres=#  
        CREATE 
        TABLESPACE ts_foo OWNER foo LOCATION 
        '/mnt/raid3805' 
        ;

创建数据库

使用新创建的用户foo登录postgres数据库，然后在ts_foo表空间上创建同名数据库foo（在哪个用户下创建默认属于哪个用户，所以可以不加OWNER foo）

 
        $ psql -U foo -h localhost -d postgres 
       
        postgres=>  
        CREATE 
        DATABASE 
        foo TABLESPACE ts_foo;

创建其他用户

用新创建的角色foo登录foo数据库，然后创建其他用户bar，并为bar赋予数据库foo上的CREATE权限

 
   
        $ psql -U foo -h localhost 
       
 
        foo=>  
        CREATE 
        ROLE bar LOGIN 
        PASSWORD 
        'password' 
        ; 
       
 
        foo=>  
        GRANT 
        CREATE 
        ON 
        DATABASE 
        foo 
        TO 
        bar; 
       
 
 

创建用户的schema

使用角色bar登录数据库foo，然后创建角色bar拥有的schema bar

 
        $ psql -U bar -h localhost -d foo 
       
        foo=>  
        CREATE 
        SCHEMA 
        bar;

bar用户创建其同名schema下的表对象

 
        foo=>  
        CREATE 
        TABLE 
        foobar (id 
        INTEGER 
        );

注意：

shell命令psql连接到哪个数据库呢？情况是这样的：

如果只使用psql不提供任何参数，则psql试图连接到与当前发出命令的操作系统用户名同名的数据库，如果提供命令行参数-U指定要使用的登录角色名，那么psql试图去连接与登录名同名的数据库，如果提供了-d 命令行参数明确的指定要连接的数据库，则psql试图连接到这个指定的数据库。

select rolname  from pg_roles;--查看角色
select usename from pg_user;--查看用户
select oid,datname,* from pg_database; --查看数据库id,模板,
SELECT  * FROM pg_namespace;--查看SCHEME

DROP DATABASE "SGDOCSTR";--删除数据库
DROP role "SGDOCSTR";--删除角色

CREATE ROLE "SGDOCSTR" SUPERUSER CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD 'SGDOCSTR';--创建role并赋予创建数据库创建角色登录加密密码权限(如需超级管理员)
CREATE DATABASE "SGDOCSTR" OWNER "SGDOCSTR" ;--创建数据库属于sgdocstr角色

C:\Program Files\PostgreSQL\9.3\bin>psql -U postgres SGDOCSTR < d:\sgdocstr.sql --使用postgresql的psql命令导入(如果需要操作系统用户最好用系统用户<pre name="code" class="sql">postgres 登录操作)
C:\Program Files\PostgreSQL\9.3\bin>pg_dump -h localhost -U postgres  SGDOCSTR > d:\sgdocstr_20150115.sql --导出命令

//系统自带的数据库的sql命令
-- Database: postgres
-- DROP DATABASE postgres;
CREATE DATABASE postgres
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'Japanese_Japan.932'
       LC_CTYPE = 'Japanese_Japan.932'
       CONNECTION LIMIT = -1;
GRANT CONNECT, TEMPORARY ON DATABASE postgres TO public;
GRANT ALL ON DATABASE postgres TO postgres;
GRANT ALL ON DATABASE postgres TO "SGDOCSTR";

COMMENT ON DATABASE postgres
  IS 'default administrative connection database';
//取消 权限<div>REVOKE ALL ON  DATABASE postgres FROM "SGDOCSTR";</div>
 安装postgresql
yum install postgresql postgresql-server
mysql占用端口3306 pgsql是5432
2
导入整个数据库
psql -U postgres(用户名)  数据库名(缺省时同用户名) < /data/dum.sql
3
导出整个数据库
pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名)   >/data/dum.sql
4
导出某个表
pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名)  -t table(表名) >/data/dum.sql
5
压缩方法
一般用dump导出数据会比较大，推荐使用xz压缩
压缩方法  xz dum.sql 会生成 dum.sql.xz 的文件
6
xz压缩数据倒数数据库方法
xzcat /data/dum.sql.xz | psql -h localhost -U postgres(用户名) 数据库名(缺省时同用户名)

-- Role: zqtest

-- DROP ROLE "zqtest";CREATE ROLE "zqtest" LOGIN ENCRYPTED PASSWORD 'zqtest'; SUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION;-- Database: "zqtest"-- DROP DATABASE "zqtest";CREATE DATABASE "zqtest" WITH OWNER = "zqtest" ENCODING = 'UTF8' TABLESPACE = pg_default --LC_COLLATE = 'Japanese_Japan.932' -- LC_CTYPE = 'Japanese_Japan.932' -- CONNECTION LIMIT = -1;

导出PostgreSQL数据库中的数据：角色（就是用户的概念），文件名，数据库名

$ pg_dump -U newdatabase -f mydatabase.sql mydatabase

导入数据时首先创建数据库再用psql导入：数据库名，角色，文件名

$ createdb newdatabase$ psql -d newdatabase -U postgres -f mydatabase.sql

C:\Program Files\PostgreSQL\9.3\bin>psql -d newdatabase -U newdatabase -f d:\mydatabase.sql

文章来源：http://my.oschina.net/Kenyon/blog/97227先谈一下postgresql中涉及用户密码的几张表：

select username,passwd from pg_shadow;
select rolname,rolpassword from pg_authid;
select usename,passwd from pg_user;
select rolname,rolpassword from pg_roles

前面两张表存有加密后的密码字符串，后面两张表存的密码都是***，我们感兴趣的前面两张表，通过简单的测试我们可以发现，同样的用户，使用同样的密码得到加密后的字符串是一样的。

一般都知道pg_hba.conf里面有配置了MD5方式来访问加密，所以用户表里存的密码数据也该是md5加密后的数据，但到底是怎么组合加密的就不得而知了。

近来读到这块源码时发现postgresql的加密方式是md5(user+passwd)的方式来保存的。
比如用户是test,密码是123456,那么这个pg_shadow里面的passwd必然是
47ec2dd791e31e2ef2076caf64ed9b3d
可以用select md5('test123456')来验证。

知道了这一点，我们就可以调整源码，将加密方式做点改善可以增强一定程度的安全性。

C:\Program Files\PostgreSQL\9.3\data

postgresql.conf 里面添加常量，如果你的表或者试图里面需要用到系统的变量。

# Add settings for extensions here
my_app.passwd = SGDOCSTR

pg_hba.conf文件添加acl权限访问下面的 ip为允许任何ip 和掩码

host all all 0.0.0.0/0 md5