install postgesql 16 on ubuntu with multipass on macOS 14.2.1

blanks2020
165 阅读4分钟

install postgresql

ref: www.postgresql.org/download/li…

sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'  
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -  
sudo apt-get update  
sudo apt-get -y install postgresql

sudo vim /etc/postgresql/16/main/postgresql.conf

#  /etc/postgresql/16/main/postgresql.conf  允许远程访问
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses = '*'          # what IP address(es) to listen on;

# /etc/postgresql/16/main/pg_hba.conf

sudo vim /etc/postgresql/16/main/pg_hba.conf # add 0.0.0.0/0  this line

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0            scram-sha-256


sudo systemctl start postgresql  # 启动 postgresql

sudo systemctl enable postgresql  # 添加启动 postgresql

sudo systemctl status postgresql  # 查看 postgresql 启动状态

sudo -i -u postgres  # 切换到 postgres 账户

postgres@primary:~$ psql
psql (16.1 (Ubuntu 16.1-1.pgdg22.04+1))
Type "help" for help.

# 修改密码
postgres=# alter role postgres with password 'postgres';
ALTER ROLE
postgres=# exit

postgres@primary:~$ ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 52:54:00:f1:0f:2a brd ff:ff:ff:ff:ff:ff
    altname enp0s3
    inet 192.168.64.5/24 metric 100 brd 192.168.64.255 scope global dynamic ens3
       valid_lft 84888sec preferred_lft 84888sec
    inet6 fdbc:a69e:9e0b:7fcc:5054:ff:fef1:f2a/64 scope global dynamic mngtmpaddr noprefixroute
       valid_lft 2591984sec preferred_lft 604784sec
    inet6 fe80::5054:ff:fef1:f2a/64 scope link
       valid_lft forever preferred_lft forever

此时在 macOS 应该是无法访问 ubuntu 的ip端口 192.168.64.5：5432

curl -v 192.168.64.5：5432

➜  ~ curl -v 192.168.64.5:5432
*   Trying 192.168.64.5:5432...
* connect to 192.168.64.5 port 5432 failed: Connection refused
* Failed to connect to 192.168.64.5 port 5432 after 1003 ms: Couldn't connect to server
* Closing connection
curl: (7) Failed to connect to 192.168.64.5 port 5432 after 1003 ms: Couldn't connect to server

查看网络

➜  ~ multipass  networks
Name     Type         Description
bridge0  bridge       Network bridge with en1, en2, en3, en4
en0      wifi         Wi-Fi
en1      thunderbolt  Thunderbolt 1
en2      thunderbolt  Thunderbolt 2
en3      thunderbolt  Thunderbolt 3
en4      thunderbolt  Thunderbolt 4

做如下修改 (重点)

multipass set local.bridged-network=en0
➜  ~ multipass get local.bridged-network
en0

multipass restart

再次验证网络

➜  ~ curl -v 192.168.64.5:5432
*   Trying 192.168.64.5:5432...
* Connected to 192.168.64.5 (192.168.64.5) port 5432
> GET / HTTP/1.1
> Host: 192.168.64.5:5432
> User-Agent: curl/8.4.0
> Accept: */*
>
* Empty reply from server
* Closing connection
curl: (52) Empty reply from server

此时使用 pg客户端工具应该就可以连接成功了， image.png

ok 这就是本次环境设置过程了，另外 multipass 内安装的 ubuntu 似乎并没有开启防火墙，如果使用其他虚拟机软件，例如 parallel desktop，utm，virtualbox，vmware 等安装的系统可能会以为默认开启防火墙而不通，ubuntu 使用的 ufw ， centos 使用的 firewall 找对应的介绍资料，将需要的端口添加允许即可。

# ubuntu
sudo ufw status
sudo ufw allow 5432/tcp
sudo ufw status numbered

# centos
sudo firewall-cmd --add-port=5432/tcp --permanent

blog.csdn.net/qq_29761395… cn.linux-console.net/?p=22460#go…

数据库的使用:

创建用户

create database database_name;
create role user_role LOGIN password  'zpasswordP' valid until 'infinity';

postgreSQL 创建用户并授权 blog.csdn.net/geol200709/…

grant all privileges on database mydatabase to user_role;


psql -h 127.0.0.1 -U user_role -d database_name -f ./database_name_dump.sql

备份还原参考： blog.csdn.net/ma286388309…

sudo vi /etc/postgresql/16/main/postgresql.conf 

# 取消 listen_addresses 开头的行注释，用 * 替换 localhost

listen_addresses = '*'          # what IP address(es) to listen on;
sudo service postgresql restart


sudo vi /etc/postgresql/16/main/pg_hba.conf

local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
#local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5  # 允许password 方式访问
host    all             all             0.0.0.0/0            md5    
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

zhuanlan.zhihu.com/p/580030679

postgres赋予角色登录权限

创建的role默认没有登录数据库的权限：

highgo=# create role a with password 'aaa';
CREATE ROLE
highgo=# \c highgo a
致命错误:  不允许角色"a" 进行登录

需要单独赋予角色login权限：

highgo=# alter role a login;
ALTER ROLE
highgo=# \c highgo a
You are now connected to database "highgo" as user "a".
highgo=>

直接创建的user可以登录数据库：

highgo=# create user b with password 'bbb';
CREATE ROLE
highgo=# \c highgo b
You are now connected to database "highgo" as user "b".
highgo=>

将角色赋予login权限后，可以连接登录所有的数据库：

highgo=# alter role a login;
ALTER ROLE
highgo=# \c highgo a
You are now connected to database "highgo" as user "a".
highgo=> \c template1 a
You are now connected to database "template1" as user "a".
template1=> \c
You are now connected to database "template1" as user "a".
template1=> \c highgo highgo
You are now connected to database "highgo" as user "highgo".

回收角色的login权限后，赋予连接某一个数据库的权限后也无法连接数据库：

highgo=# alter role a nologin;
ALTER ROLE
highgo=# grant connect on database template1 to a;
GRANT
highgo=# \c template1 a
致命错误:  不允许角色"a" 进行登录
Previous connection kept

*************************************************************************
highgo=# \c benchmarksql b
致命错误:  不允许角色"b" 进行登录
Previous connection kept
highgo=# grant connect on database benchmarksql to b;
GRANT
highgo=# \c benchmarksql b
致命错误:  不允许角色"b" 进行登录
Previous connection kept
highgo=# alter role b login;
ALTER ROLE
highgo=# \c benchmarksql b
You are now connected to database "benchmarksql" as user "b".
benchmarksql=> \c highgo highgo
You are now connected to database "highgo" as user "highgo".
highgo=# drop role b;
错误:  无法删除"b"因为有其它对象倚赖它
DETAIL:  数据库 benchmarksql的权限
highgo=# drop role b cascade;
错误:  语法错误 在 "cascade" 或附近的
LINE 1: drop role b cascade;
                    ^
highgo=# revoke connect on database benchmarksql from b;
REVOKE
highgo=# drop role b ;
DROP ROLE

版权声明：本文为CSDN博主「lk_db」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：blog.csdn.net/lk_db/artic…

image.png

avatar
文章
阅读
粉丝