30

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.

1
  • 7
    you can ALTER USER username set default_transaction_read_only = on; for this, but definetely creating a new user is better (because user can "swith off" ro transaction) Commented Jan 31, 2017 at 14:39

5 Answers 5

14

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.

Sign up to request clarification or add additional context in comments.

1 Comment

Note: this only works until the user says \c <db_name>...
4

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.

Comments

2

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).

2 Comments

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
I think the closing statement is too broad. I presume your intention is to say that one cannot create a securely read-only connection. But if the aim is just to prevent operators accidental typos, then one can provide a read-only connection. The only caveat is that a user could easily switch it back to a read-write connection.
2

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

Comments

-2

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

1 Comment

This answer would be much better if it didn't rely on a third-party tool like DBeaver which not many people use, and if it replicated the important details here, rather than just linking to some other page. But don't do that now - the details are already stated in other answers higher up the page.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.