如何在PostgreSQL中为基于Web的应用程序设置用户帐户 [英] how to set up user accounts in PostgreSQL for Web based application
问题描述
我正在使用PostgreSQL作为后端数据库开发基于Web的应用程序； perl处理脚本
I’m developing a web based application with PostgreSQL as the back End Database & perl handling the scripting
我将登录信息保存在一个单独的文件中，类似于此处的建议存储全局数据库连接参数的位置，因此根据脚本实现的目的，它可以指向不同的登录凭据。目前，这显然是默认的PostgreSQL帐户变化。
I hold the login info in a separate file similar to advice here where to store global database connection parameters so depending on what the script needs to achieve it could point to different login credentials Currently it’s the default PostgreSQL account this obviously needs changing.
我需要深入了解如何在PostgreSQL中设置用户帐户
I need to get my head around how to set up user accounts in PostgreSQL
我想我需要两个一个允许用户查询数据库的用户，例如web_user，另一个需要提交更改的用户，例如web_admin。
I think I need two one that allows users to query the Database eg web_user the other will need to submit changes eg web_admin.
web_admin帐户将需要登录到网页
The web_admin account will need to log into the webpage
在pgAdmin中或在命令行上，如何创建登录劳斯莱斯并提供所需的权限？
In pgAdmin or on the command line how do I create the login Rolls and give the what ever the required permissions are?
编辑，请澄清
我一直在创建两个帐户，但是目前尚不清楚这是否是正确的方法
I’ve had a stab at creating two accounts but am unclear if this is correct way to do it
CREATE USER web_user PASSWORD 'password1';
GRANT SELECT to web_user on Table1; // Read Only
CREATE USER web_admin PASSWORD 'password2';
GRANT SELECT,INSERT,UPDATE,DELETE to web_admin on Table1; // Read Insert and update / delete rows within a existing table but not able to create, alter or delete a Table or column
编辑2个循环
所以我已经在pgAdmin窗口中执行了以下操作
So I’ve executed the following in pgAdmin window
CREATE USER web_user PASSWORD 'password1';
GRANT SELECT to web_user in schema PUBLIC; // Read Only
CREATE USER web_admin PASSWORD 'password2';
GRANT SELECT,INSERT,UPDATE,DELETE to web_admin in schema PUBLIC
web_user帐户允许只是对数据库的读取访问权限，问题是web_admin帐户具有相同的读取访问权限
The web_user account allows just read access to a database the problem the web_admin account has the same read access
我尝试删除web_user&撤消
I’ve tried drop web_user & revoke by
revoke all privileges on database mydb from web_admin;
但是它失败了，因为有关依赖关系的错误列出了mydb中的所有表
but it fails with errors about dependencies listing all tables in mydb
我试图查看web_admin实际拥有哪些特权，但无法获得。
I've attempted to see what privileges web_admin actually has but have been unable to.
我如何删除该帐户
grant web_user的语法有什么问题？
What is wrong with the syntax for grant web_user?
推荐答案
要创建用户，您可以使用创建用户命令。 （它与 CREATE ROLE ... WITH LOGIN 相同）之后，您使用 GRANT 授予特权。
To create users you can use CREATE USER command in SQL. (it is the same as CREATE ROLE ... WITH LOGIN) Afterwards you use GRANT to grant privileges.
我不确定默认PostgreSQL帐户是什么意思。如果您谈论的是 postgres帐户，它是超级用户，并且拥有所有权利。
I'm not sure what you mean by "default PostgreSQL account". If you are talking about "postgres" account, it's superuser, and has rights to everything.
特权和安全性这一主题非常复杂，我曾在此写道至少两次：
The topic of privileges, and securing is quite complex, I wrote about it at least couple of times:
- How to grant privileges on all tables in PostgreSQL < 9.0
- How to grant privileges on all tables in PostgreSQL > 9.0
- How to secure your database
这篇关于如何在PostgreSQL中为基于Web的应用程序设置用户帐户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
