1

PREFACE (EDIT):

Per comments on my original post, this question has been modified to focus on the PostgreSQL-specific elements of my goals. A separate question for the MySQL version has been posted here: MySQL Database Setup Script(s) - Limiting Created User Access (mostly a copy/paste of this question but with SQL "scripts" specific to that RDBMS)


I'm building a stand-alone application for which I will be giving users the option of storing information in one of three (currently) database systems: PostgreSQL, MySQL, or SQLite. I'm not actually a DBA, but I've had some experience with setting up databases and such for my development projects over the years. Obviously, as a part of the initial setup, I'm going to need to create the required structure in the chosen database system. For the PostgreSQL and MySQL options, I'm also creating groups/users to access the new structure, but I'm wanting to impose restrictions on data access, both for the newly created users and for existing users. What I'm wanting is:

  1. My new users should only have access to the new structure and objects I create. They should not be able to access any existing structure that may be present in the RDBMS to avoid potential security risks and such.
  2. Existing users should not be able to access my new structure or objects. I'm not storing any personal or particularly "sensitive" information anywhere, but I'd prefer users not be able to just go in and change the structure or data manually.

For SQLite, this isn't necessarily a problem - I'm encrypting the database file and, since SQLite doesn't have the concept of "users", that's about the best I can do. But for PostgreSQL and MySQL, I just want to make sure I get it "right".

During initial setup I'll be creating the following roles: MyAppDevelopers (group), MyAppAdmin (user), and MyAppUser (user). The application user will have to provide a set of credentials for an existing user that has the CREATE ROLE and CREATE DATABASE permissions. If it's a new PostgreSQL installation, they'll be prompted to set up a password for the postgres user instead, which should have all the permissions it needs.

For the role creation part, I have this (simplified from a DO procedure that simulates the IF NOT EXISTS clause):

--
-- Name: MyAppDevelopers; Type: ROLE;
--
CREATE ROLE "MyAppDevelopers" WITH
    NOLOGIN
    NOSUPERUSER
    INHERIT
    NOCREATEDB
    NOCREATEROLE
    NOREPLICATION;

COMMENT ON ROLE "MyAppDevelopers"
    IS 'User group for application users from My App Development';

--
-- Name: MyAppAdmin; Type: ROLE;
--
CREATE ROLE "MyAppAdmin" WITH
    LOGIN
    NOSUPERUSER
    INHERIT
    CREATEDB
    NOCREATEROLE
    NOREPLICATION
    IN ROLE "MyAppDevelopers"
    PASSWORD '#APASS#';

    COMMENT ON ROLE "MyAppAdmin"
        IS 'PostgreSQL user for My App database administration';

--
-- Name: MyAppUser; Type: ROLE;
--
CREATE ROLE "MyAppUser" WITH
    LOGIN
    NOSUPERUSER
    INHERIT
    NOCREATEDB
    NOCREATEROLE
    NOREPLICATION
    IN ROLE "MyAppDevelopers"
    PASSWORD '#UPASS#';

    COMMENT ON ROLE "MyAppUser"
        IS 'PostgreSQL user for My App general use';

Nothing too fancy there. I'm creating my group/users and putting the users into the group. It's the next "step" of creating the database and schemata where I'm trying to fine-tune the access levels. I may be going a bit overboard or I may be missing/overlooking something, but here's how I have it now:

--
-- Name: myappdb; Type: DATABASE; Owner: MyAppDevelopers;
--
CREATE DATABASE myappdb WITH
    OWNER = "MyAppDevelopers"
    ENCODING = 'UTF8'
    LC_COLLATE = 'English_United States.1252'
    LC_CTYPE = 'English_United States.1252'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

COMMENT ON DATABASE myappdb
    IS 'Data and configuration information storage for My App';

GRANT ALL PRIVILEGES ON DATABASE myappdb TO "MyAppDevelopers";

REVOKE ALL PRIVILEGES ON DATABASE myappdb FROM public;

--
-- Name: AppData; Type: SCHEMA; Owner: MyAppDevelopers;
--
CREATE SCHEMA "AppData"
    AUTHORIZATION "MyAppDevelopers";

ALTER DEFAULT PRIVILEGES
    FOR ROLE "MyAppDevelopers"
    IN SCHEMA "AppData"
    REVOKE ALL PRIVILEGES ON TABLES FROM public;

ALTER DEFAULT PRIVILEGES
    FOR ROLE "MyAppDevelopers"
    IN SCHEMA "AppData"
    REVOKE ALL PRIVILEGES ON SEQUENCES FROM public;

ALTER DEFAULT PRIVILEGES
    FOR ROLE "MyAppDevelopers"
    IN SCHEMA "AppData"
    REVOKE ALL PRIVILEGES ON ROUTINES FROM public;

ALTER DEFAULT PRIVILEGES
    FOR ROLE "MyAppDevelopers"
    IN SCHEMA "AppData"
    REVOKE ALL PRIVILEGES ON TYPES FROM public;

ALTER DEFAULT PRIVILEGES
    FOR ROLE "MyAppDevelopers"
    REVOKE ALL PRIVILEGES ON SCHEMAS FROM public;

ALTER DEFAULT PRIVILEGES
    FOR ROLE "MyAppDevelopers"
    IN SCHEMA "AppData"
    GRANT ALL PRIVILEGES ON TABLES TO "MyAppDevelopers";

ALTER DEFAULT PRIVILEGES
    FOR ROLE "MyAppDevelopers"
    IN SCHEMA "AppData"
    GRANT ALL PRIVILEGES ON SEQUENCES TO "MyAppDevelopers";

ALTER DEFAULT PRIVILEGES
    FOR ROLE "MyAppDevelopers"
    IN SCHEMA "AppData"
    GRANT ALL PRIVILEGES ON ROUTINES TO "MyAppDevelopers";

ALTER DEFAULT PRIVILEGES
    FOR ROLE "MyAppDevelopers"
    IN SCHEMA "AppData"
    GRANT ALL PRIVILEGES ON TYPES TO "MyAppDevelopers";

ALTER DEFAULT PRIVILEGES
    FOR ROLE "MyAppDevelopers"
    GRANT ALL PRIVILEGES ON SCHEMAS TO "MyAppDevelopers";

REVOKE ALL PRIVILEGES ON SCHEMA "AppData" FROM public;

--
-- Name: AppSettings; Type: SCHEMA; Owner: MyAppDevelopers;
--
CREATE SCHEMA "AppSettings"
    AUTHORIZATION "MyAppDevelopers";

ALTER DEFAULT PRIVILEGES
    FOR ROLE "MyAppDevelopers"
    IN SCHEMA "AppSettings"
    REVOKE ALL PRIVILEGES ON TABLES FROM public;

ALTER DEFAULT PRIVILEGES
    FOR ROLE "MyAppDevelopers"
    IN SCHEMA "AppSettings"
    REVOKE ALL PRIVILEGES ON SEQUENCES FROM public;

ALTER DEFAULT PRIVILEGES
    FOR ROLE "MyAppDevelopers"
    IN SCHEMA "AppSettings"
    REVOKE ALL PRIVILEGES ON ROUTINES FROM public;

ALTER DEFAULT PRIVILEGES
    FOR ROLE "MyAppDevelopers"
    IN SCHEMA "AppSettings"
    REVOKE ALL PRIVILEGES ON TYPES FROM public;

ALTER DEFAULT PRIVILEGES
    FOR ROLE "MyAppDevelopers"
    REVOKE ALL PRIVILEGES ON SCHEMAS FROM public;

ALTER DEFAULT PRIVILEGES
    FOR ROLE "MyAppDevelopers"
    IN SCHEMA "AppSettings"
    GRANT ALL PRIVILEGES ON TABLES TO "MyAppDevelopers";

ALTER DEFAULT PRIVILEGES
    FOR ROLE "MyAppDevelopers"
    IN SCHEMA "AppSettings"
    GRANT ALL PRIVILEGES ON SEQUENCES TO "MyAppDevelopers";

ALTER DEFAULT PRIVILEGES
    FOR ROLE "MyAppDevelopers"
    IN SCHEMA "AppSettings"
    GRANT ALL PRIVILEGES ON ROUTINES TO "MyAppDevelopers";

ALTER DEFAULT PRIVILEGES
    FOR ROLE "MyAppDevelopers"
    IN SCHEMA "AppSettings"
    GRANT ALL PRIVILEGES ON TYPES TO "MyAppDevelopers";

ALTER DEFAULT PRIVILEGES
    FOR ROLE "MyAppDevelopers"
    GRANT ALL PRIVILEGES ON SCHEMAS TO "MyAppDevelopers";

REVOKE ALL PRIVILEGES ON SCHEMA "AppSettings" FROM public;

All of the tables, views, functions, sequences, etc. will be created after logging out of the maintenance/administrator user and then back in using the MyAppAdmin user created above. There are other things I'd like to do from a setup perspective as a part of this script (like implementing some sort of schema versioning), but they're outside of the scope of the basic user/permissions setup in this question, so I'll save those for later.

So, I guess my question(s) to all you wonderful DBA's out there is, am I SQL-ing right here? Am I missing something obvious that might conflict with my stated goals? Is there a "better"/more efficient way to get this done? I'd appreciate any feedback or suggestions.

3
  • You should ask separate questions for MySQL and Postgres. It's unlikely that you'll get one answer that covers both approaches in depth.
    – user1822
    Commented Jul 14, 2022 at 17:36
  • Thank you. I considered doing that, but I was trying to avoid the dreaded "duplicate question". However, since they are two separate and distinct systems, perhaps that would be enough to distinguish them. Commented Jul 14, 2022 at 18:06
  • @a_horse_with_no_name - I've split the question into two. They're mostly just copy/paste of each other, but each is focused on a specific DBMS now. Thank you again for the suggestion and please let me know if I can improve them in any way. Commented Jul 14, 2022 at 19:32

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.