I'm porting an application from SQL Server to PostgreSQL. Most of this is simple as the application only really does simple selects, inserts and updates. However I need to create a user to access the database. The SQL Sever code creates its user like this:

USE [ExampleDB];
CREATE USER [ExampleUserName] FOR LOGIN [ExampleUserName];
ALTER ROLE [db_datawriter] ADD MEMBER [ExampleUserName];
ALTER ROLE [db_datareader] ADD MEMBER [ExampleUserName];

What is the equivalent of that in PostgreSQL?

  • i don't think postgres has anything exactly like that... usually you grant permissions on the resource to public and then anyone with login access to the database can use them...
    – Jasen
    Commented Jun 15, 2018 at 9:19

1 Answer 1


There is no builtin role like that in PostgreSQL. However, you can set up one:

CREATE ROLE db_datawriter;

After this, you have to assign write privileges (usually INSERT, UPDATE, DELETE, maybe TRUNCATE) to it. To see how to do this for all present and future tables, read my older answer.

Normally, write access also implicates reads, therefore it make sense to add db_datawriter as a member of db_datareader (which was set up similarly, but with SELECT only):

GRANT db_datareader TO db_datawriter;

Then you can grant these two to different login users (or other roles) as necessary:

CREATE USER example_user IN ROLE db_datareader;  

Note that CREATE USER implicates being able to log in, unlike CREATE ROLE.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.