postgres学习笔记

创建数据库

在PostgreSQL中，您首先需要创建一个数据库用户，然后才能创建数据库。数据库用户与操作系统用户不同，它是PostgreSQL内的一个概念，用于管理数据库访问和操作。

以下是创建PostgreSQL用户和数据库的基本命令：

CREATE USER myuser WITH PASSWORD 'mypassword';

CREATE DATABASE mydb OWNER myuser;

当前用户、当前模式、当前数据库

select current_user,current_schema,current_database();

模式搜索路径

show search_path;

在PostgreSQL中，存在两个容易混淆的概念：角色/用户。之所以说这两个概念容易混淆，是因为对于PostgreSQL来说，这是完全相同的两个对象。唯一的区别是:CREATE USER除了默认具有LOGIN权限之外，其他与CREATE ROLE是完全相同的。

创建schema

CREATE SCHEMA my_schema;

CREATE SCHEMA my_schema AUTHORIZATION user_name;这将会把my_schema的所有权赋予user_name用户。

CREATE SCHEMA my_schema CREATE TABLE my_table (
    id serial PRIMARY KEY,
    name varchar(100) NOT NULL
);

这将会在my_schema schema中创建一个名为my_table的表，并包含一个序列主键和一个字符串字段。

pg 中的schema 与 mysql 、Oracle 之间的差异：

1. PostgreSQL中，一个database下可以有多个schema。可以给schema指定一个owner，如果没有指定，那么当前用户就是schema的默认owner。
2. 在Oracle数据库中不能直接新建一个schema，系统在创建一个用户的同时为这个用户创建一个同名的schem并作为该用户的缺省shcema。即schema的个数同user的个数相同，而且schema名字同user名字一一 对应并且相同。
3. 在MySQL中没有schema，所以创建一个database的效果和建立一个schema是相同的。我们可以简单的理解为，MySQL中的database就是schema。

修改schema的属主

alter schema schema_name owner to user_name;

postgres授权给其他用户

授权语法：

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,…] | ALL [ PRIVILEGES ] }
ON DATABASE $database_name [,…]
TO $role_specification [,…] [ WITH GRANT OPTION ]
 
e.g.:
//授权用户wqdb在数据库wqdb上的一切create权限：
GRANT CREATE ON DATABASE wqdb TO wqdb;
//授权访问模式的权限给用户
GRANT USAGE ON SCHEMA $sch_name TO $role_name;
//授权指定模式下对象的访问权限给用户
GRANT SELECT ON $sch_name.$tab_name TO $role_name;

回收权限语法：

//回收单个权限
REVOKE SELECT ON $tab_name FROM $role_name
//回收多个/所有权限
REVOKE SELECT,UPDATE,INSERT ON $tab_name FROM $role_name;
REVOKE ALL ON $tab_name FROM $role_name;
//回收某模式下所有表的查询权限
REVOKE SELECT ON ALL TABLES IN SCHEMA $schema_name FROM $role_name;
//收列权限
REVOKE SELECT(col1),UPDATE(col1) ON $tab_name FROM $role_name;
//回收所有权限
REVOKE ALL ON $tab_name FROM public;

在授权前，需要先执行schema的使用授权，不然授权无效

grant usage on schema user_1 to user_2; 
GRANT SELECT ON TABLE user_1.t_conf_table TO user_2;

查询指定表的授权

SELECT * FROM information_schema.table_privileges WHERE table_name = 'your_table_name';

授权用户查看指定schema 下的所有表（此时库的的所有表，新增表不能看到）
grant select on all tables in schema schem_name to user_name;

授权readonly用户查看新创建表的select权限
alter default privileges in schema schem_name grant select on tables to user_name;

联合使用：

SELECT 'grant usage on schema '||table_schema||' to '||grantee||';
GRANT '||privilege_type||' ON TABLE '||table_schema||'.'||table_name||' TO '||grantee||';
' FROM information_schema.table_privileges WHERE table_name like 'bpl_%'
and grantee <> 'readonly';

创建表

CREATE TABLE schema1.student_t (
	stu_id int8 NOT NULL,
	stu_name varchar NULL,
	stu_sex varchar NULL,
	stu_age int NULL,
	stu_birthday date NULL,
	CONSTRAINT student_t_pk PRIMARY KEY (stu_id)
);
COMMENT ON TABLE schema1.student_t IS '学生表';

-- Column comments

COMMENT ON COLUMN schema1.student_t.stu_id IS '学生ID';
COMMENT ON COLUMN schema1.student_t.stu_name IS '学生名称';
COMMENT ON COLUMN schema1.student_t.stu_sex IS '性别';
COMMENT ON COLUMN schema1.student_t.stu_age IS '年龄';
COMMENT ON COLUMN schema1.student_t.stu_birthday IS '学生生日';