PostgresDB tips to add a new role to an existing db..

gistfile1.txt

PostgresDB tips to add a new role to an existing db..

# create user
- add user USER with password 'PASSWORD';

---

# grant privileges to the database
- grant all privileges on database DATABASE to USER;

---

# when running the following commands make sure
# you are in the database you want to modify.

# grant privileges to schema
- grant all privileges to schema 'SCHEMANAME';

---

# grant privileges to table(s)
# get all tables under schema
- select 'grant all privileges on table '||tablename||' to USER;' \
  from pg_tables where schemaname in ('public') order by schemaname, tablename;
# execute results
- grant all privileges on table TABLE to USER;

---

# grant privileges to sequences 
# get all sequences in database
- select 'grant all privileges on sequence '||relname||' to USER;' \ 
  from pg_class where relkind = 'S';
# execute results
- grant all privileges on sequence SEQUENCE to USER;