PostgreSQL基本使用方法总结【一】

PostgreSQL是一个功能非常强大的、源代码开放的客户/服务器关系型数据库管理系统（RDBMS）,它是跨平台的，可以在许多操作系统下运行，如Linux, FreeBSD, OS X, Solaris和Microsoft Windows等。

• 官方文档：https://www.postgresql.org/docs/current/
• SQL命令: https://www.postgresql.org/docs/current/sql-commands.html

命令行登录

psql -U postgres # 管理员登录
psql -U postgres -d [-d Database] # 登录指定数据库

1001@cirrus-postgresql-0:/$ psql -U postgres
Password for user postgres:
psql (15.2)
Type "help" for help.

postgres=#

创建用户

CREATE USER testUser WITH PASSWORD '*****';

创建数据库

CREATE DATABASE testDb OWNER testUser;

给用户赋权限

权限定义：

表的权限

• Delete
• Insert
• References
• Select
• Trigger
• Truncate
• Update

数据库的权限

• Connect
• Create
• Temporary

模式的权限

• Create
• Usage

将数据库testDB所有权赋给用户testUser

GRANT ALL PRIVILEGES ON DATABASE testDB TO testUser;

如果要分权限分表赋权，示例：

GRANT SELECT ON TABLE mytable TO testUser;

将数据库连接权限授权于 testUser
GRANT CONNECT ON DATABASE 数据库名 to testUser;

将schema的usage权限授权于 testuser(具体其他权限需要添加看权限说明)
GRANT USAGE ON SCHEMA public to testUser;

将schema为public下的某个表的查询权限授权于 testuser(具体其他权限需要添加看权限说明)
GRANT SELECT on public.tb_base_cs_表名 to testUser;

给testUser用户授予库下cs模块下所有表和视图的全部权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA cs TO testUser;

给testUser用户授予库下cs模块下所有表和视图的默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA cs GRANT all ON TABLES TO testUser;

给testUser用户授予库下cs模块下所有表的查询权限
grant select ON ALL TABLES IN SCHEMA cs to testUser;

移除权限

示例：

1.移除testuser用户对于public下所有表的所有权限

REVOKE ALL PRIVILEGES ON all tables in schema public FROM testuser;

2.移除testuser用户对于public的使用权限

REVOKE Usage ON SCHEMA "public" FROM "testuser";

3.移除testuser用户对于cs数据库所有权限

REVOKE ALL PRIVILEGES ON DATABASE cs FROM testuser;

4.移除testuser用户对于tb_base_cs_表名 表的查询权限

REVOKE SELECT ON public.tb_base_cs_表名 FROM testuser;

删除数据库

DROP DATABASE testDB;

更改用户密码

ALTER USER postgres WITH PASSWORD 'postgres';

删除用户

DROP ROLE testUser;
DROP USER [ IF EXISTS ] name [, ...]
或
DROP ROLE [ IF EXISTS ] name [, ...]

如果因为依赖而报错，需要先把要删除用户的相关依赖的权限revoke.

db1=# drop role user1;
ERROR:  role "user1" cannot be dropped because some objects depend on it
DETAIL:  privileges for column oid of table pg_proc
privileges for column proname of table pg_proc
......

如果删除用户是某Dababase的owner

下面介绍两种能够简化清理被删除用户下的对象和权限的方法:

方法一：通过角色继承方式赋予给用户的权限，在删除用户时，不需要额外的回收权限操作。 角色继承是将现在以及将来某个角色所具有的权限授予其它角色。它非常适用于给多个用户赋予相同的一组权限的场景。

一个角色可以继承其他角色的权限从而成为其成员角色 （member role）；拥有成员角色的角色称为组角色 （group role）。

GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]

测试：

##创建待删除用户user4
db1=# create user user4 password 'xxxxxx';
CREATE ROLE
##给user4用户赋予db1数据库schema的owner权限，并使用user4用户在postgres数据库下创建表test1.
db1=# create schema user4 authorization user4;
CREATE SCHEMA
db1=# \c postgres user4
You are now connected to database "postgres" as user "user4".
postgres=> create table test1 (id int);
CREATE TABLE
##创建组角色role1，并赋予其omm2模式的所有权限，和表omm2.ttt的所有权限
postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres=# create role role1;
CREATE ROLE
postgres=# grant all on schema omm2 to role1;
GRANT
postgres=# grant all on table omm2.ttt to role1;
GRANT
##将角色role1的权限赋给user4
postgres=# grant role1 to user4;
GRANT ROLE
##使用user4就可以查看表omm2.ttt中的数据了
postgres=# \c - user4
You are now connected to database "postgres" as user "user4".
postgres=> select * from omm2.ttt;
 id1 | id2 | name  
-----+-----+-------
   1 |   1 | test1
(1 row)
##此时删除用户user4,删除操作报错user4用户拥有表test1,以及在db1数据库下存在1个对象
postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres=# drop user user4;
ERROR:  role "user4" cannot be dropped because some objects depend on it
DETAIL:  owner of table test1
1 object in database db1
##删除user4拥有的对象后，user4用户就被删除了。

postgres=# drop table public.test1;
DROP TABLE
postgres=# \c db1
You are now connected to database "db1" as user "postgres".
db1=# drop schema user4;
DROP SCHEMA
db1=# drop user user4;
DROP ROLE
db1=#
##到这里我们看到在删除用户的时候并不需要额外处理用户通过继承role1而间接拥有的omm2.ttt表的权限，这就是角色继承给删除用户操作带来的方便

注：如果user4用户的系统权限为noinherit,那么user4不会自动继承他所拥有的其他角色的权限，需要使用set role 命令来显示的继承角色权限。系统权限如createdb和createrole等不会被自动继承，只能通过set role命令显示的继承。

方法二：使用ressign owned / drop owned命令来批量转移用户对象/删除用户对象及权限

REASSIGN OWNED：

转移当前角色拥有的数据库对象，只转移当前数据库的对象，多个数据库分别进行reassign。

REASSIGN OWNED BY { old_role | CURRENT_USER | SESSION_USER } [, ...]
               TO { new_role | CURRENT_USER | SESSION_USER }

如果待删除用户下存在需要保留的数据库对象，可以使用ressign owned命令，把当前数据库下该用户的所有数据库对象转移给其他用户。

DROP OWNED：

删除角色所拥有的所有对象并且收回该已经授予给该角色的在其他对象上的特权。只影响当前数据库的对象，多个数据库分别进行

DROP OWNED：

角色所拥有的数据库、表空间将不会被移除。

DROP OWNED BY { name | CURRENT_USER | SESSION_USER } [, …] [ CASCADE | RESTRICT ]

如果待删除用户在当前数据库下的所有对象都需要删除，就可以使用 drop owned命令，把当前数据库下该用户的所有数据库对象（数据库和表空间除外，需要单独处理）和权限删除掉。

测试1：

##删除用户user1时报错，待删除用户在当前数据库下仍拥有数据库对象和权限
db1=# drop role user1;
ERROR:  role "user1" cannot be dropped because some objects depend on it
DETAIL:  privileges for column oid of table pg_proc
privileges for column proname of table pg_proc
......
privileges for column proacl of table pg_proc
privileges for database db1
owner of database db2
owner of schema user1
owner of table user1.t1
owner of table user1.t2
privileges for function f_inv()
##使用ressign owned 命令将当前数据库对象转移到user4用户下
db1=# REASSIGN OWNED BY user1 TO user4;
REASSIGN OWNED
db1=# \dt user1.*
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 user1  | t1   | table | user4
 user1  | t2   | table | user4
(2 rows)
##再次尝试删除用户user1，提示user1还拥有对象权限
db1=# drop role user1;
ERROR:  role "user1" cannot be dropped because some objects depend on it
DETAIL:  privileges for column oid of table pg_proc
privileges for column proname of table pg_proc
.....
privileges for column proacl of table pg_proc
privileges for database db1
privileges for function f_inv()
##使用drop owner 命令删除用户user1在当前数据库下的用户权限
db1=# drop OWNED BY user1;
DROP OWNED
##再次删除user1,成功
db1=# drop role user1;
DROP ROLE

测试2

##删除用户user1，失败，提示user1在多个数据库下存在对象和权限
postgres=# drop user user1;
ERROR:  role "user1" cannot be dropped because some objects depend on it
DETAIL:  privileges for tablespace pg_default
privileges for tablespace pg_global
owner of database db1
owner of tablespace tbs1
owner of table tp1
16 objects in database db1
##使用drop owner 命令删除用户user1在当前数据库下的用户权限
postgres=# drop OWNED BY user1;
DROP OWNED
##再次尝试删除用户user1，提示user1在db1数据库下还拥有对象，且还拥有表空间和数据库
postgres=# drop user user1;
ERROR:  role "user1" cannot be dropped because some objects depend on it
DETAIL:  owner of database db1
owner of tablespace tbs1
16 objects in database db1
###登录到db1数据库
postgres=# \c db1 postgres
You are now connected to database "db1" as user "postgres".
##使用drop owner 命令删除用户user1在db1数据库下的用户权限,提示待删除对象具有关联对象，可以使用casecade关键字，级联删除关联对象
db1=# drop OWNED BY user1;
ERROR:  cannot drop desired object(s) because other objects depend on them
DETAIL:  column postal of table user2.us_snail_addy depends on type user1.us_postal_code
column status of table user2.bug depends on type user1.bug_status
server rhnsrv depends on foreign-data wrapper postgres_fdw
user mapping for user2 on server rhnsrv depends on server rhnsrv
user mapping for user3 on server rhnsrv depends on server rhnsrv
foreign table stu depends on server rhnsrv
foreign table test depends on server rhnsrv
foreign table tp1 depends on server rhnsrv
foreign table tp3 depends on server rhnsrv
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
##确认相关对象可以被删除，使用带有cascade关键字的drop owned命令，成功删除user1关联数据库对象
db1=# DROP OWNED BY user1 cascade;
NOTICE:  drop cascades to 9 other objects
DETAIL:  drop cascades to column postal of table user2.us_snail_addy
drop cascades to column status of table user2.bug
drop cascades to server rhnsrv
drop cascades to user mapping for user2 on server rhnsrv
drop cascades to user mapping for user3 on server rhnsrv
drop cascades to foreign table stu
drop cascades to foreign table test
drop cascades to foreign table tp1
drop cascades to foreign table tp3
DROP OWNED
##再次尝试删除用户user1，提示user1还拥有表空间和数据库
db1=# drop user user1;
ERROR:  role "user1" cannot be dropped because some objects depend on it
DETAIL:  owner of database db1
owner of tablespace tbs1
## 使用ressign owned转移db1和tbs1给user2用户
db1=# REASSIGN OWNED BY user1 TO user2;
REASSIGN OWNED
##用户拥有的对象和权限都清理完毕后，删除用户命令成功完成
db1=# drop user user1;
DROP ROLE
db1=#