Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [read-only-database]

The tag has no usage guidance.

0
votes
1answer
40 views

Prevent read access on primary AG replica, but allow it on the readable secondary

We have a unique situation where we want to allow users to query a readable secondary replica of a database with SSMS for adhoc reporting, but not allow them to ever read from the primary replica. We ...
0
votes
1answer
27 views

Configure mongo for one shot full db read

I have a kubernetes cluster on bare metal with 3 machines having 8Gb of ram each. All my apps and a mongodb 4.0.9 replicaset runs on it. There is an import program that: Download a 8Gb mongo dump ...
3
votes
1answer
73 views

Query a database specific configuration parameter

I have a postgres 9.6 server with > 100 databases on it. When doing a mass-migrate, I use statements like this: ALTER DATABASE mydatabase SET default_transaction_read_only = false; Which works great....
1
vote
1answer
74 views

Insert- and Read-only table in Postgres

I want to create an "immutable" Postgres database, where users can insert/select (write/read) data only, but can not update/delete (change/delete). I came to know there is FOR UPDATE lock, but did ...
0
votes
1answer
29 views

How to set mariadb read_write for some specific tables while starting as read_only=1

I am trying to set a mariadb slave which is exact replica of master db. As per the suggestion I start my slave with command --relay-log=mysql-relay-bin" "--log-slave-updates=1" "--read-only=1" . But ...
0
votes
1answer
97 views

MySQL/MariaDB Read only except for a single table

I Have a database with lots of tables and read_only enabled in the config, is it possible to set a single table to be writable?
0
votes
2answers
131 views

Optimising MySQL database with read only tables

I have a MySQL database with 15 tables. Three of them are used for website authentication and three other are tables that are frequently read and written to. Then other tables are set in stone and I'd ...
3
votes
0answers
704 views

Postgres: permission denied for relation for read_only user

I am trying to set up a new read only user in postgres. I did the following steps to create the new user: sudo -u postgres createuser --interactive name: read_only_user superuser: n ...
1
vote
1answer
51 views

Is there any AWS RDS Utility to Manage Master Slave mysql db with read write splitting?

I am looking for a solution for production which can manage the read and write splitting on master and slave connection as i can't affford to define multiple connection string for R/W in whole ...
2
votes
2answers
395 views

Mysql 8.0 and read-only not working as expected

I have a new Master/Slave config running. Everything is happily replication. I have added a secondary user whose role will be to manage the databases and tables. Currently the following privileges ...
1
vote
0answers
189 views

Secondary Replica Read-Only Routing via Linked Server working from one server, but not the other

I have been given the task of setting up a read-only linked server on ServerA to Availability Group listener ListnerX. ListnerX points to ServerY or ServerZ (ServerY is usually the primary). Read-only ...
3
votes
1answer
126 views

SQL server Read Only permission automatically for new databases

When I setup my database I create a sql user that my application runs as. The sql user gets read only access to all the databases on the server. The problem is the user won’t have read only access to ...
0
votes
1answer
49 views

How do I truncate performance_schema tables on a read replica?

On occasion, you have to truncate events_statements_summary_by_digest, when it's hit the row limit. On a write master, this is easy, but on a read replica, there doesn't seem to be a way to do it. The ...
1
vote
2answers
580 views

What's the difference between --read-only and just permit SELECT queries?

I'm using Amazon Aurora and I have two endpoints, my endpoint which can read/write are having some issues to execute queries, these are the issues: SQLSTATE[HY000]: General error: 1290 The MySQL ...
1
vote
0answers
84 views

How do read queries against Postgres's JSONB type compare to Couchbase?

I'm working on a small project which would involve running queries against deeply-nested (up to 5 layers) keys in dictionaries. This would be specifically a read-heavy environment, with only one large ...
15
votes
2answers
277 views

Columnstore Index in read_only filegroup prevents CheckDB

It appears setting a filegroup to read_only prevents dbcc checkdb for the entire database if the filegroup contains a columnstore index. When attempting to run checkdb or checkfilegroup (for any ...
0
votes
1answer
4k views

Do I need to execute “create extension pgcrypto” everytime?

I have used crypt() in one of my functions to hash user password. But what I observed the next day I started my pc that the same thing failed as cryptographic functions became unavailable upon restart....
3
votes
3answers
2k views

Unable to create read-only user in PostgreSQL 9.5

I seem to be having trouble creating a read-only user for a database in a fresh install of PostgreSQL 9.5 with PostGIS 2.2 on Ubuntu 16.04. Here is what I've done so far: Ran this, CREATE ROLE ...
3
votes
1answer
56 views

Does SQL Server route connections to a secondary replica which has commit mode as synchronous in case of an AG with multiple secondary nodes?

If I have an AG with 4 different nodes as below: Primary Node Secondary Node 1 - Asynchronous Commit - Read Only Yes Secondary Node 2 - Synchronous Commit - Read Only No Secondary Node 3 - ...
3
votes
2answers
530 views

Using postgres_fdw to connect from one read replica to another

I have two postgres database servers in AWS, A and B. These databases have different tables. Both databases also have their own read replica. My goal is a simple solution for doing cross database ...
0
votes
1answer
635 views

Worry to delete Read replica RDS

I created a read replica from RDS , and I do want to delete it. I am so worry about affecting the origin RDS which is in PROD . Is it recommended to take snapshots before deletion of read replica ...
0
votes
2answers
515 views

Obvious reason Postgres Users can't read a table?

Situation: a PG user (non superuser, inherits from parent role) that is a member of a Role/Group cannot read from specific tables even though these Object Privileges have been specified: DBName - ...
1
vote
2answers
70 views

Proc caching on read only db's/replicas

I've been given the task of finding which stored procs are being called in our database. To that end I started querying sys.dm_exec_cached_plans. All is well and good, until I go over to our ...
1
vote
1answer
1k views

Create temp tables in Amazon RDS PostgreSQL read-replica

I have dozens of legacy stored procedures which create temporary tables inside for collecting results for read-only application. I've created read-replica of my PostgreSQL in Amazon RDS and tried to ...
2
votes
3answers
139 views

Restrictions on user-defined queries

I have a client that wants me to implement a webpage that allows users to enter custom queries in a text box so they can directly query the SQL Server database. I am concerned about users entering ...
1
vote
1answer
4k views

How can I expose an Amazon RDS database as read only to other partners who access through home broadband?

In one of our projects we have hosted our web application on an Amazon Linux AMI instance and it relies on a Amazon RDS MySQL instance as DB server. One of our client's developer wants to have access ...
0
votes
1answer
185 views

intel p3600 SSD no increase in read speed - postgres rdbms vs hdd

We run a custom application that produces analyzable data-sets for medical research. the amount of data we have is about 600GB in form 3Billion rows distributed in 600+ tables it is hosted in ...
2
votes
0answers
422 views

Basic PostgreSQL 9.x Performance Optimization Strategies for Reads/Analysis

Postgres comes, out of the box, configured as a general purpose database for both OLTP and OLAP types of loads. My need is to do use Postgres, in this case, purely as an OLAP running aggregate queries ...
2
votes
1answer
743 views

Set a filegroup to readonly

I have a set of dimensions which are updated only during specific times. I have moved these tables to a specific filegroup named Dimensions. I wanna keep this filegroup as readonly. When ETL runs, it ...
1
vote
0answers
109 views

Are there security-issues with a read-only-account on Oracle-DBs?

I am currently working as IT-maintenance-guy for a classic MVC-system(oracle-db/jboss) at public sector. The newest feature should be the shutdown of my oracle-db-readOnly-Account cause of "security ...
1
vote
3answers
1k views

How to backup a secondary log shipped standby mode database

We have a secondary transaction log shipped database configured for standby mode. Its primary purpose is for data recovery and for select only queries for reports etc. As the database is very big we ...
2
votes
1answer
57 views

Track write requests on Read-only database

I have changed one of the databases to read-only in SQL Server 2005. I would like to track if someone tries to write to the database. Any suggestions on how to track such requests (end user write ...
2
votes
1answer
2k views

Synchronizing a RDS instance with another RDS instance

I have a database on an RDS Instance (master) that needs to be synchronized to another RDS instance (slave). I created a read-replica for the first one, but that is created as read_only so I cannot do ...
2
votes
1answer
680 views

Implications of setting READ_Write

I am looking to update several users on READ_ONLY databases. As the databases are used 24/7 globally, and maintenance windows have just past, I would like to do this during a low-activity moment (to ...
3
votes
0answers
409 views

Table queries locked during long insert SQL server

Everyday runs a sql job that loads data from bulk files and load them in auxiliary tables, to adapt and then copy to the destination table where applications read (and only read) the data. I am ...
3
votes
0answers
770 views

Optimizing a single table, write-once, read-many PostgreSQL database

I've just finished constructing a table of ~835 million rows using Google's ngram dataset, aggregated on the years in which they occurred so that each 2-,3-,4-, and 5-gram is represented by a single ...
3
votes
1answer
8k views

Revoke write privileges vs setting database to readonly

I am planning to do a database migration to a new server. During the transition stage, I do not want Apache user to be able to write anything to the database. I have two options. Revoke write ...
3
votes
1answer
8k views

How to start mysqld with read-only data directory?

I'm trying to start MariaDB in a docker container, using a data directory from the host mounted read-only. MySQLd is of course shut down on the host. Unfortunately, even with adding the read_only flag ...
4
votes
1answer
10k views

Create new Read Only user on DB2 on the whole database

I am an oracle/sql server DBA having limited knowledge in IBM DB2. We have DB2 9.7 fp4 running on AIX 6. I would like to create a new user and grant him READ only privilege on a particular database. ...
2
votes
2answers
2k views

How do I map a SQL Server login to a user that already exists in a read-only database?

One of our database users complains that they can't select from one of the databases in this particualr instance while they have no problems in accessing others in this very same instance. The one ...