Skip to content

Instantly share code, notes, and snippets.

@checco
Forked from oinopion/read-access.sql
Last active March 22, 2024 08:32
Show Gist options
  • Save checco/40960cd7e993e568e4bd4ede27a25197 to your computer and use it in GitHub Desktop.
Save checco/40960cd7e993e568e4bd4ede27a25197 to your computer and use it in GitHub Desktop.
How to create a read only user in AWS RDS PostgreSQL and a user with superuser privileges on AWS RDS PostgreSQL
--
-- Read only
--
-- Create a group
CREATE ROLE postgres_ro_group;
-- Grant access to existing tables
GRANT USAGE ON SCHEMA public TO postgres_ro_group;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO postgres_ro_group;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO postgres_ro_group;
-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO postgres_ro_group;
-- Create a final user with password
CREATE USER postgres_ro WITH PASSWORD 'secret';
GRANT postgres_ro_group TO postgres_ro;
--
-- Superuser
--
-- Create a final user with password
CREATE USER postgres_adm WITH PASSWORD 'secret';
GRANT rds_superuser to postgres_adm;
@Systho
Copy link

Systho commented Jul 1, 2020

You may want to add :

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO postgres_ro_group;

for future sequences

@diego-ojeda-binbash
Copy link

Are you sure this works? I mean you still seem to be using public schema which already grants too many privileges by default. I would say your postgres_ro user can actually do more than SELECT. Have you tried INSERTS, CREATEs, and so on with it?
Reference: https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/

@Systho
Copy link

Systho commented Feb 4, 2021

No I have not but this line only add SELECT privileges so I would not think it would allow me to run anything different than a SELECT

@mconigliaro
Copy link

No I have not but this line only add SELECT privileges so I would not think it would allow me to run anything different than a SELECT

Didn't test it did you? 😂

@mconigliaro
Copy link

By default, anyone can create new tables in the public schema. Here's what's missing:

REVOKE CREATE ON SCHEMA public FROM public;

https://stackoverflow.com/questions/56940582/how-do-i-create-a-readonly-user-in-postgres-in-aws-rds

@dwelch2344
Copy link

dwelch2344 commented Jan 8, 2024

I've run into this before and prefer to do the following:

-- should be run by dbadmin user
CREATE OR REPLACE FUNCTION public.grant_readonly_hourly()
RETURNS void AS
$do$
  DECLARE
    V_ROW record;
    V_CMD VARCHAR;
    V_USER VARCHAR := 'prod_pump_user';
  BEGIN
    FOR V_ROW IN
      SELECT ns.nspname as namespace FROM pg_namespace ns where ns.nspname not in ('namespace', 'pg_toast', 'pg_catalog', 'information_schema')
      LOOP

        FOR V_CMD IN
          VALUES
            (concat('GRANT SELECT ON ALL TABLES IN SCHEMA ', V_ROW.namespace, ' TO ', V_USER, ';')),
            (concat('GRANT USAGE ON SCHEMA ', V_ROW.namespace, ' TO ', V_USER, ';')),
            (concat('GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA ', V_ROW.namespace, ' TO ', V_USER, ';')),
            (concat('ALTER DEFAULT PRIVILEGES IN SCHEMA ', V_ROW.namespace, ' GRANT SELECT ON TABLES TO ', V_USER, ';')),
            (concat('ALTER DEFAULT PRIVILEGES IN SCHEMA ', V_ROW.namespace, ' GRANT SELECT, USAGE ON SEQUENCES TO ', V_USER, ';'))
          LOOP
            raise notice '[running] %', V_CMD;
            EXECUTE(V_CMD);
            raise notice '[complete]';
          END LOOP;
      END LOOP;
  END
$do$
LANGUAGE plpgsql
SECURITY DEFINER; -- CRITICAL as it allows this function to run with the user who *created* it, vs called it


Next step is to use pg_cron to keep it up to date...

SELECT cron.schedule('0 * * * *', $$SELECT public.grant_readonly_hourly()$$);

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment