PostgreSQL - Find ALL Privileges for a Group

Question

I have been attempting to remove a group role from an AWS PostgreSQL instance, and I've gotten stuck. I have successfully removed all dependencies from the group, except for 6, and I have no idea what those might be. I will explain what I have attempted to use to find these missing privileges, and I really appreciate some direction as to where I can look next. Each of these steps were performed on all of the databases and schemas within the Instance:

1. I used the following code to revoke all privileges from the group

   REVOKE ALL ON DATABASE {dbname} FROM GROUP "Application_Access";
   REVOKE ALL ON SCHEMA public FROM GROUP "Application_Access";
   REVOKE ALL ON ALL TABLES IN SCHEMA  public  FROM GROUP "Application_Access";
   REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public  FROM GROUP "Application_Access";
   REVOKE ALL ON ALL SEQUENCES IN SCHEMA public  FROM GROUP "Application_Access";
   

2. I used the psql commands to list all objects and their privileges, and this is what I found

   • \z = Nothing listed for Application_Access
   • \du and \dg = Application_Access - No inheritance, Cannot login, Member of {}
   • \d = All relations owned by OurAdmin
   • \db = All tablespaces owned by rdsadmin (AWS Admin)
   • \dtisv = Nothing
   • \ddp = This command is the only one showing Application_Access Results of \ddp Command

3. I understand that \ddp shows default privileges for future database additions. I attempted to run ALTER PRIVILEGES commands. It forced me to make myself a member of Application_Access (ERROR: Must be a member of Role. why?!?) in order to run the command. This reassigned all of my privileges back to Application_Access and I was back to square one.

   ALTER DEFAULT PRIVILEGES ON ROLE "Application_Access" REVOKE ALL ON DATABASE {dbname} FROM "Application_Access";
   ALTER DEFAULT PRIVILEGES ON ROLE "Application_Access" REVOKE ALL ON ALL SCHEMAS FROM "Application_Access";
   ALTER DEFAULT PRIVILEGES ON ROLE "Application_Access" REVOKE ALL ON TABLES FROM "Application_Access";
   ALTER DEFAULT PRIVILEGES ON ROLE "Application_Access" REVOKE ALL ON FUNCTIONS FROM "Application_Access";
   ALTER DEFAULT PRIVILEGES ON ROLE "Application_Access" REVOKE ALL ON SEQUENCES FROM "Application_Access";
   

   • I reran all of my previous steps, however, now when I run \ddp on all databases, it has added the first 3 lines that you see in the attached image above.

4. I have a script that I found and altered to show me all of the privileges on each database. I can pass it a Role to simplify the search. It isn't showing Application_Access as being associated with any objects either.

   SELECT  relacl
       , SUBSTRING(
          CASE WHEN strpos('r', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', SELECT'     ELSE '' END
       || CASE WHEN strpos('w', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', UPDATE'     ELSE '' END
       || CASE WHEN strpos('a', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', INSERT'     ELSE '' END
       || CASE WHEN strpos('d', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', DELETE'     ELSE '' END
       || CASE WHEN strpos('R', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', RULE'       ELSE '' END
       || CASE WHEN strpos('x', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', REFERENCES' ELSE '' END
       || CASE WHEN strpos('t', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', TRIGGER'    ELSE '' END
       || CASE WHEN strpos('X', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', EXECUTE'    ELSE '' END
       || CASE WHEN strpos('U', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', USAGE'      ELSE '' END
       || CASE WHEN strpos('C', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', CREATE'     ELSE '' END
       || CASE WHEN strpos('T', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', TEMPORARY'  ELSE '' END
          , 3,10000)
       || namespace ||'.'|| item ||' TO '|| pu.groname ||' ;' AS grantsql
   FROM    (SELECT      use.usename AS subject
                   ,nsp.nspname AS namespace
                   ,cls.relname AS item
                   ,cls.relkind AS type
                   ,use2.usename AS owner
                   ,cls.relacl
       FROM        pg_user     use 
       CROSS JOIN  pg_class    cls
       LEFT JOIN   pg_namespace nsp 
       ON          cls.relnamespace = nsp.oid 
       LEFT JOIN   pg_user      use2 
       ON          cls.relowner = use2.usesysid
       WHERE       cls.relowner = use.usesysid
       AND         nsp.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
       ORDER BY     subject
                   ,namespace
                   ,item ) as x
   JOIN    pg_group pu ON array_to_string(relacl, '|') LIKE '%'|| pu.groname ||'%' 
   WHERE   relacl IS NOT NULL
   AND     relacl::text LIKE '%Application%'
   ORDER BY 2 
   

***Based on everything that you see above, is there anything that anyone can think of that I left out, or didn't do correctly/completely? How can I resolve the default privileges - ALTER PRIVILEGES REVOKE ALL didn't work. Thank you for your help.

Answer 1

It took several days, but I guess I finally asked Google the right question. I thought I would post my solution in case anyone might be having a similar problem on PostgreSQL:

I was close with my ALTER PRIVILEGES commands. In this screenshot (psql command \ddp)-

there are 4 columns: Owner, Schema, Type, and Access Privilege. You will need each of these columns to generate the ALTER PRIVILEGES commands:

• Owner - This is the user/group that will have the privileges altered - seemed weird to me too. :)
• Schema - This is the schema containing the commands to assign the default privileges
• Type - This will tell you what type of REVOKE command you need to use
• Access Privilege - This is the user/group that you can't drop because "it has dependencies"

Based on this list, your command structure will look similar to this - filling {} in from above:

ALTER DEFAULT PRIVILEGES FOR ROLE {Owner} IN SCHEMA {Schema} REVOKE ALL PRIVILEGES ON {Type} FROM {Access Privilege};

In order to perform this command, you must be logged into the Postgres database as either the Owner, or a member of the Owner group, otherwise, you will get the error "ERROR: Must be a member of Role {Owner}."

I had to perform 2 sets of ALTER PRIVILEGES - logging into the database as the owner dbac and Application_Access.

Logging in as Application_Access and executing the commands below eliminated the first 3 rows (where Application_Access is the Owner) from the above screenshot of the \ddp psql command.

alter default privileges for role "Application_Access" in schema public revoke all privileges on functions from "Application_Access";
alter default privileges for role "Application_Access" in schema public revoke all privileges on sequences from "Application_Access";
alter default privileges for role "Application_Access" in schema public revoke all privileges on tables from "Application_Access";

Logging in as dbac and executing the commands below eliminated the other rows (where dbac is the Owner) from the above screenshot of the psql \ddp command.

alter default privileges for role "dbac" in schema public revoke all privileges on functions from "Application_Access";
alter default privileges for role "dbac" in schema public revoke all privileges on sequences from "Application_Access";
alter default privileges for role "dbac" in schema public revoke all privileges on tables from "Application_Access";