2

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 [master] CREATE LOGIN [ExampleUserName] WITH PASSWORD = 'ExamplePassword', CHECK_EXPIRATION=OFF, CHECK_POLICY=ON;
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?

1
  • 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
    Jun 15 '18 at 9:19
7

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, privacy policy and cookie policy

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