PostgreSQL 使用方法(一)

1 PostgreSQL 的安装

在 Ubuntu 下:
sudo apt-get install postgresql

2 postgres 用户

同 mysql 类似,在安装完 postgresql 后,会自动建立一个名为 postgres 的系统用户,一个名为 postgres 的 postgresql 用户,一个名为 postgres 的数据库。

这个 postgresql 中的 postgres 用户类似于 mysql 中的 root 用户,具有所有权限。

postgres 初始时没有密码。

3 PostgreSQL Shell

postgresql 提供了一个命令 psql,通过该命令可以进入 postgresql shell 环境。

4 登录

psql -h hostname -U username -d database

其中:

  • -h 指定 postgresql 服务器所在主机名或者主机 IP。如果是在该主机上进行登录,则可以不指定;
  • -U 指定登录用户名;
  • -d 指定数据库名。如果存在与 username 同名的数据库,也可以不用指定,登录时会自动进入这个数据库。

4.1 peer

在 postgresql 服务器所在主机上登录 postgresql 的用户(包括 postgres),默认都是以 peer 进行登录验证。所有的验证方式详见:Authentication Methods

所谓的 peer 验证,也即如果操作系统中存在一个用户,与 postgresql 中的一个用户名字一样,那么在进行登录时,会首先会根据执行登录命令时的用户到 postgresql 中查找是否存在同名用户。如果存在,则 peer 验证通过,进入 shell 界面,无需输入该用户在 postgresql 中的密码。这种方式只用于在 postgresql 所在主机上登录。

例如,可以这样以 postgres 用户登录:
sudo -u postgres psql
注意,这里无需再通过 -U 指定用户名。该命令相当于是以操作系统中的 postgres 用户执行 psql,而 postgresql 中也存在一个同名的用户,因此在执行这条命令时会使得 peer 验证通过,进入 shell 界面。

4.2 md5

peer 要求系统中必须存在一个与 postgresql 中某个用户名字相同的用户,这样才能验证成功。如果觉得 peer 带来很多不便,可以将其改成与 mysql 类似的通常的密码验证,也即 md5

打开权限配置文件:
sudo vim /etc/postgresql/9.5/main/pg_hba.conf
找到 # Database administrative login by Unix domain sockey,下面存在两条记录,分别为:
local all postgres peer
local all all peer

上面一条记录管理着 postgres 在 postgresql 所在主机登录的权限;下面这条记录管理着除 postgres 外的其他的用户在 postgresql 所在主机登录的权限。

因此,如果想要改变用户 postgresql 所在主机登录的权限,相应地修改上面某条记录的 peermd5 即可。

4.3 trust

置为 trust 的用户不需要进行身份验证。同样也是在权限配置文件进行更改。

5 用户管理

5.1 添加用户

postgresql 提供了一个系统 shell 命令 createuser 来创建 用户:
sudo -u postgres createuser -P -E username
其中,-P 表示添加密码,-E 表示对密码进行加密,默认为 md5。

该命令的执行过程为,首先会通过 postgres 登录,然后查看该用户是否具有 CREATEROLE 权限(创建用户的权限)。如果具有这个权限,就会创建用户。

也可以先进入 postgresql 的 shell,然后再用 CREATE USER 或者 CREATE ROLE 语句添加用户:
CREATE USER username WITH ENCRYPTED PASSWORD 'password';

CREATE USER 创建的用户默认具有 LOGIN 权限,而 CREATE ROLE 创建的用户默认不具有该权限。

5.2 赋予访问权限

在创建一个数据库时,会指定该数据库的所有者。这个所有者拥有对该数据库的访问权限

对于一个数据库,如果要让其他用户也拥有访问该数据库的权限,则执行如下:
GRANT ALL PRIVILEGES ON DATABASE database TO username
其中,如果是指定用户对某个表的访问权限,则去掉 DATABASE 关键字。

权限分为两类。一类是对数据库的访问权限,通过 GRANT 指定;一类是用户相关的权限(用户属性),通过 CREATE USER 在创建时指定或者 ALTER USER 修改。更多权限详见:GRANTCREATE ROLE

5.3 撤销权限

REVOKE INSERT ON DATABASE database FROM username;

5.4 删除用户

sudo -u postgres dropuser username

或者
DROP USER username;

5.5 显示用户信息

\du

5.6 更改用户密码

\password username
回车后,会提示输入新的密码。

或者
ALTER USER username WITH ENCRYPTED PASSWORD 'new_password';

6 一些细节

  • ctr + d 或者 \q 退出 postgresql;
  • 一条完整的语句可以输入多行,最后以分号结束;
  • 如果一条语句输入多行后,想取消这套语句,那么可以用 \r