PostgreSQL安装与权限管理
安装
官方文档
https://www.postgresql.org/download/linux/redhat/www.postgresql.org选型
- 版本 12
- 平台 CentOS 7
- 选择处理器 x86_64
以下操作需要root权限
安装yum源
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
安装client
yum install postgresql12
安装server
yum install postgresql12-server
初始化并启动数据库
/usr/pgsql-12/bin/postgresql-12-setup initdb
systemctl enable postgresql-12
systemctl start postgresql-12
默认用户
在安装过程中,会自动创建系统用户postgres,postgresql内置的超级用户也是postgres。 需要注意系统用户和数据库用户没有直接关系。
修改密码
切换至postgres用户
su postgres
连接数据库
psql
修改密码,将*号处替换为真实密码
postgres=# ALTER ROLE postgres WITH PASSWORD '*';
ALTER ROLE
修改认证方式
将认证方式修改为密码认证,打开
/var/lib/pgsql/12/data/pg_hba.conf
不同平台该文件位置可能不同,如果不在该位置,可以查找目录
/etc/postgresql/
将默认的peer方式
local all all peer
host all all 127.0.0.1/32 peer
host all all 127.0.0.1/32 peer
修改为md5方式
local all all md5
host all all 127.0.0.1/32 md5
host all all 127.0.0.1/32 md5
重载配置
# SELECT pg_reload_conf();
创建用户及数据库
连接数据库
$ psql -U postgres -W
Password:
psql (12.1) Type "help" for help.
postgres=#
创建用户
postgres=# CREATE ROLE admin WITH PASSWORD '123456';
CREATE ROLE
创建数据库。注意:数据库名必须小写!
postgres=# create database admindb;
CREATE DATABASE
连接数据库
使用admin用户连接admindb
$ psql -U admin -W -d admindb
Password:
psql: error: could not connect to server: FATAL: role "admin" is not permitted to log in
错误原因为没有LOGIN权限,需要由postgres用户授予权限
postgres=# ALTER ROLE admin WITH LOGIN;
ALTER ROLE
再次连接,可以看到成功了
$ psql -U admin -W -d admindb
Password:
psql (12.1) Type "help" for help.
admindb=>
创建数据表
admin用户连接,可以看出admin用户默认拥有建表权限。
admindb=> CREATE TABLE books (id SERIAL UNIQUE, name VARCHAR(100), author VARCHAR(30));
CREATE TABLE
postgres用户连接
admindb=# CREATE TABLE authors (id SERIAL UNIQUE, name varchar(30));
CREATE TABLE
数据表权限
admin用户,INSERT INTO authors,提示没有表权限
admindb=> INSERT INTO authors (name) VALUES ('Mo Yan');
ERROR: permission denied for table authors
postgres用户,授予表权限
admindb=# GRANT ALL PRIVILEGES ON TABLE authors TO admin;
GRANT
admin用户,INSERT INTO authors,提示没有sequence权限
admindb=> INSERT INTO authors (name) VALUES ('Mo Yan');
ERROR: permission denied for sequence authors_id_seq
postgres用户,授予sequence权限
admindb=# GRANT ALL PRIVILEGES ON SEQUENCE authors_id_seq TO admin; GRANT
admin用户,执行INSERT,写入成功
admindb=> INSERT INTO authors (name) VALUES ('Mo Yan');
INSERT 0 1
上文中的 authors_id_seq 用于获取 id SERIAL 的下一个值,我们的INSERT语句没有指定id,所以需要该权限获取自动生成的值。
可以看出,Postgresql对表权限的管理非常细致严密。
Database,Schema,Table
这三者是数据库中很重要的概念,它们的层级关系为database -> schema -> table。
database是数据库,schema可以看作数据库中的命名空间,table是具体的数据表,每个table都归属于一个schema。不同的schema下可以有相同名称的数据表。
每个数据库都有一个默认的public schema,我们在创建数据表时,如果不指定schema,则会自动创建在public下。
查看数据库所有表
admindb=> dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | authors | table | postgres
public | books | table | admin
可以看出每个表的owner、schema,其中owner就是创建该表的用户。
schema、table
postgres用户,创建schema
admindb=# CREATE SCHEMA relation_schema;
CREATE SCHEMA
在relation_schema下创建与public下同名的表books
admindb=# CREATE TABLE relation_schema.books (id SERIAL UNIQUE, name varchar(30), author varchar(30));
CREATE TABLE
查看所有schema
admindb=# dnS
List of schemas
Name | Owner
--------------------+----------
information_schema | postgres
pg_catalog | postgres
pg_temp_1 | postgres
pg_toast | postgres
pg_toast_temp_1 | postgres
public | postgres
relation_schema | postgres
查看public下所有表
admindb=# dt public.*
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | authors | table | postgres
public | books | table | admin
查看relation_schema下所有表,可以看出不同schema下可以创建相同名称的数据表
admindb=# dt relation_schema.*
List of relations
Schema | Name | Type | Owner
-----------------+-------+-------+----------
relation_schema | books | table | postgres
参考资料
身份认证失败解决
Chapter 20. Client Authenticationwww.postgresql.org Getting error: Peer authentication failed for user "postgres", when trying to get pgsql working with railsstackoverflow.compeer方式会将当前系统用户作为数据库用户,如当前系统用户为postgres,数据库同样存在该用户,则认证通过,否则认证失败
权限管理
mydb2=> dS
List of relations
Schema | Name | Type | Owner
public | books | table | postman
public | books_id_seq | sequence | postman
public | girls | table | postgres
public | girls_id_seq | sequence | postgres
mydb2=> dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
public | books | table | postman
public | girls | table | postgres
## books由postman创建,girls由postgres创建
## postman没有girls操作权限
[limen@qd-centos7 ~]$ psql -U postman -W -d mydb2
Password:
psql (12.1)
Type "help" for help.
mydb2=> select * from girls;
ERROR: permission denied for table girls
## postgres拥有两张表的权限
[limen@qd-centos7 ~]$ psql -U postgres -W -d mydb2
Password:
psql (12.1)
Type "help" for help.
mydb2=# select * from books;
id | name
----+-------------
1 | Don Quijote
(1 row)
## 列出所有库
postgres=# l
List of databases
-[ RECORD 1 ]-----+----------------------
Name | mydb
Owner | postgres
Encoding | UTF8
Collate | en_US.UTF-8
Ctype | en_US.UTF-8
Access privileges |
-[ RECORD 2 ]-----+----------------------
Name | mydb2
Owner | postgres
Encoding | UTF8
Collate | en_US.UTF-8
Ctype | en_US.UTF-8
Access privileges |
## 可以看到postgres是mydb和mydb2的owner
授予权限
https://www.postgresql.org/docs/12/sql-grant.html
https://www.postgresql.org/docs/12/ddl-priv.html
## postman
mydb2=> select * from girls;
ERROR: permission denied for table girls
## postgres将girls表SELECT权限授予postman
mydb2=# GRANT SELECT ON girls TO postman;
GRANT
## postman
mydb2=> select * from girls;
id | name
----+------
(0 rows)
mydb2=> insert into girls (name) values ('Alice');
ERROR: permission denied for table girls
## postgres
mydb2=# GRANT INSERT ON girls TO postman;
GRANT
## postman
mydb2=> insert into girls (name) values ('Alice');
ERROR: permission denied for sequence girls_id_seq
## postgres
mydb2=# GRANT ALL ON SEQUENCE girls_id_seq TO postman;
GRANT
## postman
mydb2=> insert into girls (name) values ('Alice');
INSERT 0 1
## postgres授予mydb2所有权限
mydb2=# GRANT ALL ON DATABASE mydb2 TO postman;
GRANT
mydb2=# l
List of databases
-[ RECORD 1 ]-----+----------------------
Name | mydb
Owner | postgres
Encoding | UTF8
Collate | en_US.UTF-8
Ctype | en_US.UTF-8
Access privileges |
-[ RECORD 2 ]-----+----------------------
Name | mydb2
Owner | postgres
Encoding | UTF8
Collate | en_US.UTF-8
Ctype | en_US.UTF-8
Access privileges | =Tc/postgres +
| postgres=CTc/postgres+
| postman=CTc/postgres
mydb2=# create table boys (id serial unique, name varchar(30));
CREATE TABLE
## postman 没权限访问postgres创建的表
mydb2=> select * from boys;
ERROR: permission denied for table boys
## postgres
mydb2=# ALTER TABLE boys OWNER TO postman;
ALTER TABLE
## postman
mydb2=> select * from boys;
id | name
----+------
(0 rows)