CODEWITHSUNDEEP
postgresql
tommy_p1ckles
tommy_p1ckles

Reputation: 685

Connecting to Postgres database with read only access.

Is it possible to restrict my connection to a database through the psql client to be read only. I.E. The role using to connect to the database has full privileges on it, but I want to restrict some of my connections to being read-only.

Is this possible or do I have to simply create a new database role with my desired privileges.

Upvotes: 28

Views: 23943

Answers (5)

Archy Will He
Archy Will He

Reputation: 9767

Why not just

SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;

This command can be issued after the connection is made, i.e. typed into 'psql' after it has started and connected to the database. It will affect all transactions started within the current psql session. There is a variant on the command which affects only the current transaction (see docs below).

This does provide a safeguard against operators accidentally modifying important data, but is not securely read-only - an attacker with access to this connection can simply set the characteristics back to "READ WRITE" again.

Postgresql documentation here.

Upvotes: 13

brauliobo
brauliobo

Reputation: 6315

Based on @Vao's comment, just use:

SET default_transaction_read_only = on

This is config to be applied to the connection setup. i.e. can be placed in your psqlrc or ~/.psqlrc file to affect all future invocations of psql.

This does provide a safeguard against operators accidentally modifying important data, but is not securely read-only - an attacker with access to a connection created this way could simply set the characteristics back to "READ WRITE" again.

Postgresql documentation here

Upvotes: 2

Gabriel
Gabriel

Reputation: 61

So if you are trying to connect to the database using the node pg module, then I would recommend to use the options Like below, your client const should look like:

  new Client({  host: "localhost",
  user: "user name",
  port: 5432,
  database: "db name",
  options: "-c default_transaction_read_only=on"
})

You can also run this query after connecting:

    client.query(
  `SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;`,
  (err, res) => {
    if (!err) {
      console.log(res.rows);
    } else {
      console.log(err.message);
    }
  }
);

But I find the first way better.

Upvotes: 3

user27137772
user27137772

Reputation: 41

You can create read-only user as -> create a new database user; grant connect on DB; then grant usage on schema and then grant select on tables.

Here is a reference, https://tableplus.com/blog/2018/04/postgresql-how-to-create-read-only-user.html

However allowing read-only to a fully privileged user over a (specific) connection isn't feasible (using built-in features).

Upvotes: 2

Jason Jewel
Jason Jewel

Reputation: 57

Depending on the client you are using to connect you can set the connection to read only mode.

Using DBeaver as an example https://github.com/dbeaver/dbeaver/issues/4112

Upvotes: -2

Related Questions