CODEWITHSUNDEEP
postgresqlpostgisamazon-rds
katahdin
katahdin

Reputation: 399

Change privileges on schema created by rdsadmin user

I added the postgis_topology extension, which added the topology schema to my database. However, my root user does not have sufficient privileges to use the functions in that schema in the AWS RDS instance. The topology schema was created with the following (as seen in pgAdmin):

CREATE SCHEMA topology
  AUTHORIZATION rdsadmin;

When I try to run the statement SELECT topology.CreateTopology('element_topo', 4326); I get an error "permission denied for schema topology". When I try to drop the schema and start over, the error is "must be the owner of schema topology".

I also tried just granting permissions to my root user with

GRANT ALL ON ALL FUNCTIONS IN SCHEMA topology TO my_root_user;

but that just gives "permission denied for schema topology".

How can I grant privileges on this schema to my root user?

Upvotes: 2

Views: 4007

Answers (1)

katahdin
katahdin

Reputation: 399

I have not found the reason that the CREATE EXTENSION postgis_topology; statement creates the topology schema and all its functions and tables with the rds admin user. However, that prevents other users from executing functions.

The following DO statement resolves the issues by updating the owner of the tables and functions to the role given to the database's root user.

DO
$BODY$
DECLARE
    _sql   text;

BEGIN

  EXECUTE 'SET search_path = topology,public;';

  EXECUTE 'ALTER SCHEMA topology OWNER TO rds_superuser;';

  EXECUTE 'ALTER TABLE topology.topology OWNER TO rds_superuser;';

  EXECUTE 'ALTER TABLE topology.topology_id_seq OWNER TO rds_superuser;';

  EXECUTE 'ALTER SEQUENCE topology.topology_id_seq OWNER TO rds_superuser;';

  EXECUTE 'ALTER TABLE topology.layer OWNER TO rds_superuser;';

  SELECT INTO _sql
    string_agg('ALTER FUNCTION '
      || nsp.nspname || '.' 
      || p.proname || '(' 
      || pg_get_function_identity_arguments(p.oid)
      || ') OWNER TO rds_superuser;', E'\n'
    )
  FROM pg_catalog.pg_proc p
  JOIN pg_catalog.pg_namespace nsp ON p.pronamespace = nsp.oid
  WHERE nsp.nspname = 'topology';

  EXECUTE _sql;

END
$BODY$;

Upvotes: 3

Related Questions