CODEWITHSUNDEEP
postgresqlpostgresql-9.3
Blankman
Blankman

Reputation: 267300

Creating a postgres user

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

  1. The web application will require access to all tables.
  2. 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?

Upvotes: 0

Views: 819

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 325031

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 ....

Upvotes: 3

Related Questions