When setting up database access in PostgreSQL, administrators often need to grant SELECT privileges across all tables to a new user. While the GRANT SELECT ON TABLE table_name TO username syntax works for individual tables, applying this to an entire database requires a more scalable solution.

PostgreSQL doesn't support wildcards in GRANT statements directly, but we can generate the necessary commands dynamically:

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'
    LOOP
        EXECUTE 'GRANT SELECT ON TABLE ' || quote_ident(r.tablename) || ' TO readonly_user';
    END LOOP;
END
$$;

For databases with multiple schemas, we need to refine our query to include schema qualification:

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN 
        SELECT table_schema, table_name 
        FROM information_schema.tables 
        WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
    LOOP
        EXECUTE 'GRANT SELECT ON ' || quote_ident(r.table_schema) || '.' || 
                quote_ident(r.table_name) || ' TO analyst_role';
    END LOOP;
END
$$;

To automatically apply permissions to new tables created later, consider setting default privileges:

ALTER DEFAULT PRIVILEGES 
FOR ROLE db_owner
IN SCHEMA public
GRANT SELECT ON TABLES TO reporting_user;

After applying grants, verify them with:

SELECT table_schema, table_name, privilege_type 
FROM information_schema.table_privileges 
WHERE grantee = 'readonly_user';

When setting up a read-only user in PostgreSQL, database administrators often need to grant SELECT privileges across all tables. While PostgreSQL doesn't support wildcard grants directly like some other databases, there are efficient ways to accomplish this.

The most practical approach is to generate and execute GRANT statements dynamically:

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'
    LOOP
        EXECUTE 'GRANT SELECT ON TABLE ' || quote_ident(r.tablename) || ' TO my_new_user';
    END LOOP;
END
$$;

If you're working from the command line, you can use psql's \gexec command:

SELECT 'GRANT SELECT ON TABLE ' || tablename || ' TO my_new_user;'
FROM pg_tables
WHERE schemaname = 'public'
\gexec

To automatically grant SELECT on future tables, set default privileges:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO my_new_user;

Check the granted permissions with:

SELECT table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'my_new_user';

Remember that granting SELECT on all tables might expose sensitive data. Consider:

• Creating specific schemas for read-only access
• Using views to limit data exposure
• Regularly auditing permissions