How To Create User Credentials And Grant Privileges In Postgres
- By : Mydatahack
- Category : DBA, Infrastructure
- Tags: Credentials, DBA, Postgres, Privileges
Creating user credentials and grand privileges in Postgres can be tricky. You have to make sure each user has the right access level to the right tables and schemas. Each database works differently even they all are relational databases.
Postgres has its own way of creating users and granting privileges. It took me a few goes to work it out. If you just grant privileges for a user, the access privileges do not work for the table created in the future. The trickiest part is to run the alter default privilege statement so that the user can have the same privileges for the newly created tables, which is stated in the Postgres documentation on ALTER DEFAULT PRIVILEGES.
Ok, let’s have a look at a scenario and script to make the magic happen!
Scenarios
I launched a Postgres RDS instance in AWS and created schemas with root account in the database called mydatahack. If you sign up for AWS, you have 12 months of free RDS access as Amazone RDS Free Tier, which is pretty cool. Of course, you can download and install it locally.
CREATE schema datamart;
CREATE schema staging;
CREATE schema usermanaged;
CREATE schema wordpress;
CREATE schema googleanalytics;
I need to create 3 different users.
- developer: All access to all tables in all schemas
- readonly: Read only access to all tables in all schemas
- datahack: All access to all tables in the playground and usermanaged schemas and readonly for the rest
Steps
- Create a group with CREATE ROLE statement
- Grant Usage on Schema with GRANT USAGE ON SCHEMA statement
- Grant Privileges on Exisiting Tables with GRANT <> On ALL TABLES statement
- Set set the privileges that will be applied to objects created in the future with ALTER DEFAULT PRIVILEGES
- Great a user with password and add to the user group
Scripts
developer user
CREATE ROLE developeraccess;
GRANT USAGE ON SCHEMA
playground, datamart, staging, usermanaged, wordpress, googleanalytics
TO developeraccess;
GRANT ALL ON ALL TABLES IN SCHEMA
playground, datamart, staging, usermanaged, wordpress, googleanalytics
TO developeraccess;
ALTER DEFAULT PRIVILEGES IN SCHEMA
playground, datamart, staging, usermanaged, wordpress, googleanalytics
GRANT ALL ON TABLES TO developeraccess;
DROP USER IF EXISTS developer;
CREATE USER developer WITH PASSWORD '';
GRANT developeraccess TO developer;
readonly user
CREATE ROLE readaccess;
GRANT USAGE ON SCHEMA
playground, datamart, staging, usermanaged, wordpress, googleanalytics
TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA
playground, datamart, staging, usermanaged, wordpress, googleanalytics
TO readaccess;
ALTER DEFAULT PRIVILEGES IN SCHEMA
playground, datamart, staging, usermanaged, wordpress, googleanalytics
GRANT SELECT ON TABLES TO readaccess;
DROP USER IF EXISTS readonly;
CREATE USER readonly WITH PASSWORD '';
GRANT readaccess TO readonly;
datahack user
CREATE ROLE datahackaccess;
GRANT USAGE ON SCHEMA
playground, datamart, staging, usermanaged, wordpress, googleanalytics
TO datahackaccess;
GRANT ALL ON ALL TABLES IN SCHEMA
playground, usermanaged
TO datahackaccess;
ALTER DEFAULT PRIVILEGES IN SCHEMA
playground, usermanaged
GRANT ALL ON TABLES TO datahackaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA
datamart, staging, wordpress, googleanalytics
TO datahackaccess;
ALTER DEFAULT PRIVILEGES IN SCHEMA
datamart, staging, wordpress, googleanalytics
GRANT SELECT ON TABLES TO datahackaccess;
DROP USER IF EXISTS datahack;
CREATE USER datahack WITH PASSWORD '';
GRANT datahackaccess TO datahack;
You have successfully created the users that meet the requirements!
Cool!