postgresql: permission denied for sequence <table>_i_seq

Question

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 ?

Answer 1

GRANT USAGE, SELECT ON SEQUENCE some_table_name_i_seq TO some_user;

In this case:

GRANT USAGE, SELECT ON SEQUENCE pmacct_i_seq TO B;

Answer 2

In Postgres 15+ it is much easier to grant useful permissions while setting up your database by using predefined roles.

See https://www.postgresql.org/docs/current/predefined-roles.html

    GRANT pg_read_all_data TO USER;  
    -- Grants USAGE (which includes SELECT) on all schemas. 
    -- Grants SELECT on all tables, views, and sequences.
    
    GRANT pg_write_all_data TO USER;
    -- Grants USAGE on all schemas. 
    -- Grants INSERT, UPDATE, DELETE on all tables, views, and sequences.

Answer 3

You miss

ALTER DEFAULT PRIVILEGES GRANT USAGE ON SEQUENCES TO B;

pmacct_i_seq is a sequence that backs the BIGSERIAL column i. Since user B can modify the table, it needs the privilege to access the sequence.

Answer 4

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.