PostgreSQL基本操作总结

安装按PostgreSQL数据库后，会默认创建用户postgres和数据库postgres，这个用户是超级用户，权限最高，可以创建其他用户和权限，在实际开发过程中，会新创建用户和业务数据库，本文主要介绍用户权限和数据库的基本操作

1. 用户权限

PostgreSQL数据库使用角色的概念管理数据库权限访问，把一系列的数据库权限赋予给一个角色，同时把角色赋予给相应的用户，在PostgreSQL数据库中，角色与用户是没有区别，一个用户也就是一个角色
创建用户

CREATE USER name [ [ WITH ] option [ ... ] ]

创建角色

CREATE ROLE name [ [ WITH ] option [ ... ] ]

两则的区别是CREATE USER默认创建的用户有LOGIN权限，CREATE ROLE创建的用户没有LOGIN权限

option常用的选项如下所示：

详细解释如下：

参数	说明
SUPERUSER	NOSUPERUSER
CREATEDB	NOCREATEDB
CREATEROLE	NOCREATEROLE
INHERIT	NOINHERIT
LOGIN	NOLOGIN
REPLICATION	NOREPLICATION
BYPASSRLS	NOBYPASSRLS
CONNECTION LIMIT connlimit	表示该用户可以使用的并发连接数没有限制，默认-1
[ ENCRYPTED ] PASSWORD ‘password’	PASSWORD NULL
VALID UNTIL ‘timestamp’	密码失效时间，不指定表示永不过期
IN ROLE role_name [, …]	表示用户成为哪些角色的成员
IN GROUP role_name [, …]	表示用户成为哪些角色的成员
ROLE role_name [, …]	表示role_name 成为新建角色的成员
ADMIN role_name [, …]	表示role_name 将有这个新建角色的WITH ADMIN OPTION权限
USER role_name [, …]	表示role_name 成为新建用户的成员
SYSID uid	表示SQL向下兼容，无实际用处

用户的权限分为两类，一类是在创建用户时指定的权限，可以使用ALTER ROLE命令修改
另一类是由GRANT命令和REVOKE命令管理

1. 在数据库中创建模式(SCHEMA)
2. 在指定的数据库中创建临时表
3. 连接某个数据库
4. 在模式中创建数据库对象，如创建表、视图、函数
5. 在表中做SELECT、UPDATE、INSERT、DELETE等操作
6. 对序列进行查询、使用和更新
7. 在声明中创建触发器
8. 把表、索引建到指定表空间

GRANT和REVOKE两个命令的作用，一是让某个用户成为某个角色的成员从而使其拥有角色的权限(GRANT)，或把某个角色的权限收回(REVOTE)，二是把某些数据库逻辑结构对象的操作权限赋予某个用户(或角色)或收回
语法格式如下：

GRANT some_privileges ON database_object_type object_name TO role_name;
REVOKE some_privileges ON database_object_type object_name FROM role_name;

role_name：具体的用户名或角色名
some_privileges：表示该数据库对象的权限，如SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、CREATE、ALL PRIVILEGES
database_object_type：数据库对象类型，如TABLE、SEQUENCE、SCHEMA

2. 数据库操作

创建数据库的语法如下：

参数说明：

参数	说明
OWNER [=] user_name	指定新建的数据库属于哪个用户，默认属于当前执行命令的用户
TEMPLATE [=] template	模板名(从哪个模板创建新数据库)，默认使用template1
ENCODING [=] encoding	创建新数据库使用的字符编码
TABLESPACE [=] tablespace_name	指定和新数据库关联的表空间名称
CONNECTION LIMIT [=] connlimit	指定数据库可以接受多少并发连接，默认-1，表示没有限制
示例如下：

create database yu_commerce with owner xxx template=template0 encoding 'UTF-8';

修改数据库的语法如下：

删除数据库语法如下：

注意：如果有用户连接在这个数据库上，将无法删除该数据库