How To Create User Credentials And Grant Privileges In Postgres

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 playground;
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

DROP ROLE IF EXISTS developeraccess;
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

DROP ROLE IF EXISTS readaccess;
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

DROP ROLE IF EXISTS datahackaccess;
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!

Git
How to specify which Node version to use in Github Actions

When you want to specify which Node version to use in your Github Actions, you can use actions/setup-node@v2. The alternative way is to use a node container. When you try to use a publicly available node container like runs-on: node:alpine-xx, the pipeline gets stuck in a queue. runs-on is not …

AWS
Using semantic-release with AWS CodePipeline and CodeBuild

Here is the usual pattern of getting the source from a git repository in AWS CodePipeline. In the pipeline, we use AWS CodeStart to connect to a repo and get the source. Then, we pass it to the other stages, like deploy or publish. For some unknown reasons, CodePipeline downloads …

DBA
mysqldump Error: Unknown table ‘COLUMN_STATISTICS’ in information_schema (1109)

mysqldump 8 enabled a new flag called columm-statistics by default. When you have MySQL client above 8 and try to run mysqldump on older MySQL versions, you will get the error below. mysqldump: Couldn’t execute ‘SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM ‘$”number-of-buckets-specified”‘) FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = ‘myschema’ AND TABLE_NAME = ‘craue_config_setting’;’: Unknown …