PostgreSQL数据库常用命令

一、数据库常用命令

1
.登录 PostgreSQL 数据库: psql -U postgres -h localhost -p 5432 mydatabase 2.登陆到指定数据库 psql -U root -d mydatabase; 3.查看表和数据: 3.1 列出所有数据库: \l 3.2 连接到数据库: \c mydatabase 3.3 列出当前数据库的所有表: \dt 3.4 查看某个表的内容: SELECT * FROM mytable LIMIT 10; 3.5 退出 psql: \q

3.6 列出所有用户
\du

3.7 创建用户并设置密码
CREATE USER postgres WITH PASSWORD 'your_password';

3.8 修改指定用户的密码
ALTER USER username WITH PASSWORD 'new_password';
4.备份数据库并包含创建数据库命令: pg_dump -U postgres -h localhost -p 5432 -F c -b -v -C -f /path/to/backup/aibox_cloud.backup mydatabase 参数解释: pg_dump:用于备份 PostgreSQL 数据库的工具。 -U postgres:指定数据库用户名为 postgres。 -h localhost:指定数据库服务器的主机名为 localhost。 -p 5432:指定数据库服务器的端口号为 5432-F c:指定备份文件格式为自定义格式(custom)。这种格式支持压缩和并行恢复。 -b:包含大对象(blobs)在备份中。 -v:启用详细模式,显示备份过程中的详细信息(verbose)。 -C:在备份文件中包含创建数据库的命令(--create)。 -f /path/to/backup/aibox_cloud.backup:指定输出备份文件的路径和文件名为 /path/to/backup/aibox_cloud.backup。 mydatabase:要备份的数据库名。 5.恢复包含创建数据库命令的备份文件: pg_restore -U postgres -h localhost -p 5432 -C -d postgres -v /path/to/backup/aibox_cloud.backup 参数解释: pg_restore:用于恢复由 pg_dump 创建的备份文件的工具。 -U postgres:指定数据库用户名为 postgres。 -h localhost:指定数据库服务器的主机名为 localhost。 -p 5432:指定数据库服务器的端口号为 5432-C:在恢复过程中创建数据库。如果备份文件中包含了创建数据库的命令(--create)。 -d postgres:指定连接的目标数据库。在使用 -C 选项时,这个数据库仅用作连接,并在其中执行创建和恢复新数据库的操作。通常使用默认的 postgres 数据库。 -v:启用详细模式,显示恢复过程中的详细信息(verbose)。 /path/to/backup/aibox_cloud.backup:要恢复的备份文件的路径和文件名。

 

二、docker容器里禁止直接登陆pg数据库,修改为登陆时输入密码

1.说明:在容器中无需密码即可登录 PostgreSQL 数据库的原因通常与 PostgreSQL 的身份验证方法配置有关。在 PostgreSQL 的配置文件中,可能设置了信任(trust)模式,允许在特定条件下无需密码即可访问数据库。

2.PostgreSQL 身份验证方法
PostgreSQL 支持多种身份验证方法,包括:

trust:允许无密码登录。
password:要求密码登录。
md5:要求 MD5 加密的密码。
peer:仅允许本地 Unix 用户登录。
ident:根据客户端的操作系统用户名进行认证。
这些配置通常在 PostgreSQL 配置文件 pg_hba.conf 中设置。该文件通常位于 PostgreSQL 数据目录下。

3. 修改 pg_hba.conf 配置文件
找到并编辑 pg_hba.conf 文件,将所有 trust方法修改为 password。该文件通常位于 PostgreSQL 数据目录下,可以通过如下方式找到:
sudo find / -name pg_hba.conf

以下是一个示例配置:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     password
# IPv4 local connections:
host    all             all             127.0.0.1/32            password
# IPv6 local connections:
host    all             all             ::1/128                 password
# Allow replication connections from localhost, by a user with the replication privilege.
local   replication     all                                     password
host    replication     all             127.0.0.1/32            password
host    replication     all             ::1/128                 password

4. 重启 PostgreSQL 服务
修改 pg_hba.conf 文件后,需要重启 PostgreSQL 服务以使更改生效。

对于系统服务
systemctl restart postgresql

对于容器中
如果你在 Docker 容器中运行 PostgreSQL,可以重启容器:
docker restart my_postgres_container

5. 设置 PostgreSQL 用户密码
确保你已经为 postgres 用户设置了密码。

切换到 postgres 用户:
sudo -i -u postgres

进入 psql 命令行界面:
psql

设置密码:
ALTER USER postgres PASSWORD 'your_password';
退出 psql:
\q

6. 登录 PostgreSQL 数据库
以下是几种通过命令行提供密码的方法。

方法 1:使用环境变量
使用 PGPASSWORD 环境变量来指定密码。
export PGPASSWORD='your_password'
psql -U postgres -h localhost -p 5432 -d your_database

方法 2:使用 .pgpass 文件
创建一个 .pgpass 文件,在其中保存数据库连接的配置信息和密码。

创建或编辑 .pgpass 文件:
nano ~/.pgpass
添加以下内容(每一行格式为:hostname:port:database:username:password):

localhost:5432:your_database:postgres:your_password
设置 .pgpass 文件的权限,使其只有用户自己可以读取:

chmod 600 ~/.pgpass
使用 psql 连接到数据库(psql 会自动读取 .pgpass 文件中的信息):

psql -U postgres -h localhost -p 5432 -d your_database

方法 3:通过命令行参数传递密码
在命令行中使用 PGPASSWORD 环境变量临时传递密码(适合一次性使用):

PGPASSWORD='your_password' psql -U postgres -h localhost -p 5432 -d your_database
通过这些步骤,你可以配置 PostgreSQL 使用 password 认证方法,并在登录时提供密码。

 

三、给用户设置访问权限
要设置 PostgreSQL 中的权限以确保用户 myuser 仅能连接到数据库 mydatabase,你需要配置以下几个方面:

创建用户和数据库(如果尚未创建)
配置数据库权限
确保数据库访问控制规则设置正确
1. 创建用户和数据库
如果你尚未创建用户 myuser 和数据库 mydatabase,可以使用以下命令创建:

创建用户

CREATE USER myuser WITH PASSWORD 'myuser_password';
创建数据库

CREATE DATABASE mydatabase;
将用户 myuser 授予数据库 mydatabase 的访问权限

GRANT CONNECT ON DATABASE mydatabase TO myuser;
2. 配置表和其他权限
授予对特定表的访问权限
如果数据库中有表,你可以授予用户对这些表的特定权限:


\c mydatabase   -- 连接到 mydatabase 数据库

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;
SELECT: 读取数据
INSERT: 插入数据
UPDATE: 更新数据
DELETE: 删除数据
授予对特定模式的访问权限
如果你只想授予 myuser 对特定模式下的表的权限,你可以如下配置:


GRANT USAGE ON SCHEMA public TO myuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;
3. 确保访问控制设置正确
编辑 pg_hba.conf 文件
确保 pg_hba.conf 文件中的访问控制规则设置正确,允许 myuser 从指定 IP 地址或网段连接到 mydatabase 数据库。


# 允许 myuser 从特定 IP 地址连接到 mydatabase 数据库
host    mydatabase      myuser           192.168.1.0/24            md5
4. 重启 PostgreSQL 服务
修改 pg_hba.conf 文件后,需要重启 PostgreSQL 服务:


sudo systemctl restart postgresql
或者,如果你在 Docker 容器中运行 PostgreSQL,重启容器:


docker restart my_postgres_container
总结
创建用户和数据库(如果尚未创建):

CREATE USER myuser WITH PASSWORD 'myuser_password';
CREATE DATABASE mydatabase;
GRANT CONNECT ON DATABASE mydatabase TO myuser;
配置权限:

\c mydatabase
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;
编辑 pg_hba.conf 文件:

host    mydatabase      myuser           192.168.1.0/24            md5
重启 PostgreSQL 服务。

通过这些步骤,你可以确保用户 myuser 只能够连接到数据库 mydatabase 并具有适当的权限

 

posted @ 2024-07-24 10:37  Leonardo-li  阅读(2655)  评论(0编辑  收藏  举报