Postgresql 学习笔记(1)

前端之家收集整理的这篇文章主要介绍了Postgresql 学习笔记(1)前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

一、环境

#配置远程连接

supostgres
vim/var/lib/pgsql/9.4/data/postgresql.conf编辑配置文件
listen_address=’localhost’前面的注释#去掉,并把’localhost’该为’*’;
vim/etc/postgresql/8.2/main/pg_hba.conf
hostallall192.168.1.0/24passwordpassword可以设置为trust
/etc/init.d/postgresql-8.2restart重启服务:还有stopstart命令一样的。如果配置错误可能导致无法重启

二、语法:

psql-Upostgres#进入数据库
\l#查看有哪些数据库
\cpostgresql#选择postgresql这个数据库,会提示进入连接
\dt#查看所有表
\dtablename#查看某张表结构
\h#查看帮助

三、创建只读用户

#1.创建表
createtablet1(idserial,namevarchar(64));
CREATETABLE
postgres=#\dtListofrelations
Schema|Name|Type|Owner
--------+------+-------+----------
public|t1|table|postgres
(1row)
2.创建用户u1
createroleu1withloginpassword'123456';#login是赋予登录权限,否则是不能登录的
CREATEROLE
3.赋予u1对表的只读权限(因为创建的普通用户默认是没有任何权限的)
postgres=#\c-u1
FATAL:PeerauthenticationFailedforuser"u2"
PrevIoUsconnectionkept

如果出现以上信息,则需改配置文件:
vim/etc/postgresql/9.6/main/pg_hba.conf
找到下面的一行:
localallpostgrespeer
改成:
localallpostgrestrust

如果出现下面的错误:
FATAL:PeerauthenticationFailedforuser"mypguser"

请仍然修改pg_hba.conf文件,该下面行的peer为md5:
localallallmd5#replacepeerwithmd5

完成上面的修改后请重新加载postgresql:
/etc/init.d/postgresqlreload

postgres=>select*fromt1;
ERROR:permissiondeniedforrelationt1
postgres=>\c-postgres
Youarenowconnectedtodatabase"postgres"asuser"postgres".
postgres=#grantselectonalltablesinschemapublictou1;
GRANT
postgres=#\c-u1Youarenowconnectedtodatabase"postgres"asuser"u1".
postgres=>select*fromt1;
id|name
----+------
(0rows)
4.创建表t2
postgres=>\c-postgres
Youarenowconnectedtodatabase"postgres"asuser"postgres".
postgres=#createtablet2(idserial,namevarchar(64));
CREATETABLE
postgres=#\dtListofrelations
Schema|Name|Type|Owner
--------+------+-------+----------
public|t1|table|postgres
public|t2|table|postgres
(2rows)
5.验证u1的权限
postgres=#\c-u1Youarenowconnectedtodatabase"postgres"asuser"u1".
postgres=>select*fromt1;
id|name
----+------
(0rows)

postgres=>select*fromt2;
ERROR:permissiondeniedforrelationt2

可见u1是有t1表的读权限,但没有t2表的读权限,这样是不是意味着每次新建表就要赋一次权限?
6.解决办法
postgres=>\c-postgres
Youarenowconnectedtodatabase"postgres"asuser"postgres".
postgres=#alterdefaultprivilegesinschemapublicgrantselectontablestou1;
ALTERDEFAULTPRIVILEGES
#grant是赋予用户schema下当前表的权限
#alterdefaultprivileges是赋予用户schema下表的默认权限

postgres=#createtablet3(idserial,namevarchar(64));
CREATETABLE
postgres=#\dtListofrelations
Schema|Name|Type|Owner
--------+------+-------+----------
public|t1|table|postgres
public|t2|table|postgres
public|t3|table|postgres
(3rows)

四、创建可更新用户

1.创建u2用户
postgres=#createroleu2withloginpassword'123456';
CREATEROLE
2.赋予更新权限
postgres=#alterdefaultprivilegesinschemapublicgrantselect,insert,update,deleteontablestou2;
ALTERDEFAULTPRIVILEGES
3.创建表t4
postgres=#createtablet4(idserial,namevarchar(64));CREATETABLE
postgres=#\dtListofrelations
Schema|Name|Type|Owner
--------+------+-------+----------
public|t1|table|postgres
public|t2|table|postgres
public|t3|table|postgres
public|t4|table|postgres
(4rows)
4.查看权限
postgres=#\c-u2Youarenowconnectedtodatabase"postgres"asuser"u2".
postgres=>insertintot4values(1,'aa');
INSERT01
postgres=>select*fromt4;
id|name
----+------
1|aa
(1row)

postgres=>updatet4setname='bb'whereid=1;
UPDATE1
postgres=>select*fromt4;
id|name
----+------
1|bb
(1row)

postgres=>deletefromt4whereid=1;
DELETE1
postgres=>select*fromt4;
id|name
----+------
(0rows)
5.序列的权限与解决办法
#在insert的时候,指定列插入,主键id是serial类型会默认走sequence的下一个值,但前面
#只赋予了表的权限,所以会出现下面的问题:

postgres=>insertintot4(name)values('aa');
ERROR:permissiondeniedforsequencet4_id_seq

#解决方法就是再赋一次sequence的值就行了
postgres=>\c-postgres
Youarenowconnectedtodatabase"postgres"asuser"postgres".
postgres=#alterdefaultprivilegesinschemapublicgrantusageonsequencestou2;
ALTERDEFAULTPRIVILEGES
postgres=#createtablet5(idserial,namevarchar(64));
CREATETABLE
postgres=#\c-u2
Youarenowconnectedtodatabase"postgres"asuser"u2".
postgres=>insertintot5(name)values('cc');
INSERT01postgres=>select*fromt5;
id|name
----+------
1|cc
(1row)

五、删除用户

postgres=>\c-postgres
Youarenowconnectedtodatabase"postgres"asuser"postgres".
postgres=#droproleu2;
ERROR:role"u2"cannotbedroppedbecausesomeobjectsdependonit
DETAIL:privilegesfortablet5
privilegesforsequencet5_id_seq
privilegesfordefaultprivilegesonnewsequencesbelongingtorolepostgresinschemapublicprivilegesfortablet4
privilegesfordefaultprivilegesonnewrelationsbelongingtorolepostgresinschemapublic

#当我们删除用户的时候,会提示有权限依赖,所以我们要删除这些权限
postgres=#alterdefaultprivilegesinschemapublicrevokeusageonsequencesfromu2;
ALTERDEFAULTPRIVILEGES
postgres=#alterdefaultprivilegesinschemapublicrevokeselect,delete,updateontablesfromu2;
ALTERDEFAULTPRIVILEGES
postgres=#revokeselect,updateonalltablesinschemapublicfromu2;
REVOKE
postgres=#revokeusageonallsequencesinschemapublicfromu2;
REVOKE
postgres=#droproleu2;
DROPROLE

六、修改用户密码

sudo-upostgrespsql
ALTERUSERpostgresWITHPASSWORD'passwd';
原文链接:https://www.f2er.com/postgresql/193753.html

猜你在找的Postgre SQL相关文章