CODEWITHSUNDEEP
databasepostgresqldatabase-schemaprivileges
John
John

Reputation: 7067

Postgres Grant SELECT & UPDATE privilege ONLY to a user

Can anyone kindly tell me how I can grant a user (username: restricted_user) with only SELECT AND UPDATE privilege on postgresql? I'm running centos.

At the moment the user cannot select anything. I've logged in to phpPgMyAdmin and i can only seem to grant the SELECT and UPDATE privilege per table for the user which works but I would like to apply this rule for all tables across all databases.

thanks

Upvotes: 3

Views: 11927

Answers (2)

Gomiunik
Gomiunik

Reputation: 480

I know there are a lot of answers and solutions but I hope to help someone. I've created a simple query to take care of this.

DO $$
DECLARE 
    schema_name text;
    user_name text;
    table_privileges text;
BEGIN
    -- set user to assign privileges to
    user_name := 'my_user';
    -- set which privileges to assign
    table_privileges := 'SELECT, INSERT, UPDATE';

    FOR schema_name IN 
        -- find schemas (in my case I'm working in timescale so excluding     these as well) feel free to determine your own filters on schemas
        SELECT DISTINCT table_schema 
        FROM information_schema.tables
        WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
          AND table_schema NOT LIKE ('%timesca%')
    LOOP
        EXECUTE format('REVOKE ALL ON ALL TABLES IN SCHEMA %I FROM PUBLIC;', schema_name);
        
        EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I;', schema_name, user_name);  
        EXECUTE format('GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA %I TO %I;', schema_name, user_name);
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT, INSERT, UPDATE ON TABLES TO %I', schema_name, user_name);

        IF(UPPER(table_privileges) like '%INSERT%') THEN
            EXECUTE format('GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA %I TO %I;', schema_name, user_name);
            EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT USAGE, SELECT ON SEQUENCES TO %I;', schema_name, user_name);         
        END IF;

    END LOOP;

END $$;

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

You can grant to all tables in a schema at once:

GRANT SELECT, UPDATE ON ALL TABLES IN SCHEMA public TO restricted_user;

If there are serial columns or other SEQUENCES in use, you need also:

GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO restricted_user;

Run for every relevant schema. More in the fine manual for GRANT. You also need is the USAGE privilege on each schema:

GRANT USAGE ON SCHEMA public TO restricted_user;

If you have another user creating more objects, you probably want to grant the same privileges for future objects by default, too. That's where DEFAULT PRIVILEGES come in:

ALTER DEFAULT PRIVILEGES FOR creating_user IN SCHEMA public
GRANT SELECT, UPDATE ON TABLES TO restricted_user;

ALTER DEFAULT PRIVILEGES FOR creating_user IN SCHEMA public
GRANT USAGE ON SEQUENCES TO restricted_user;

Upvotes: 8

Related Questions