1. psql常用命令

<code>登录pg数据库：

psql postgres postgres

psql -h ip -p port daname username

这些连接参数也可以通过环境变量指定：

export PGDATABASE=postgres

export PGHOST=xxx.xxx.xxx.xxx

export PGPORT=5432

export PGUSER=postgres

\c 显示连接信息

\conninfo 显示当前连接的相关信息

1.同一用户切换到其他数据库

\c testdb

2.同一库切换登录用户

\c ‐ user1

单独切换用户，需要加 “-”

3.当前实例同时切换数据库和登录用户

\c postgres postgres

4.切换主机和数据库

\c testdb ‐ xxx.xxx.xxx.xxx

主机参数前面需要加 “-”

5.切换主机、数据库和登录用户(端口为默认)

\c testdb user1 xxx.xxx.xxx.xxx

6.切换主机、数据库和登录用户以及端口

\c postgres user1 xxx.xxx.xxx.xxx

断开连接

ctrl + d

\q

exit

\encoding utf8 指定客户端字符集

常用快捷命令

\l 查看数据库

\db 查看表空间

\d 查看表

\dt 查看表

\di 查看索引

\ds 查看序列

\dv 查看视图

\df 查看函数

\d+查看表详细信息

\d t1 查看表定义

\dt+查看表大小

\di+查看索引大小

\sf 查看函数代码

\dn 查看模式

\du or \dg 查看用户

\dp or \z 查看表权限

格式化输出命令-\pset

\pset border 0： 表示输出内容无边框。

\pset border 1： 表示输出内容只有内边框。也是默认输出格式。

\pset border 2： 表示输出内容内外都有边框。

\pset format unaligned 以分隔符分隔输出结果，默认分隔符是“|”

\pset fieldsep '\t'设置 Tab 分隔符

\t 只显示记录，不显示列字段

\x 切换扩展输出模式

输入\输出

执行 sql 脚本

vi test.sql

create table test(id int);

insert into test values(1);

insert into test values(2);

insert into test values(3);

psql dbname username -f /home/test.sql

\i test.sql

将查询结果写入文件

\o test.sql

将 SQL 命令输出到文件中

\o test.txt

select * from t1;

其他命令

psql -E dbname username 查看快捷命令对应的实际 SQL

\set ECHO_HIDDEN on|off

\timing 记时

\watch 反复执行命令

2.用户管理

语法格式：

create user name 【【with】option [......]】

option 选项：



SUPERUSER|NOSUPERUSER： 表示创建出来的用户是否为超级用户。



CREATEDB|NOCREATEDB： 表示创建出来的用户是否具有 CREATE DATABASE 的

权限。



CREATEROLE|NOCREATEROLE： 表示创建出来的用户是否具有创建其他角色的权

限。



INHERIT|NOINHERIT： 如果创建的用户拥有某个或某几个角色， 这时若指定

INHERIT， 则表示用户自动拥有相应角色的权限， 否则该用户没有相应角色的权限。



LOGIN|NOLOGIN： 表示创建出来的用户是否具有 LOGIN 权限。



REPLICATION | NOREPLICATION：创建出来的用户是否具有复制权限。



BYPASSRLS | NOBYPASSRLS：决定是否一个用户可以绕过每一条行级安全性（RLS）

策略。 默认是 NOBYPASSRLS。



CONNECTION LIMIT connlimit： 指明该用户可以使用的并发连接的数量。 默认值

是“-1”， 表示没有限制。



[ENCRYPTED|UNENCRYPTED]PASSWORD 'password'： 设置密码。



VALID UNTIL 'timestamp'： 密码失效时间， 如果不指定该子句， 那么口令将永远

有效。



IN ROLE role_name [,...]： 指定用户成为哪些角色的成员。



IN GROUP role_name [,...]： 与 IN ROLE 相同， 是已过时的语法。



ROLE role_name [,...]： role_name 将成为这个新建的角色的成员。



ADMIN role_name [,...]： role_name 将有这个新建角色的 WITH ADMIN OPTION

权限。



USER role_name [,...]： 与 ROLE 子句相同， 但已过时。



SYSID uid： 此子句主要是为了 SQL 向下兼容， 实际没有什么用途。

 2.1创建没有密码的用户

create user test;

后续添加密码：

alter user test password 'test';

2.2创建带密的用户

create user test password 'test';

2.3创建有时间限制的用户

create user test password 'test' valid until '2024-1-1 00:00:00';

2.4创建具有创建数据库和管理角色权限的用户

create user test password 'test' CREATEDB CREATEROLE;

2.5创建具有超级权限的用户

create user test password 'test'  SUPERUSER;

2.6创建复制账号

create user repl REPLICATION password 'repl';

2.7修改用户密码

alter user test password 'xxxx';

2.8移除用户密码

alter user test password null;

2.9更改密码失效日期

alter user test VALID UNTIL  '2025-1-1:00:00:00';

2.10 让密码永久生效

alter user user2 VALID UNTIL 'infinity';

2.11修改用户系统权限，让其用户createdb和createrole的权限

alter user test createdb createrole;

2.12删除用户

drop user test;

3.角色管理

语法格式：

CREATE ROLE name 【with option 【...】】

3.1创建角色

create role role1 login createdb createrole;

3.2将角色授权给用户

grant all on t1 to role1;

grant role1 to user1;

3.3修改角色权限

alter role role1 superuser;

3.4删除角色

drop role role1；

4.权限管理

grant 语法：

grant {权限} on {对象} to {角色}

revoke 语法格式：

revoke  {权限} on {对象}  from {角色}

示例：

grant all on t1 to role1;

revoke all on t1  from  role1;

5.查看权限

5.1查看表权限：

5.1.1方式1：\dp

示例，user1=r*w/postgres 指明 user1 是被授权者，r(select)权限、*（with grant option）、w(update)，postgres 授权者

例如，授权 t1 的查询权限给所有人:

GRANT SELECT ON t1 TO PUBLIC;

注意：public 表示所有用户

GRANT SELECT, UPDATE, INSERT,DELETE ON t1 TO user2;

GRANT SELECT (name), UPDATE (age) ON t1 TO user2;

5.1.2方式二：

select * from information_schema.table_privileges where table_name='t1';

5.2查看用户权限

select usename,usecreatedb,usesuper,userepl,usebypassrls,valuntil  from pg_user;