pg-db-admin

This is a utility to administer postgres databases that are behind a firewall.

Using a lambda that is on the same VPC as the database, this utility can ensure a database exists with a specific owner. This utilizes AWS IAM to secure administration instead of using an SSH Tunnel or VPN. This also limits the actions that a user can take, making it extremely hard to perform malicious commands.

AWS Lambda setup

The Lambda requires specific configuration to work properly:

• A SecretsManager Secret containing the connection string as a postgres URL.
• DB_CONN_URL_SECRET_ID env var containing ARN of the AWS SecretsManager Secret.
• The execution role must have access to the above secret.
• The executing lambda must have network access to the postgres cluster.

HTTP API

The function exposes a small REST API. Each resource is idempotent: a POST carrying useExisting: true adopts an object that already exists instead of failing, which lets an infrastructure module take ownership of objects that were created by hand.

Resource	Routes	Notes
Databases	/databases, /databases/{name}
Roles	/roles, /roles/{name}	attributes: createDb, createRole, replication
Role members	/roles/{target}/members
Schema privileges	/databases/{database}/schema_privileges	Grants full access to a database and its public schema
Table privileges	/databases/{database}/table_privileges	Grants a specific (typically read-only) set of privileges on a schema
Default grants	/roles/{role}/default_grants
Publications	/databases/{database}/publications	FOR ALL TABLES, FOR TABLES IN SCHEMA (postgres 15+), or FOR TABLE
Replication slots	/databases/{database}/replication_slots	DELETE really drops the slot
Settings	GET /settings	Reports wal_level, the current user, and replication slot usage

Logical replication

Publications and replication slots exist to support logical replication consumers (e.g. Google Datastream). They require the server to run with wal_level = logical; on Cloud SQL that means setting the cloudsql.logical_decoding database flag to on, which requires an instance restart. GET /settings reports the current wal_level so a caller can detect this before it tries to create a slot.

Creating a slot also requires the admin role itself to have the REPLICATION attribute. The admin role can grant that to itself (PUT /roles/{admin} with attributes.replication = true) because Cloud SQL permits members of cloudsqlsuperuser to set the attribute.

Deleting a replication slot is the one destructive operation in this API. Every other resource treats DELETE as a no-op, because dropping a database or a role would destroy data that the caller did not intend to lose. A slot is different: an abandoned logical slot retains WAL indefinitely and will eventually fill the instance's disk, so it must be dropped when its consumer goes away. A slot that still has a consumer attached cannot be dropped, so the consumer is disconnected first.

How it works

There are 3 actions that the AWS code performs to grant database access:

• create-database
• create-user
• create-db-access

create-database

This action performs the following steps:

1. Ensures that a new user exists whose role name is databaseName.
2. Ensures that a database with the injected databaseName exists.
3. The newly-created database has an owner of the databaseName role.

create-user

This action performs the following steps:

1. Ensure the user username exists.
2. If username role already exists, set the password to password.

create-db-access

This action performs the following steps:

1. Add username as a member to the owner of the database.
2. Alters username so that the database owner has access to any schema objects created by username.
3. Grant all privileges on the databaseName and the public schema in databaseName.

In Practice

In practice, the following should be true.

1. An application role runs migrations to create and alter schema objects.
2. Implicitly, this application role owns newly-created schema objects.
3. All application roles are a member of the role that owns the database -- giving them implicit access to all schema objects.
4. The database owner role is given access to all schema objects (present and future).

It's important to note that an application user created for a worker application typically does not perform migrations. This application user is granted access to schema objects because it has membership in the database owner role (which has explicit access to schema objects).

Repair database

In early versions of this module (below v0.2.0), schema objects were created and managed differently. Your database may be left in a bad state. To fix, follow these steps:

1. Set the database owner to a role with the same name as the database.
2. Ensure all application roles have membership to the database owner role.
3. Alter default privileges FOR ROLE <application-role> TO <database-owner-role>.
4. Grant privileges to all schema objects to application role on database and schema.
5. Set ownership of tables to any application role.

Example access privilege outputs

oracle-> \dp
                                          Access privileges
 Schema |         Name          | Type  |      Access privileges      | Column privileges | Policies 
--------+-----------------------+-------+-----------------------------+-------------------+----------
 public | expiring_downloads    | table | postgres0=arwdDxt/postgres0+|                   | 
        |                       |       | oracle=arwdDxt/postgres0    |                   | 
 public | flyway_schema_history  | table | postgres0=arwdDxt/postgres0+|                   | 
        |                       |       | oracle=arwdDxt/postgres0    |                   | 
 public | module_artifacts      | table | postgres0=arwdDxt/postgres0+|                   | 
        |                       |       | oracle=arwdDxt/postgres0    |                   | 
 public | module_versions       | table | postgres0=arwdDxt/postgres0+|                   | 
        |                       |       | oracle=arwdDxt/postgres0    |                   | 
 public | modules               | table | postgres0=arwdDxt/postgres0+|                   | 
        |                       |       | oracle=arwdDxt/postgres0    |                   | 
(5 rows)

oracle-> \ddp
                        Default access privileges
    Owner     | Schema |   Type   |           Access privileges           
--------------+--------+----------+---------------------------------------
 oracle-zshgw |        | function | =X/"oracle-zshgw"                    +
              |        |          | oracle=X/"oracle-zshgw"              +
              |        |          | "oracle-zshgw"=X/"oracle-zshgw"
 oracle-zshgw |        | schema   | oracle=UC/"oracle-zshgw"             +
              |        |          | "oracle-zshgw"=UC/"oracle-zshgw"
 oracle-zshgw |        | sequence | oracle=rwU/"oracle-zshgw"            +
              |        |          | "oracle-zshgw"=rwU/"oracle-zshgw"
 oracle-zshgw |        | table    | oracle=arwdDxt/"oracle-zshgw"        +
              |        |          | "oracle-zshgw"=arwdDxt/"oracle-zshgw"
 oracle-zshgw |        | type     | =U/"oracle-zshgw"                    +
              |        |          | oracle=U/"oracle-zshgw"              +
              |        |          | "oracle-zshgw"=U/"oracle-zshgw"
(5 rows)