标签 Postgres 下的文章

安装Postgres

Docker部署

  1. 拉取镜像

    [root@ecs ~]# docker pull registry.cn-shanghai.aliyuncs.com/maisi/postgres:16.13-alpine
[root@ecs ~]# docker tag registry.cn-shanghai.aliyuncs.com/maisi/postgres:16.13-alpine postgres:16.13-alpine
[root@ecs ~]# docker rmi registry.cn-shanghai.aliyuncs.com/maisi/postgres:16.13-alpine

  2. 创建容器

    [root@ecs ~]# docker run --name postgres \
--privileged=true \
-e POSTGRES_PASSWORD=wfb88Os2xBk0sUQH \
-p 5432:5432 \
-v /data/postgresql:/var/lib/postgresql/data \
-d postgres:16.13-alpine

PSQL操作

  • 登录Postgres

    [root@ecs ~]# docker exec -it postgres /bin/bash
dc4d208b728b:/# psql -U postgres;
psql (16.13)
Type "help" for help.

postgres=#

    常见指令说明：

    命令说明
    \l,\l+列出所有数据库
    \du列出所有用户
    \c [db-name]切换数据库
    \d列出数据库内所有内容，包含table、sequence等
    \dt列出数据库内所有表
    \d [table-name]查看表结构
    \q退出登录

  • 创建用户

    postgres=# create user maisi with password 'Ri71kHISuS9mDVBF';
CREATE ROLE

    或者：

    postgres=# create role maisi password 'Ri71kHISuS9mDVBF' login;
    CREATE USER is now an alias for CREATE ROLE. The only difference is that when the command is spelled CREATE USER, LOGIN is assumed by default, whereas NOLOGIN is assumed when the command is spelled CREATE ROLE.

    create user默认有login权限，而create role默认没有login权限，需要单独指定。

  • 删除用户

    postgres=# drop user maisi;

  • 创建数据库

    postgres=# create database maisi_db with owner maisi;
CREATE DATABASE

  • 授权数据库

    postgres=# grant all on database maisi_db to maisi;
GRANT

    这是数据库级别的授权，权限包括：CONNECT（允许连接数据库）、CREATE（允许在数据库创建schema）和TEMPORARY（允许在数据库创建临时表），不会授予任何表的权限，即只能连上数据库，看不到、也操作不了数据库里面的任何表。

  • 授权表

    postgres=# \c maisi_db;
You are now connected to database "maisi_db" as user "postgres".
maisi_db=# grant select,insert,update,delete on all tables in schema public to maisi;
GRANT

    这是表级别的授权，作用对象是public schema下的已存在的所有表。权限包括：SELECT、INSERT、UPDATE和DELETE，不包含TRUNCATE、REFERENCES、TRIGGER等权限。

    注：只对当前已存在的表生效，新建的表不会自动获得授权。

    自动授权新建表

    默认情况下，maisi用户新创建的表，maisi自动拥有所有权限，但如果是其他用户创建的表，maisi用户就没有操作权限了。可以通过alter default privileges将其他用户新创建的表自动授权给maisi用户。

    postgres=# \c maisi_db;
maisi_db=# alter default privileges in schema public grant select,insert,update,delete on tables to maisi;
ALTER DEFAULT PRIVILEGES
maisi_db=# \ddp
         Default access privileges
 Owner | Schema | Type | Access privileges 
-------+--------+------+-------------------
(0 rows)

    maisi（被授权用户）=a（INSERT）r（SELECT）w（UPDATE）d（DELETE）/postgres（授权用户）：postgres用户在maisi_db数据库下创建的表，自动将arwd权限授权给maisi用户。

  • 撤回表授权

    postgres=# revoke select,insert,update,delete on all tables in schema public from maisi;
REVOKE

  • 撤回数据库授权

    postgres=# revoke all on database maisi_db from maisi;
REVOKE
postgres=# revoke all on database maisi_db from public;
REVOKE

    注：如果只是revoke from maisi，依旧可以使用psql -U maisi -d maisi_db;登录数据库，因为数据库默认授权给public角色CONNECT权限，必须revoke from public后才可以彻底拒绝maisi用户登录maisi_db数据库。

  • 登录数据库

    maisi_db=# \q
dc4d208b728b:/# psql -U maisi -d maisi_db;
psql (16.13)
Type "help" for help.

maisi_db=>

    注：不指定数据库的情况下，默认会登录到和用户名同名的数据库。如果数据库名称和用户名不一致，必须指定数据库名称，否则会报错。

  • 创建表

    maisi_db=> create table "user"(
  id serial primary key,
  username varchar(255) unique not null,
  password varchar(50) not null,
  created_at timestamp with time zone default current_timestamp,
  updated_at timestamp with time zone default current_timestamp
);
CREATE TABLE

  • 插入记录

    maisi_db=> insert into "user"(username,password) values('root','password');
INSERT 0 1

  • 导入sql

    dc4d208b728b:/# cat > /tmp/user.sql << 'EOF'
drop table if exists "user";

create table "user" (
  id serial primary key,
  username varchar(255) unique not null,
  password varchar(60) not null,
  created_at timestamp with time zone default current_timestamp,
  updated_at timestamp with time zone default current_timestamp
);

create index user_id_idx on "user"(id);

insert into "user"(username, password) values ('admin', '$2b$10$BUli0c.muyCW1ErNJc3jL.vFRFtFJWrT8/GcR4A.sUdCznaXiqFXa');
EOF
dc4d208b728b:/# psql -h 127.0.0.1 -p 5432 -U maisi -d maisi_db -W -f /tmp/user.sql

    注：shell中``表示执行命令，并将命令输出替换当前位置，如`user`是执行user命令，但linux没有user命令，因此就会报错。如果需要保留`user`，只需将EOF改成'EOF'即可。

    在postgres中，user是保留关键字，不能直接用来作为表名，如必须使用user作为表名，必须使用""强制转义。

  • 查询记录

    maisi_db=> select * from "user";

  • 删除表

    maisi_db=> drop table "user";
DROP TABLE

  • 修改数据库所有者

    maisi_db=> \q
dc4d208b728b:/# psql -U postgres;
psql (16.13)
Type "help" for help.

postgres=# alter database maisi_db owner to postgres;
ALTER DATABASE
postgres=# select datname,rolname from pg_database d join pg_roles r on d.datdba=r.oid order by d.datname;
  datname  | rolname  
-----------+----------
 postgres  | postgres
 template0 | postgres
 template1 | postgres
 maisi_db  | postgres
(4 rows)

    注：修改数据库的所有者后，使用psql -U maisi -d maisi_db;仍可以登录，而且依旧可以用maisi用户对表进行SELECT、INSERT、UPDATE和DELETE操作，这是因为数据库默认会将CONNECT权限授予给PUBLIC角色（所有用户），而且之前grant select,insert,update,delete on all tables in schema public to maisi将表操作权限授予给maisi用户，因此即使更改数据库的所有者，也不影响表授权。

  • 删除数据库

    postgres=# drop database maisi_db;
DROP DATABASE

    注：如果删除的数据库是当前登录的数据库，则必须先试用\q退出登录，然后登录到postgres后再删除数据库。

命令行操作

  • 创建数据库

    https://www.postgresql.org/docs/current/app-createdb.html

    语法：createdb [connection-option...] [option...] [dbname [description]]

    dc4d208b728b:/# createdb -h 127.0.0.1 -p 5432 -U postgres -e maisi_db "maisi"
SELECT pg_catalog.set_config('search_path', '', false);
CREATE DATABASE maisi_db;
COMMENT ON DATABASE maisi_db IS 'maisi';
    参数值描述
    -h host指定服务器主机名
    -p port指定服务器监听端口
    -U username连接数据库用户名
    -w忽略输入密码（前提是没有设置用户密码）
    -W连接时强制要求输入密码
    非必须，如果用户设置了密码，createdb会自动提示输入密码。但是createdb会先尝试一次连接才发现服务器需要验证密码，造成额外的连接尝试
    -D tablespace指定数据库默认表空间
    -e,--echo显示创建过程中的交互信息
    -E encoding指定数据库编码
    -l locale指定数据库语言

  • 删除数据库

    https://www.postgresql.org/docs/current/app-dropdb.html

    语法：dropdb [connection-option...] [option...] [dbname]

    dc4d208b728b:/# dropdb -h 127.0.0.1 -p 5432 -U postgres maisi_db