max-ieremenko / SqlDatabase

Command-line tool and PowerShell module for MSSQL Server and PostgreSQL.
MIT License
14 stars 7 forks source link

Postgres: run scripts as a non-superuser to check if they can do something silly #53

Closed max-ieremenko closed 4 months ago

max-ieremenko commented 5 months ago

It is related to the discussion in the PR #50.

Created by @parmcoder

Use-case

Somehow, I want to be able to run some scripts as a non-superuser to check if they can do something silly. Not sure if I did that correctly, I wrote something like SqlDatabase -database='Host=localhost;Username=myuser;Password=qwerty; Database=MyDatabase' -fromSql='SQL' [command].

Problem

I technically wanna quickly create a database from a new cluster and run some scripts using myuser, but it will instead log me into the myuser database. That database exists because I used your script to create it at first. Then, when I try to create new databases using another user, it breaks.

max-ieremenko commented 5 months ago

Existing use case

max-ieremenko commented 5 months ago

Create command assumes, that MyDatabase may not exist yet and changes the database name in the connection string to null.

It works perfectly for postgres user, because postgres database always exists. For any other myuser it leads to PostgresException : 3D000: database "myuser" does not exist

parmcoder commented 5 months ago

From this comment

To make work the existing and your use case:

// builder.Database = null;
builder.Database = "postgres"
_connectionStringMaster = builder.ToString();

I reject. That will always log me into the postgres database.

DBA can prevent non-superusers from connecting to postgres database. My application relies heavily on the database because it is a client application. Unlike web applications that send requests to the server, the client application has to set roles in the database directly to the users.

Suppose you insist on doing it like this. Mark the documentation: "This command will always run the SQL script on the postgres database".

max-ieremenko commented 5 months ago

The create command is designed to fall back to postgres database if the target one does not exist yet. That is why builder.Database = "postgres" makes sense.

In your case, the target database already exists and you want to prevent switching to postgres. For this, the execute command should be used.

The following changes are in the branch feature/53

parmcoder commented 4 months ago

Thank you for your explanation! I see why it has to be like that. I will wait until the next release is out. 😄

max-ieremenko commented 4 months ago

released in version 4.2.2