Postgresql: permission denied for sequence _i_seq

postgresqlpostgresql-9.2

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 ?

Best Answer

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.

Related Solutions

PostgreSQL: After restoring data, unable to use Views (permission denied)

To troubleshoot, I would start by looking at the output of SELECT current_user; and SHOW search_path;. Also, the output of \dp termin in psql.

My suspicion here is that you are stumbling over mixed case identifiers. The error message mentions a table

Map

Notice the capital letter M. Are you sure there is no mix-up with another table named map (lower case)?
Are PostgreSQL column names case-sensitive?

(Or, in another schema coming earlier in the search_path?)

Postgresql – How to grant drop table/function privileges to a role in PostgreSQL

Only the owner (and superusers) can drop objects. Per documentation:

The right to drop an object, or to alter its definition in any way, is not treated as a grantable privilege; it is inherent in the owner, and cannot be granted or revoked. (However, a similar effect can be obtained by granting or revoking membership in the role that owns the object; see below.) The owner implicitly has all grant options for the object, too.

So, make administrator own such objects that users should be able to drop.

ALTER FUNCTION foo() OWNER TO administrator;
ALTER TABLE foo      OWNER TO administrator;

And you remembered to actually grant group membership, right?

GRANT administrator TO _administrator;