前言
… …
postgresql_4">安装postgresql数据库
centos7.x系统
# Install the repository RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Install PostgreSQL:
sudo yum install -y postgresql13-server
# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
sudo systemctl enable postgresql-13
sudo systemctl start postgresql-13
centos8系统
# Install the repository RPM:
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Disable the built-in PostgreSQL module:
sudo dnf -qy module disable postgresql
# Install PostgreSQL:
sudo dnf install -y postgresql13-server
# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
Initializing database ... OK
sudo systemctl enable postgresql-13
sudo systemctl start postgresql-13
使用方法
- 注: 以下为服务器上操作
# 登录postgresql终端
[root@localhost /]# sudo -i -u postgres
[postgres@localhost ~]$
# 登录postgres用户
[postgres@localhost ~]$ psql postgres postgres
psql (13.11)
Type "help" for help.
postgres=#
# 修改默认用户的密码
postgres=# \password postgres
Enter new password for user "postgres":
Enter it again:
# 创建数据库
postgres=# CREATE DATABASE department
# 切换数据库
postgres-# \c department
You are now connected to database "department" as user "postgres".
# 创建表department
department-# CREATE TABLE DEPARTMENT(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
);
CREATE TABLE
# 查看表格是否创建成功
department=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | department | table | postgres
(1 row)
# 查看表格信息
department=# \d department
Table "public.department"
Column | Type | Collation | Nullable | Default
--------+---------------+-----------+----------+---------
id | integer | | not null |
dept | character(50) | | not null |
emp_id | integer | | not null |
Indexes:
"department_pkey" PRIMARY KEY, btree (id)
# 插入数据
department=# INSERT INTO DEPARTMENT ( ID , DEPT , EMP_ID ) VALUES ( 1 , 'Paul' , 32 );
INSERT 0 1
# 创建只读用户
1. 创建一个用户名为test,密码为devops的用户
department=# CREATE USER test WITH ENCRYPTED PASSWORD 'devops';
CREATE ROLE
2. 修改用户只读事务属性
department=# ALTER USER test SET default_transaction_read_only=on;
ALTER ROLE
3. 设置USAGE权限给到test
department=# GRANT USAGE ON SCHEMA public to test;
GRANT
4. 在对应的数据库中,授予所有表权限,如select。
GRANT SELECT ON ALL TABLES IN SCHEMA public TO test;
4. 在对应的数据库中,授予单个表权限,如select。
GRANT SELECT ON DEPARTMENT TO test;
GRANT SELECT ON DEPARTMENT.* IN SCHEMA public TO test;
# 查询所有表
sql> select * from pg_tables;
... ...
结语
PostgreSQL Yum Repository Install
PostgreSQL Tutorial