第 23 课 PostgreSQL 创建自己的数据库、模式、用户
时间:2019-05-23 13:06 来源:linux.it.net.cn 作者:IT
1. 修改PostgreSQL数据库默认用户postgres的密码
[root@izwz90tx4egvh4qj3p95vsz ~]# sudo -u postgres psql
psql (10.4)
Type "help" for help.
postgres=# ALTER USER postgres WITH PASSWORD 'duyeweb';
ALTER ROLE
注意：
密码postgres要用引号引起来
命令最后有分号
2. 修改linux系统postgres用户的密码
步骤一：删除用户postgres的密码
[root@izwz90tx4egvh4qj3p95vsz ~]# sudo passwd -d postgres
Removing password for user postgres.
passwd: Success
步骤二：设置用户postgres的密码
[root@izwz90tx4egvh4qj3p95vsz ~]# sudo -u postgres passwd
Changing password for user postgres.
New password:
BAD PASSWORD: The password fails the dictionary check - it does not contain enough DIFFERENT characters
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
3. 使用超级用户postgres创建新数据库和用户
创建数据库duyeweb
[root@izwz90tx4egvh4qj3p95vsz ~]# sudo -u postgres psql
could not change directory to "/root": Permission denied
psql (10.4)
Type "help" for help.
postgres=# create database duyeweb;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
duyeweb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | duye=C/postgres
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
创建新用户duye
设置密码'123456'
赋予登录和创建数据库对象的权限
[root@izwz90tx4egvh4qj3p95vsz ~]# sudo -u postgres psql
could not change directory to "/root": Permission denied
psql (10.4)
Type "help" for help.
postgres=# create user duye;
CREATE ROLE
postgres=# alter user duye password '123456';
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
duye | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# alter user duye createrole createdb replication login;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
duye | Create role, Create DB, Replication | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
修改客户端认证方式：vim /var/lib/pgsql/10/data/pg_hba.conf
修改用户的认证方式为md5：
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 ident
host replication all ::1/128 ident
重新加载配置：
systemctl reload postgresql-10
使用新用户duye登录新的数据库duyeweb
[root@izwz90tx4egvh4qj3p95vsz ~]# psql duyeweb -Uduye -W
Password for user duye:
psql (10.4)
Type "help" for help.
duyeweb=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
duyeweb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | duye=C/postgres
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
duyeweb=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
duye | Superuser, Create role, Create DB, Replication | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
duyeweb=#
4. 创建模式
schema是逻辑别构，将数据库进行逻辑划分。同一数据库下可以有多个schema，不同数据库下的schema互不相关。
查看模式
duyeweb=# select current_schema;
current_schema
----------------
public
(1 row)
duyeweb=# show search_path
search_path
-----------------
"$user", public
(1 row)
duyeweb=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
duyeweb=# select * from pg_catalog.pg_namespace order by 1;
nspname | nspowner | nspacl
--------------------+----------+-------------------------------------
information_schema | 10 | {postgres=UC/postgres,=U/postgres}
pg_catalog | 10 | {postgres=UC/postgres,=U/postgres}
pg_temp_1 | 10 |
pg_toast | 10 |
pg_toast_temp_1 | 10 |
public | 10 | {postgres=UC/postgres,=UC/postgres}
(6 rows)
创建一个模式
duyeweb=# \h create schema
Command: CREATE SCHEMA
Description: define a new schema
Syntax:
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification
where role_specification can be:
user_name
| CURRENT_USER
| SESSION_USER
duyeweb=# CREATE SCHEMA IF NOT EXISTS duyeweb;
CREATE SCHEMA
duyeweb=# \dn
List of schemas
Name | Owner
---------+----------
duyeweb | duye
public | postgres
(2 rows)
(责任编辑：IT)
| 1. 修改PostgreSQL数据库默认用户postgres的密码 [root@izwz90tx4egvh4qj3p95vsz ~]# sudo -u postgres psql psql (10.4) Type "help" for help. postgres=# ALTER USER postgres WITH PASSWORD 'duyeweb'; ALTER ROLE 注意： 密码postgres要用引号引起来 命令最后有分号 2. 修改linux系统postgres用户的密码 步骤一：删除用户postgres的密码 [root@izwz90tx4egvh4qj3p95vsz ~]# sudo passwd -d postgres Removing password for user postgres. passwd: Success 步骤二：设置用户postgres的密码 [root@izwz90tx4egvh4qj3p95vsz ~]# sudo -u postgres passwd Changing password for user postgres. New password: BAD PASSWORD: The password fails the dictionary check - it does not contain enough DIFFERENT characters New password: Retype new password: passwd: all authentication tokens updated successfully. 3. 使用超级用户postgres创建新数据库和用户 创建数据库duyeweb [root@izwz90tx4egvh4qj3p95vsz ~]# sudo -u postgres psql could not change directory to "/root": Permission denied psql (10.4) Type "help" for help. postgres=# create database duyeweb; CREATE DATABASE postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- duyeweb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres + | | | | | postgres=CTc/postgres+ | | | | | duye=C/postgres template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres 创建新用户duye 设置密码'123456' 赋予登录和创建数据库对象的权限 [root@izwz90tx4egvh4qj3p95vsz ~]# sudo -u postgres psql could not change directory to "/root": Permission denied psql (10.4) Type "help" for help. postgres=# create user duye; CREATE ROLE postgres=# alter user duye password '123456'; ALTER ROLE postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- duye | | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} postgres=# alter user duye createrole createdb replication login; ALTER ROLE postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- duye | Create role, Create DB, Replication | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} 修改客户端认证方式：vim /var/lib/pgsql/10/data/pg_hba.conf 修改用户的认证方式为md5： # "local" is for Unix domain socket connections only local all all md5 # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5 # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer host replication all 127.0.0.1/32 ident host replication all ::1/128 ident 重新加载配置： systemctl reload postgresql-10 使用新用户duye登录新的数据库duyeweb [root@izwz90tx4egvh4qj3p95vsz ~]# psql duyeweb -Uduye -W Password for user duye: psql (10.4) Type "help" for help. duyeweb=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- duyeweb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres + | | | | | postgres=CTc/postgres+ | | | | | duye=C/postgres template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows) duyeweb=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- duye | Superuser, Create role, Create DB, Replication | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} duyeweb=# 4. 创建模式 schema是逻辑别构，将数据库进行逻辑划分。同一数据库下可以有多个schema，不同数据库下的schema互不相关。 查看模式 duyeweb=# select current_schema; current_schema ---------------- public (1 row) duyeweb=# show search_path search_path ----------------- "$user", public (1 row) duyeweb=# \dn List of schemas Name | Owner --------+---------- public | postgres (1 row) duyeweb=# select * from pg_catalog.pg_namespace order by 1; nspname | nspowner | nspacl --------------------+----------+------------------------------------- information_schema | 10 | {postgres=UC/postgres,=U/postgres} pg_catalog | 10 | {postgres=UC/postgres,=U/postgres} pg_temp_1 | 10 | pg_toast | 10 | pg_toast_temp_1 | 10 | public | 10 | {postgres=UC/postgres,=UC/postgres} (6 rows) 创建一个模式 duyeweb=# \h create schema Command: CREATE SCHEMA Description: define a new schema Syntax: CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ] CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ] CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ] CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification where role_specification can be: user_name | CURRENT_USER | SESSION_USER duyeweb=# CREATE SCHEMA IF NOT EXISTS duyeweb; CREATE SCHEMA duyeweb=# \dn List of schemas Name | Owner ---------+---------- duyeweb | duye public | postgres (2 rows) (责任编辑：IT) |