4259 links
  • Arnaud's links
  • Home
  • RSS Feed
  • Tag cloud
  • Login
search: tag:postgresql tag:aws tag:rds — newest first
  • Managing PostgreSQL users and roles | AWS Database Blog

    A faire en superuser:

    -- Revoke privileges from 'public' role
    REVOKE CREATE ON SCHEMA public FROM PUBLIC;
    REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;

    -- Create schema
    CREATE SCHEMA myschema

    -- Read-only role
    CREATE ROLE readonly;
    GRANT CONNECT ON DATABASE mydatabase TO readonly;
    GRANT USAGE ON SCHEMA myschema TO readonly;
    GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;
    ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;

    -- Read/write role
    CREATE ROLE readwrite;
    GRANT CONNECT ON DATABASE mydatabase TO readwrite;
    GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;
    GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;
    ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
    GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
    ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;

    -- Users creation
    CREATE USER reporting_user1 WITH PASSWORD 'some_secret_passwd';
    CREATE USER reporting_user2 WITH PASSWORD 'some_secret_passwd';
    CREATE USER app_user1 WITH PASSWORD 'some_secret_passwd';
    CREATE USER app_user2 WITH PASSWORD 'some_secret_passwd';

    -- Grant privileges to users
    GRANT readonly TO reporting_user1;
    GRANT readonly TO reporting_user2;
    GRANT readwrite TO app_user1;
    GRANT readwrite TO app_user2;

    August 3, 2019 - permalink - https://aws.amazon.com/fr/blogs/database/managing-postgresql-users-and-roles/
    aws postgresql rds