Amazon Postgres RDS: Update pg_attribute to update my custom attribute I want to update my attribute to change its length. I am getting 

Error: Permission denied for relation pg_attribute.


My SQL Query is like this:

DO $$
BEGIN
UPDATE
    pg_attribute
SET
    atttypmod = 5+4
WHERE
    attname LIKE '%my_attribute%'
    AND atttypmod = 1 + 4;
END $$;


I have set search_path to my user, my user has the usage on schema pg_catalog. Is this possible? Because if not, we would have to drop the idea of RDS DB and move to ec2-instance and creating our own postgres db

UPDATE: 

Granting permission with this command:

GRANT UPDATE ON pg_attribute TO rds_superuser;


Result is:

WARNING:  no privileges were granted for "pg_attribute"
Query returned successfully with no result in 41 msec.
If you want to change the length of many varchar columns (which isn't actually a custom attribute), you are better off using ALTER TABLE commands. You don't risk having unwanted side-effects, just in case PostgreSQL needs to do something more than changing the pg_attributes table. It might not do it today, but might do it in any future version.

You can have the database help you generate the ALTER TABLE DDL statements (or even, generate and execute them within a UDF).

To generate the DDL statements, you can use the information_schema.columns view; which is simpler to understand than using pg_attributes together with more catalog tables all joined. [The view actually joins several tables in non-obvious ways, but that's already done for you.]

You first check the columns involved:

-- Check the columns you are interested in
SELECT 
    table_schema, table_name, column_name, 
    data_type, character_maximum_length  
FROM
    information_schema.columns
WHERE
    column_name like '%varchar_column%' 
    AND table_schema = 'my_schema' ;


You can get something like:

| table_schema | table_name |            column_name |         data_type | character_maximum_length |
|--------------|------------|------------------------|-------------------|--------------------------|
|    my_schema |         t1 |     one_varchar_column | character varying |                        5 |
|    my_schema |         t1 | another_varchar_column | character varying |                        6 |
|    my_schema |         t2 | a_third_varchar_column | character varying |                        3 |


Once you're sure the list of columns you get is the one you're actually interested, make the database compute the necessary DDL to perform what you want:

--
-- This will generate a collection of `ALTER TABLE` statements
--
-- This is what you're looking for
--
SELECT 
    'ALTER TABLE "' || table_schema || '"."' || table_name || '" ' ||
    '    ALTER COLUMN "' || column_name || '" TYPE VARCHAR(9);' AS ddl_to_execute
FROM
    information_schema.columns
WHERE
    column_name like '%varchar_column%' 
    -- AND table_schema = 'my_schema'  
    -- AND whatever else you need
ORDER BY
    table_schema, table_name;


This will result in the following list of ALTER TABLE statements:

ALTER TABLE "my_schema"."t1" ALTER COLUMN "one_varchar_column" TYPE VARCHAR(9);
ALTER TABLE "my_schema"."t1" ALTER COLUMN "another_varchar_column" TYPE VARCHAR(9);
ALTER TABLE "my_schema"."t2" ALTER COLUMN "a_third_varchar_column" TYPE VARCHAR(9);


Copy them and execute them.

You can check the example at http://rextester.com/GYHR58531