Granting specific permissions on ALL tables I have a tutorial that users need to type in PostgreSQL commands. I don't give the script to users simply because it's a learning experience. Everything in the script below is typed in except for the grants for table data, which is imported.

There should be a way to grant the permissions needed without having to include all the table names. If I can cut all that out I can have the users actually type the grant commands in instead id inserting from a script.

Any help would be greatly appreciated...

create database archive;

create database snort;

create user snort with password 'l0gg3r';

create user base with password 'an@l1st';

\connect archive;

\i d:/winids/barnyard2/schemas/create_postgresql;

\i d:/winids/inetpub/wwwroot/base/sql/create_base_tbls_pgsql.sql;

\i d:/winids/inetpub/wwwroot/base/sql/create_base_tbls_pgsql_extra.sql;

GRANT ALL ON TABLE data, detail, encoding, event, icmphdr, iphdr, opt, reference, reference_ref_id_seq, reference_system, reference_system_ref_system_id_seq, schema, sensor, sensor_sid_seq, sig_class, sig_class_sig_class_id_seq, sig_reference, signature, signature_sig_id_seq, tcphdr, udphdr, acid_ag, acid_ag_alert, acid_ip_cache, acid_event, base_roles, base_users TO base;

\connect snort;

\i d:/winids/barnyard2/schemas/create_postgresql;

\i d:/winids/inetpub/wwwroot/base/sql/create_base_tbls_pgsql.sql;

\i d:/winids/inetpub/wwwroot/base/sql/create_base_tbls_pgsql_extra.sql;

GRANT INSERT, SELECT, UPDATE ON TABLE data, detail, encoding, event, icmphdr, iphdr, opt, reference, reference_ref_id_seq, reference_system, reference_system_ref_system_id_seq, schema, sensor, sensor_sid_seq, sig_class, sig_class_sig_class_id_seq, sig_reference, signature, signature_sig_id_seq, tcphdr, udphdr TO snort;

GRANT ALL ON TABLE data, detail, encoding, event, icmphdr, iphdr, opt, reference, reference_ref_id_seq, reference_system, reference_system_ref_system_id_seq, schema, sensor, sensor_sid_seq, sig_class, sig_class_sig_class_id_seq, sig_reference, signature, signature_sig_id_seq, tcphdr, udphdr, acid_ag, acid_ag_alert, acid_ip_cache, acid_event, base_roles, base_users TO base;
If you're not specifying the table's schema, and you're not, then you can use ALL TABLES IN SCHEMA and rely on the default schema being PUBLIC or in the event you've changed your search_path just write the schema name below. From the docs on GRANT

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]


So that looks like

GRANT INSERT, SELECT, UPDATE
  ON ALL TABLES IN SCHEMA public
  TO snort;
GRANT ALL
  ON ALL TABLES IN SCHEMA public
  TO base;