SQL statement to Create Role fails on Postgres 12 using Dapper

Question

I am running Postgres 12 on Windows and have a .Net Core app which uses Dapper as an ORM:

The following query works fine:

var sql = "SELECT 1 FROM pg_roles WHERE rolname=@un"
var result = con.ExecuteScalar<int>(sql, new {un = "someuser"});

Now I'm trying to execute an sql statement that would create a role:

var sql = @"CREATE ROLE @un WITH LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION PASSWORD @pw";     
con.Execute(sql, new {un = "someuser", pw = "somepass");

This query fails with the following exception: Npgsql.PostgresException: '42601: syntax error at or near "$1"'.

What am I missing here?

Answer 1

The name of a role is an identifier. You can not pass identifiers as parameters, you will need to concatenate the name as a constant value into the SQL string, so that you execute this:

var sql = @"CREATE ROLE someuser WITH LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION PASSWORD @pw";     
con.Execute(sql, new {pw = "somepass");

I am not entirely sure sending the password as a parameter works either. You will need to test that.