PostgreSQL 基本使用

基本操作

PostgreSQL安装好后，其会自动以本机当前登录用户名创建三个数据库(Schema)，你可以运行以下命令查看：psql -l。

psql是PostgreSQL的交互式命令行工具

• 连接数据库
  • psql -d DBname -h Hostname -p Port -U Username => 指定参数连接
  • psql postgres => 连接当前用户名下的postgres数据库
• 创建用户
  • 连接数据库 psql postgres
  • 创建用户 CREATE USER username [WITH PASSWORD 'password'];
• 创建数据库
  • CREATE DATABASE dbname;
• 授权
  • 授予所有权限 GRANT ALL PRIVILEGES ON DATABASE dbname to username;
  • 授予查询权限 GRANT SELECT ON DATABASE dbname to username;
  • 权限列表 SELECT INSERT UPDATE DELETE RULE REFERENCES TRIGGER CREATE TEMPORARY EXECUTE USAGE
• 常用命令
  • 连接数据库 psql postgres
  • 列出所有数据库 postgres=# \l
  • 列出用户 postgres=# \du
  • 列出数据表 postgres=# \dt
  • 描述数据表 postgres=# \d tablename

postgres=# 为命令行提示符

GUI工具

虽然在命令行下我们可以完成几乎所有的操作，但是GUI工具也有需要的。
这里给大家推荐两个“颜值”还不错的Mac APP: Postico 和 PSequel。

How to Change Privileges of Roles in PostgreSQL

To change the attributes of an already created role, we use the “ALTER ROLE” command.

This command allows us to define privilege changes without having to delete and recreate users as we demonstrated earlier.

The basic syntax is:

ALTER ROLE role_name WITH attribute_options;

For instance, we can change “demo_role” back to its previous state by issuing this command:

ALTER ROLE demo_role WITH NOLOGIN;

ALTER ROLE

We can see the privileges have reverted to their previous state:

\du

                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 demo_role | Cannot login                                   | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 test_user |                                                | {}

We can easily change it back with the following command:

ALTER ROLE demo_role WITH LOGIN;