Setting read/write access for a PostgreSQL database

Question

Pulumi · Accepted Answer

To set read/write access for a PostgreSQL database using Pulumi, you would typically do the following steps:

1. **Define the PostgreSQL database** - This would involve specifying the parameters for the database such as the name, encoding, and owner.
2. **Create user roles** - You would need to create roles or use existing ones to whom you will assign permissions for accessing the database. Roles can be either individual users or groups.
3. **Assign privileges** - After you have your roles, you'll grant specific privileges to those roles to define what operations they can perform on the database.

Below is an example TypeScript program that shows how to create a PostgreSQL database, add a user role, and set the read and write (SELECT, INSERT, UPDATE, DELETE) privileges on the database for that role. We'll use the `postgresql` package from Pulumi to interact with a PostgreSQL database. Remember, you will need to have the PostgreSQL provider set up in your Pulumi environment to use these resources.

The example assumes you already have a PostgreSQL server running and have the required PostgreSQL provider details configured with Pulumi.

Let's go through the program:

```typescript
import * as pulumi from '@pulumi/pulumi';
import * as postgresql from '@pulumi/postgresql';

// Create a new database.
const db = new postgresql.Database('my-database', {
  // Define the parameters for the database.
});

// Create a new role with login privileges.
const userRole = new postgresql.Role('my-role', {
  login: true,
  password: 'supersecretpassword', // Please use a more secure way to handle passwords, for example, using Pulumi Config secrets.
  createDb: false,
  connectionLimit: -1,
});

// Grant read/write privileges ('SELECT', 'INSERT', 'UPDATE', 'DELETE') on all tables in the database to the role.
const rwAllTables = new postgresql.Grant('read-write-all-tables', {
  database: db.name,
  role: userRole.name,
  objectType: 'table',
  privileges: ['SELECT', 'INSERT', 'UPDATE', 'DELETE'], // Adjust privileges as needed for your use case.
  // The 'schema' would be specified here if you are using a specific schema.
  // For example: schema: 'public'
});

export const databaseName = db.name;
export const roleName = userRole.name;

```

This program creates a new PostgreSQL database and a user role with the ability to log in but without the ability to create additional databases. Then, it grants the new role read/write access to all tables within the database that is created. The privileges array is where you define the exact access: in this case, `SELECT`, `INSERT`, `UPDATE`, and `DELETE` are included, covering the typical read and write operations you might need.

Please note:
- Replace `'my-database'` and `'my-role'` with your actual database and role names.
- The actual privileges should be adjusted based on your specific requirements.
- The password `'supersecretpassword'` must be handled securely. In a production scenario, you might use Pulumi’s secret handling to ensure the password is encrypted.
- The `schema` field is commented out, but you might need to specify this if you're working with specific schemas other than the default (`public`).

Once you apply this Pulumi program, you will have a PostgreSQL database ready with a specific role that has read/write access to all tables in that database. Remember to reference the [official Pulumi PostgreSQL provider documentation](https://www.pulumi.com/registry/packages/postgresql/) for more details on the available properties and usage.