pgsql 配置搭建和权限管理

安装pgsql

yum安装并启动

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y postgresql12-server
/usr/pgsql-12/bin/postgresql-12-setup initdb
systemctl enable postgresql-12
systemctl start postgresql-12

修改数据目录

#vim /usr/lib/systemd/system/postgresql-12.service
#Environment=PGDATA=/usr/local/pgsql/12/data/
sed -i "s/var\/lib/usr\/local/" /usr/lib/systemd/system/postgresql-12.service
mkdir -pv  /usr/local/pgsql/12/
mv /var/lib/pgsql/12/data  /usr/local/pgsql/12
systemctl daemon-reload
systemctl restart postgresql-12

配置环境变量

cat >> /etc/profile <<EOF  
###### pgsql env start ######
export PG_HOME=/usr/pgsql-12
export PGDATA=/usr/local/pgsql/12/data
export PATH=\$PG_HOME/bin:\$PATH
export LD_LIBRARY_PATH=\$PG_HOME/lib
export MANPATH=\$PG_HOME/share/man:\$MANPATH
###### pgsql env end ######
EOF

source /etc/profile

修改配置

vim $PGDATA/postgresql.conf
#数据库服务监听IP地址，默认只监听localhost，外部无法访问。修改为 *，允许外部访问数据库服务
listen_addresses = '*'
#数据库服务监听端口
port = 5432
#默认100，连接数限制根据实际业务需求修改
max_connections = 5000
vim $PGDATA/pg_hba.conf
#末尾添加下面类容，不限制任何主机并允许远程登录：
host    all             all             0.0.0.0/0               md5
systemctl  restart postgresql-12

设置密码

su - postgres
psql
alter user postgres with encrypted password 'password';
create database "aquarius";

 pgsql权限管理

数据库授权

grant all on database "aquarius" to test;
\c aquarius;
grant all on all tables in schema public to test; 
grant usage, select on all sequences in schema public to test;

取消授权

#取消数据库授权：
revoke all on database aquarius from test; 

#取消表授权：
\c aquarius;
revoke all on all tables in schema public from test; 
revoke  all on all  SEQUENCES IN SCHEMA public from  test;

删除角色

#取消数据库授权：
revoke all on database aquarius from test; 
revoke all on database postgres from test;

#取消表授权：
\c aquarius;
revoke all on all tables in schema public from test; 
revoke  all on all  SEQUENCES IN SCHEMA public from  test;
drop owned by test cascade;

\c postgres;
revoke all on all tables in schema public from test; 
revoke  all on all  SEQUENCES IN SCHEMA public from  test;
drop owned by test cascade;
drop user test;