I was asked by a friend to work (or at least suggest) some magic with PostgreSQL hosted in GCP. He had inherited a system configured by someone who had left the organisation, leaving some gaps in documentation and most importantly some admin passwords.

In this case, it was the PostgreSQL which was being used for a production system. He didn’t have the database password for any of the users on it (everything had been done in the hosted application, for which admin access was available). When he needed to log in and see some information in the database, he was stuck.

It turns out that it’s not so easy (or at least wasn’t at the time of this story) to do wheat I (or my friend) would normally do… Instead there is a bit of extra working around which isn’t so well documented (hence i thought I’d share my experience here).

The usual approach (which doesn’t work in GCP)

Using the system superuser (root/administrator) account and launching psql and logging in as the postgres user (without actually giving a password):

sudo -u postgres psql postgres

Once in as the postgres user, creating the new one as required. Something like:

CREATE ROLE myuser LOGIN PASSWORD 'mypass';

The approach for GCP

Because GCP doesn’t allow someone to just jump into PostgreSQL from the root account, we had to get a little creative. The steps were:

• Create user in SQL -> <database> -> Users
• Log in using Cloud SQL Studio (or your preferred tool)
• Grant/copy access to databases/objects as required from the existing user, to the new one.

Create a new user

Follow the guidance, see the Google Documentation here: Create and manage users

Users created with built-in authentication are granted the cloudsqlsuperuser role, and have the same set of attributes as the native postgres user. More details on this step can be found under the Google Community: CLOUDSQLADMIN super user or other super users to be created

Sign in to Cloud SQL Studio

Now you have a new user, you can use the built in SQL Studio: Manage your data using Cloud SQL Studio

Grant/copy access

-- Explained with: https://www.postgresql.org/docs/current/user-manag.html

-- Grant all by schema
GRANT ALL ON SCHEMA public TO "<new_user>";

-- Clone user grants
GRANT "<original_user>" TO "<new_user>";

Cleaning up

Remove user

Normally, one would expect to drop the user simply:

DROP ROLE "<new_user>";

But, if anything has since been created leveraging the user/role you may get an error:

Invalid request: failed to delete user new_user: . role "new_user" cannot be dropped because some objects depend on it

In which case, it’s time to clean up the permissions if we want to drop this new user (as with most databases, this is the reverse of granting):

-- Revoke if you used all permissions
REVOKE ALL ON SCHEMA public FROM "<new_user>";

-- Revoke if you copied access:
REVOKE "<original_user>" FROM "<new_user>";

-- Revoke if you set database level permissions (not in the above guide)
REVOKE ALL ON DATABASE <database> FROM "<new_user>";

Notes & Links

Looking into Ownership & Permissions

-- Table Permissions:
SELECT *
  FROM information_schema.role_table_grants 
 WHERE 1=1
   AND UPPER(grantee) = UPPER('new_user')
;

-- Ownership:
SELECT *
  FROM pg_tables 
 WHERE 1=1
   AND UPPER(tableowner) = UPPER('new_user')
;

-- Schema permissions:
      SELECT r.usename AS grantor,
             e.usename AS grantee,
             nspname,
             privilege_type,
             is_grantable
        FROM pg_namespace
JOIN LATERAL (SELECT *
                FROM aclexplode(nspacl) AS x) a
          ON true
        JOIN pg_user e
          ON a.grantee = e.usesysid
        JOIN pg_user r
          ON a.grantor = r.usesysid 
       WHERE 1=1
       --AND UPPER(e.usename) = UPPER('<new_user>')
;

-- View Roles (and role memberships)
-- psql uses the bellow query when you issue \du command.
SELECT r.rolname,
       r.rolsuper,
       r.rolinherit,
       r.rolcreaterole,
       r.rolcreatedb,
       r.rolcanlogin,
       r.rolconnlimit,
       r.rolvaliduntil,
       ARRAY(SELECT b.rolname
               FROM pg_catalog.pg_auth_members m
               JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
              WHERE m.member = r.oid) 
       AS memberof,
       r.rolreplication,
       r.rolbypassrls
  FROM pg_catalog.pg_roles r
 WHERE r.rolname !~ '^pg_'
 --AND UPPER(r.rolname) = UPPER('new_user')
 ORDER BY 1
;

-- Code for \l command looks at :
SELECT d.datname    AS "Name",
       pg_catalog.pg_get_userbyid(d.datdba)        AS "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding)  AS "Encoding",
       d.datcollate AS "Collate",
       d.datctype   AS "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
  FROM pg_catalog.pg_database d
 ORDER BY 1
;