Postgresql – How to setup vault and Postgres in Google cloud to have the correct permissions

google-cloud-sqlpermissionspostgresql

The Problem

I am trying to setup Hashicorp vault and Postgres in Google Cloud.

I am using liquibase to manage the schema and when that does a migration, it retrieves a username/password from vault and then runs under that user to perform the migration.

However, the default postgres user, or any other user that tries to use that table can't and receives the ERROR: permission denied for relation.

If I understand correctly, the users are being created while logged into as the role of postgres. This all works locally but the google cloud stuff seems to be setup in a vastly different way.

Here are some outputs to see what the database is setup as:

\du
                                                List of roles
            Role name             |                         Attributes                         |      Member of
----------------------------------+------------------------------------ 
------------------------+---------------------
 cloudsqladmin                    | Superuser, Create role, Create DB, 
Replication, Bypass RLS | {}
 cloudsqlagent                    | Create role, Create DB                                     
| {cloudsqlsuperuser}
 cloudsqlreplica                  | Replication                                                
| {}
 cloudsqlsuperuser                | Create role, Create DB                                     
| {}
 postgres                         | Create role, Create DB                                     
| {cloudsqlsuperuser}
 test                             | Create role, Create DB                                     
| {cloudsqlsuperuser}
 v-token-power-watc-p079t4r13s85w | Password valid until              
| {cloudsqlsuperuser}


\dt
                            List of relations
 Schema |         Name          | Type  |              Owner
--------+-----------------------+-------+------------------------------ 
 public | databasechangelog     | table | v-token-power-watc-p079t4r13s85w
 public | databasechangeloglock | table | v-token-power-watc-p079t4r13s85w
 public | test                  | table | v-token-power-watc-p079t4r13s85w

The vault creation SQL looks something like this: 

CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; 
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "{{name}}"; 
GRANT cloudsqlsuperuser to "{{name}}";"

I've tried playing with the roles in a number of ways but it seems all of the new roles that I create are always put into their own sandbox and can't play with any other roles. I'm at a lost at this point of exactly what to do next, it seems that no matter the grant options I can pass, it doens't help.

Current Questions

Is it that the owner needs to be the cloudsqlsuperuser group instead of the temp ownwer?

Is there a way to make that the default instead of having to make sure all my tables are created with the right owner?

Replicate with a local docker instance

Docker Command to start Postgres Database

docker run --rm --name database -v $(pwd)/setup.sql:/docker-entrypoint-initdb.d/setup.sql -e POSTGRES_USER=temp -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=mydb postgres:9.6

Contents of setup.sql

ALTER ROLE postgres RENAME TO cloudsqladmin;
CREATE ROLE cloudsqlsuperuser WITH CREATEDB CREATEROLE;
ALTER DATABASE mydb OWNER TO cloudsqlsuperuser;
CREATE ROLE "postgres" WITH LOGIN CREATEDB CREATEROLE IN ROLE cloudsqlsuperuser;

Creating user under postgres simulating vault user creation

docker exec -ti database psql -U postgres -d mydb -c "CREATE ROLE testuser WITH LOGIN IN ROLE cloudsqlsuperuser"

Create test table from testuser

docker exec -ti database psql -U testuser -d mydb -c "CREATE TABLE test (col1 text)"

Try and select table demonstrating error

docker exec -ti database psql -U postgres -d mydb -c "SELECT * FROM test"

Best Answer

Short Summary

You have to run an REASSIGN OWNED BY "$VAULT_USERNAME" TO "cloudsqlsuperuser" after the table/sequence/function creation process to reassign back to a the common role.

Longer Answer

Postgres 9.6 CREATE TABLE will by default create the table under the user's role. Because the vault user is designed to be a temporary credential, we need to also make sure that the objects created under the temporary roles are reassigned back to a common role of some kind. In the specific case I outlined in Google Cloud, we are going to reassign back to cloudsqlsuperuser.

The one command I found to work is the REASSIGN OWNED BY current_user to "cloudsqlsuperuser" command that will then take all of the objects owned by that temporary user and assign back to that common role.

Bonus: liquibase config

I am using liquibase to do my migrations and this is in my change log to fix the permissions everytime we run a migration:

<databaseChangeLog>
    <changeSet author="elindblom" id="fix-permissions" context="" logicalFilePath="fix-permissions" runAlways="true" runOrder="last">
        <sqlFile path="baselines/sql/fix-permissions.sql" splitStatements="false" relativeToChangelogFile="true" stripComments="false"/>
    </changeSet>
   ... rest of your change logs after ...
</databaseChangeLog>

The SQL looks like this:

REASSIGN OWNED BY current_user TO "cloudsqlsuperuser";

The changeSet runs after everything comes to completion and runs every-time. The very important bits are the runAlways and runOrder elements.

Related Question