how to set up user accounts in PostgreSQL for Web based application

Question

I’m developing a web based application with PostgreSQL as the back End Database & perl handling the scripting

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.

I need to get my head around how to set up user accounts in PostgreSQL

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.

The web_admin account will need to log into the webpage

In pgAdmin or on the command line how do I create the login Rolls and give the what ever the required permissions are?

EDIT Please Clarify

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

Edit 2 ooops

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

The web_user account allows just read access to a database the problem the web_admin account has the same read access

I’ve tried drop web_user & revoke by

revoke all privileges on database mydb from web_admin;

but it fails with errors about dependencies listing all tables in mydb

I've attempted to see what privileges web_admin actually has but have been unable to.

How do I drop this account

What is wrong with the syntax for grant web_user?

postgresql.org/docs/9.1/static/sql-createrole.html there is a ton of info on how to do this here. — Lucas
– Lucas, Commented Feb 6, 2013 at 17:17
Have you considered using a framework such as Dancer for your development? I can't vouch for that specific framework as I use python and django exclusively, but using a framework in general takes away a lot of the pain of this kind of thing, and reduces the likelihood of your code being insecure. — cms_mgr
– cms_mgr, Commented Feb 6, 2013 at 17:22

score 3 · Accepted Answer · 2013-02-07 11:54:05Z

3

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.

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:

edited Feb 7, 2013 at 11:54

answered Feb 6, 2013 at 17:21

user80168

Sign up to request clarification or add additional context in comments.

2 Comments

Holly Over a year ago

Well that has opened up a whole new world of pain! Can you look at my edit and tell me if my understanding is correct? Thank you

Holly Over a year ago

I read your posts but in my case performing a remote login as a superuser is unlikely I'm using pgAdmin to access the DB server to access psql directly they would need to remote into the server but only sys admin has access. i'm relay struggling with this & suspect i'm making a hash of it. Please see second edit.

Collectives™ on Stack Overflow

how to set up user accounts in PostgreSQL for Web based application

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related