postgresql: permission denied for sequence _i_seq I have a schema of a table in PostgreSQL that looks like:

CREATE TABLE IF NOT EXISTS  data.pmacct (

    i                       BIGSERIAL PRIMARY KEY,


And there are users:


A: superuser privileges 
B: read/write privileges 
C: read privileges


And I have made the following modifications in regards to the privileges (as user A)

CREATE SCHEMA data ;
ALTER DATABASE ip_spotlight SET search_path TO data ;
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA data FROM PUBLIC ;
ALTER DEFAULT PRIVILEGES IN SCHEMA data GRANT SELECT ON TABLES TO C ;
ALTER DEFAULT PRIVILEGES IN SCHEMA data GRANT ALL ON TABLES TO B ;


as user postgres

GRANT USAGE ON SCHEMA data TO netops ;
GRANT USAGE ON SCHEMA data TO netopsapp ;
GRANT SELECT ON ALL TABLES IN SCHEMA data TO C ;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA data TO C ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA data TO C ;
GRANT ALL ON ALL TABLES IN SCHEMA data TO B ;
GRANT ALL ON ALL SEQUENCES IN SCHEMA data TO B ;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA data TO B ;


But I am getting the following error message as user B


  ERROR:  permission denied for sequence pmacct_i_seq


Could you please enlighten me what I have done wrong and user B is not able to do an INSERT statement into the data.pmacct table ?
Try to make changes  on all tables
Answer from Alex Soto



Since you're changing the ownership for all tables, you likely want views and sequences too.  Here's what I did:

Tables:

for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" YOUR_DB` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done


Sequences:

for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" YOUR_DB` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done


Views:

for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" YOUR_DB` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done


You could probably [DRY][1] that up a bit since the alter statements are identical for all three.