25

I want to GRANT USAGE to a user/role for a given database. The database has many schemas.

I know there's an ON ALL TABLES IN SCHEMA, but I want "all schemas". I tried GRANT USAGE .. ON DATABASE, but that's obviously wrong (it doesn't actually exist).

This is for Postgres 9.3 or 9.4, it happens to be a server that is on AWS RDS.

3 Answers 3

26

You have at least two options.

The first one makes use of a small query and a text editor. We have to collect the schemata of our interest:

SELECT nspname
  FROM pg_namespace;

You can add a WHERE clause if you want to limit the scope. Copy the output and amend it, so you get a number of GRANT USAGE ON SCHEMA ... TO your_role; commands. Then just feed it to psql, for example:

psql -f multigrant.sql

A usual variant of this could be a shell script that loops over the collected names and calls psql, passing the constructed GRANT statement to the -c option.

The other solution does basically the same in one pl/pgsql block, building a dynamic query. The core is the same - we have to collect the schemata. Then we loop over all of them, granting the permissions schema by schema:

DO $do$
DECLARE
    sch text;
BEGIN
    FOR sch IN SELECT nspname FROM pg_namespace
    LOOP
        EXECUTE format($$ GRANT USAGE ON SCHEMA %I TO your_role $$, sch);
    END LOOP;
END;
$do$;

Notes:

  • unlike for tables, sequences, functions and types, one cannot set default privileges for schemata (as of 9.4). You will have to grant this privilege for any newly added schema manually.
  • here I am using dollar quoting when building the dynamic query. This allows me to use 'normal' syntax, as opposed to multiplicating single quotes, for example (not present in this example). This way most editors will highlight the statements nicely.
  • I also use format() with the %I format specifier to have the object name properly quoted if necessary. This approach is far more readable than building the query with concatenation of string constants and some quote_ident() calls.
  • pg_namespace can be found in the pg_catalog schema. Check out the other objects in there - they store every aspect of your schemas, tables and so on.
2
  • 1
    I wonder if you could simulate default privileges for newly created schemas by using an event trigger that automatically runs the grant usage for the new schema. Jul 10, 2015 at 14:27
  • @a_horse_with_no_name thinking about it, I see currently no reason why it could not be done.
    – dezso
    Jul 10, 2015 at 15:30
3

You can use too.

DO $do$
DECLARE
    sch text;
BEGIN
    FOR sch IN SELECT nspname FROM pg_namespace where nspname != 'pg_toast' 
    and nspname != 'pg_temp_1' and nspname != 'pg_toast_temp_1'
    and nspname != 'pg_statistic' and nspname != 'pg_catalog'
    and nspname != 'information_schema'
    LOOP
        EXECUTE format($$ GRANT USAGE ON SCHEMA %I TO your_role $$, sch);
        EXECUTE format($$ GRANT SELECT ON ALL SEQUENCES IN SCHEMA %I TO your_role $$, sch);
        EXECUTE format($$ GRANT SELECT ON ALL TABLES IN SCHEMA %I TO backup_user $$, sch);

        EXECUTE format($$ ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO your_role $$, sch);
        EXECUTE format($$ ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON SEQUENCES TO your_role $$, sch);
    END LOOP;
END;
$do$;
1
  • The first line in the loop looks duplicated.
    – zoechi
    Jul 13, 2020 at 12:53
1

That's my variation, adapted from the other answers, for giving all privileges:

create or replace function grant_all_privileges_on_database_to_role(
    role_name text,
    database_name text
)
    returns void
    language plpgsql as
$function$
declare
    row record;
begin
    execute format('grant all on database %1$s to %2$s', database_name, role_name);

    for row in (
        select
            nspname as name
        from
            pg_namespace
        where
            nspname not in (
                'information_schema',
                'pg_catalog',
                'pg_toast'
            )
    ) loop
        execute format('grant all on schema %1$s to %2$s', row.name, role_name);
        execute format('grant all on all tables in schema %1$s to %2$s', row.name, role_name);
        execute format('grant all on all sequences in schema %1$s to %2$s', row.name, role_name);
        execute format('grant all on all functions in schema %1$s to %2$s', row.name, role_name);
        execute format('grant all on all procedures in schema %1$s to %2$s', row.name, role_name);
        execute format('grant all on all routines in schema %1$s to %2$s', row.name, role_name);
    end loop;
end;
$function$;

-- select grant_all_privileges_on_database_to_role('<my_user>', '<my_database>');

or, for a schema:

create or replace function grant_all_privileges_on_schema_to_role(
    role_name text,
    schema_name text
)
    returns void
    language plpgsql
as $function$
begin
    execute format('grant all on schema %1$s to %2$s', schema_name, role_name);
    execute format('grant all on all tables in schema %1$s to %2$s', schema_name, role_name);
    execute format('grant all on all sequences in schema %1$s to %2$s', schema_name, role_name);
    execute format('grant all on all functions in schema %1$s to %2$s', schema_name, role_name);
    execute format('grant all on all procedures in schema %1$s to %2$s', schema_name, role_name);
    execute format('grant all on all routines in schema %1$s to %2$s', schema_name, role_name);
end;
$function$;

-- select grant_all_privileges_on_schema_to_role('<my_user>', '<my_schema>');
1
  • PS: the function must be created on the database that contains all the schemas Sep 18 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.