postgresql知识大全

devops_sre

已于 2023-10-07 09:01:44 修改

阅读量355

点赞数

文章标签： postgresql 数据库

于 2023-07-01 09:42:01 首次发布

本文链接：https://blog.csdn.net/weixin_42715225/article/details/131487301

版权

前言

… …

安装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;

# 查看每个表用户对应权限
sql> select * from information_schema.usage_privileges;
... ...