PostgreSQL 8.4 grant DML privileges on all tables to a role

10
votes

How do I go about granting DML (SELECT,INSERT,UPDATE,DELETE) on all tables in a schema in PostgreSQL 8.4? I'd also like this grant to persist for new table creation in the future as well.

I've seen solutions for 9.0 but I'm stuck with 8.4 as it ships with Debian stable.

I have tried the following as a baseline but it doesn't work, resulting in the inevitable "access to relation X denied":

GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;

I've dredged through the documentation and I can't seem to find a suitable solution.

5
postgresqlgrantdml
Why can't you upgrade? There surely must be a way to upgrade PostgreSQL. The current versions can all be downloaded from the Postgres website. – a_horse_with_no_name
There are definitely ways to upgrade, but I do not wish to as the vendor-supplied packages are well integrated, tested and understood. Our plan is to up to 9.1 at the same time Debian wheezy is declared stable. – pointyhat
I will never understand why some operating systems patronize the user that much. – a_horse_with_no_name
We do have options (Debian backports/switch to CentOS+vendor packages) but we choose not to take them as they require more maintenance. We're lazy really :-) – pointyhat
@pointyhat: Lazy is quite ok, but PostgreSQL 9.1 has been available for lazy people on Debian stable soon after its release. There is an official backport. We are using it on several servers, works like a charm. Upgrade to next Debian version works seemlessly, too. Start with reading instructions on backports.debian.org. – Erwin Brandstetter

5 Answers

21
votes

I'd also like this grant to persist for new table creation in the future as well. [...] I've dredged through the documentation and I can't seem to find a suitable solution.

Because before 9.0 there is none. All you can get is to set the permissions for existing tables. You have to do one GRANT for each table, because before 9.0 there was no "bulk" mode. See the SQL grammer for 8.4 and 9.0:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [,...] | ALL [ PRIVILEGES ] }
    ON [ TABLE ] tablename [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

and 9.0 here:

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

The new ALL TABLES IN SCHEMA part is the one you are missing.

Also: Setting permissions on the database level as in you question won't help you: You will "only" set the permissions on he database, but not on any "contained" stuff like tables. The relevant section: 

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
    ON DATABASE dbname [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

Which means you can only set CREATE, CONNECT and TEMP permissions on the database itself but no SELECT, INSERT etc.

So far for the bad stuff. What you can do are the following things:

  • Reduce the number of permission management by granting rights not to users but to roles. Then add roles to individual users. When a new table is created you only need to adjust one or two roles, but not hundreds of users.

  • Query the system catalogues and create appropriate GRANT commands. Save them into a file and execute that file. This should give you an easier startup.

Such a query might look like this:

select 'GRANT ALL ON ' || table_schema || '.' || table_name ||' to my_group;' 
from information_schema.tables 
where 
    table_type = 'BASE TABLE' and 
    table_schema not in ('pg_catalog', 'information_schema');
2
votes

Grants in PostgreSQL are not recursive; a GRANT on the database sets rights to the database object but does not affect the contained schemas or their tables, views, functions, etc.

Granting ALL PRIVELEGES on the database grants CREATE, CONNECT and TEMPORARY rights.

See \h GRANT in psql, or the documentation for GRANT in 8.4, to see what ALL PRIVILEGES means for DATABASE:

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

There are plpgsql functions and various 3rd party scripts available that use pg_catalog or information_schema to dynamically build GRANTs and recursively set rights. Search for "postgresql recursive grant".

These will not help you set default access rights for new tables. PostgreSQL doe have ALTER DEFAULT PRIVILEGES to allow you to set the default table rights for new tables, but it's only supported in Pg 9.1 and newer. Explicit GRANTs are required for each table in older versions or when permissions are to be set after table creation.

Newer versions, as you noted, have facilities for multiple grants via GRANT ... ALL TABLES, but your question is specific to 8.4.

1
votes

I believe you can't do that. But you can use the information schema to generate the grants so you don't have to manually do it for 10,000 tables. See the link below for a relative example and the site linked to easy very good for info.

http://www.postgresonline.com/journal/archives/30-DML-to-generate-DDL-and-DCL-Making-structural-and-Permission-changes-to-multiple-tables.html

1
votes

If you have another user who have the DML privileges, it works in postgresql 8.x/9.x:

grant <userWithDMLPrivileges> to testuser;

Hope it helps.

0
votes

Add all priviledges on all tables:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO [username];