Creating a postgres user

Question

I want to make a typical user in postgres that will be used by my web application.

The web application will require access to all tables.
The user should be able to read/write to all tables (select, update, delete, insert)

All tables belong to the public schema

What is the best practise for creating this user?

database name: acmeinc user: acmeuser

I tried this so far and I was not able to even update a table (permission error on the relation):

create user acmeuser
grant all privileges on database acmeinc to acmeuser
alter schema public owner to acmeuser

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO acmeuser

Update:

I was running the above like:

psql acmeinc -c "create user acmeuser"

Should I be creating a role and then associating the user acmeuser to the role?

I updated my question to show how I ran the statements, I'll try again with the semicolons. BTW, when I ran the commands, I did get a response back like "GRANT". Still getting the error ERROR: permission denied for relation tableabc — Blankman
– Blankman, Commented Nov 19, 2014 at 13:41
update table123 set col3='23' where id=1; Selects work fine. — Blankman
– Blankman, Commented Nov 19, 2014 at 14:18
Ah. Granting on a database doesn't grant on tables within it. Also alter default privs only affects tables created after that is run. You want grant ... on all tables in schema. — Craig Ringer
– Craig Ringer, Commented Nov 19, 2014 at 14:40

Craig Ringer · Accepted Answer · 2014-11-20 00:25:16Z

3

I think your issue stems from two misunderstandings around GRANT:

Granting on a database doesn't grant on tables within it, it only grants rights on the database object its self; and
ALTER DEFAULT PRIVILEGES only affects tables created after that is run, it doesn't change existing tables.

You want GRANT ... ON ALL TABLES IN SCHEMA ....

answered Nov 20, 2014 at 0:25

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

1 Comment

Blankman Over a year ago

I have to run a separate query for sequences and functions?

Collectives™ on Stack Overflow

Creating a postgres user

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related