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?

MDCCL
  • 8,520
  • 3
  • 30
  • 61
Martin Brown
  • 720
  • 1
  • 5
  • 15
  • 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 09:19

1 Answers1

8

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.

András Váczi
  • 31,278
  • 13
  • 101
  • 147