Search code examples
postgresqlsql-grant

Create readonly user on all 1000 postgres databases and schema's

Using Vault I'm trying to create an on-demand temporary read-only users in a Postgres 11.8 instance.

I will have:

  • 1000+ databases (one per customer aka tenant)
  • Each database has a public and reporting schema.

So I'm trying to find a way to grant this read-only user access to every database, and on all tables in both schemas.

While I came up with the following snippet:

-- Create a new user
CREATE ROLE "my-readonly-user" WITH LOGIN PASSWORD 'test123';

-- Grant access to the two schema's we have
GRANT USAGE ON SCHEMA public TO "my-readonly-user";
GRANT USAGE ON SCHEMA reporting TO "my-readonly-user";

-- Grant access to all tables in our two schema's
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "my-readonly-user";
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO "my-readonly-user";

-- Grant access to sequences
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO "my-readonly-user";
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA reporting TO "my-readonly-user";

-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "my-readonly-user";
ALTER DEFAULT PRIVILEGES IN SCHEMA reporting GRANT SELECT ON TABLES TO "my-readonly-user";

It will only apply to 1 single database (the current one). If I login with the readonly user and switch to some databases, I don't see any tables except in the first database.

A cluster holds many databases, which hold many schemas. Schemas (even with the same name) in different DBs are unrelated. Granting privileges for a schema only applies to this particular schema in the current DB (the current DB at the time of granting). https://stackoverflow.com/a/24923877/1409047

Because of that limitation, it seems to make the grant snippet from above more complex. Should I iterate all my databases somehow and run the snippet? How would I do the database switch? Is it even possible in plain SQL (as required by Vault's API)? Anyone did this before?

Note: doing the same in MySQL only requires 2 lines of code, using wildcards *.* which is not supported in Postgres afaik:

CREATE USER '{{name}}'@'10.0.0.0/255.0.0.0' IDENTIFIED BY '{{password}}';
GRANT SELECT, SHOW DATABASES, SHOW VIEW ON *.* TO '{{name}}'@'10.0.0.0/255.0.0.0';
Solution

  • It is a deliberate design decision that an SQL statement cannot affect objects in a database different from the one that you are connected to.

    Yes, you will have to iterate through all databases in your cluster and run your script there.

    Note that there is a mistake in your script: you shouldn't grant a read-only user USAGE on sequences, else they can modify the sequence value. SELECT is fine.

    What I would do is create a read_only_group (with NOLOGIN) and grant all these permissions to that role. Then, when there is a request for a read-only user, create a user and add it to that group, so that it inherits the group privileges. Don't grant anything to the user itself, so that you can easily DROP it when it is no longer needed.