postgresql – How to limit a postgres role entirely to a single schema

I want to create a read-only role for a third party to access a handful of tables through the public schema without being able to view the rest of the tables publicly.

My original idea was to create a new scheme & # 39; readonly & # 39; to create and then create views in this scheme:

create view readonly.table1 as select * from public.table1

Then change the search path for the read-only user to only change it to the & # 39; read-only & # 39; Restrict scheme. However, it looks like I can still view the public schema as this role (although I can't select anything from tables in schema public).

Is there a way to remove all public schema visibility from a role? Unfortunately, it is not possible to move everything into a different scheme.

Permissions – Cannot connect to Postgres

I did a really stupid thing: I ran sudo chmod -R 777 / for all folders in the root directory. After that even sudo did not work due to invalid permissions. I fixed the problem with sudo, but I need to create a backup dump file of my database in PostgreSQL. As you can imagine, after my idiotic act pg_dump and psql stop working When I try to do it, I get:

could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"

postgresql – Postgres 11+: Are covering indexes (INCLUDE) useful for join / where conditions?

I want to better understand when index covering can be useful to enable index-only scans in Postgres 11+. As the documentation says, given the cover index

CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);

Queries like this can only use it for index scans:

SELECT y FROM tab WHERE x = 'key';

Now I'm wondering if such a cover index could also allow index scans if the cover columns are shown as conditions. For example, assume a cover index:

CREATE INDEX tab_x_y_z ON tab(x) INCLUDE (y, z);

Would this only allow index scans for the following queries?

SELECT z FROM tab WHERE x = 'key' AND y = 1;

SELECT x, y, z FROM (VALUES ('key1'),('key2'),('key3')) sub(id)
JOIN tab ON tab.x = WHERE y = 1;

postgresql – Postgres: Practical method / function for calculating the bearing change in coordinates

I will calculate point based acceleration and bearing relative to true / magnetic north from the sequence of GPS points with time stamp and instantaneous speed.

The data record looks like this:

postgres=> SELECT * FROM location_track LIMIT 10;
user_id  | timestamp  |                        geo                         |    lat     |    lon     | speed
      49 | 1458203631 | 0101000020E6100000F74BD5873F9C21C07530AC2C1BDF4340 | 39.7430168 | -8.8051722 |     9
      49 | 1458203632 | 0101000020E6100000F74BD5873F9C21C07530AC2C1BDF4340 | 39.7430168 | -8.8051722 |     9
      49 | 1458203633 | 0101000020E610000005888219539C21C0F8CC48731ADF4340 | 39.7429947 | -8.8053215 |     9
      49 | 1458203634 | 0101000020E610000005888219539C21C0F8CC48731ADF4340 | 39.7429947 | -8.8053215 |     9
      49 | 1458203635 | 0101000020E610000007D7378B729C21C05452CCE616DF4340 | 39.7428864 | -8.8055614 |  7.75
      49 | 1458203636 | 0101000020E610000007D7378B729C21C05452CCE616DF4340 | 39.7428864 | -8.8055614 |  7.75
      49 | 1458203637 | 0101000020E610000027B220DE849C21C04E4DDDF016DF4340 | 39.7428876 | -8.8057012 |     8
      49 | 1458203638 | 0101000020E610000027B220DE849C21C04E4DDDF016DF4340 | 39.7428876 | -8.8057012 |     8
      49 | 1458203639 | 0101000020E610000028EFE3688E9C21C03505D78118DF4340 | 39.7429354 |  -8.805774 |  8.25
      49 | 1458203640 | 0101000020E610000028EFE3688E9C21C03505D78118DF4340 | 39.7429354 |  -8.805774 |  8.25
(10 rows)

postgres=> d location_track
Table "postgres.location_track"
   Column   |       Type       |           Modifiers
 user_id    | integer          | not null
 timestamp  | integer          |
 geo        | geography        |
 lat        | double precision | not null
 lon        | double precision | not null
 speed      | real             |

Then I want to change that location_track Table by adding acceleration and bearing Columns. I know acceleration could be derived from speed / timestamp AS acceleration, but I wonder if there is a practical way or function in Postgres to deduce this bearing,

The GPS is received every 1 second, i.e. the bearing of the point in row 1 would be calculated in relation to the point in row 2 this way to the very last point. In addition, lat and lon are given in degrees, so they should be converted into radians.

Any practical way to do this in Postgres?

postgresql – Postgres, returns 0 rows if one row matches the condition, otherwise all rows are returned

I have a table with ten rows. One of the columns is a Boolean. Let's just say, for the sake of reasoning:

email text
is_activated boolean

I want to write a query that returns zero rows if is_activated applies to each of the lines. If none of the lines has is_activated When set to true, I want to return all rows in the table.

How can I do that?

json – How do you create a simple jsonb object from multiple arrays in postgres?

If I have three (or more) Postgres arrays with the same number of elements and want to create a multi-level JSONB object from them, how can I do that?


create temp table tbl1 as
    array('a', 'b', 'c') as keys,
    array('d', 'e', 'f') as val1,
    array('g', 'h', 'i') as val2;

Then I can do:

select jsonb_object(keys, val1) from tbl1;

And get:

{"a": "d", "b": "e", "c": "f"}

I can also stand val2 and get:

select jsonb_object(keys, val2) from tbl1;
 {"a": "g", "b": "h", "c": "i"}
(1 row)

But I can't see how I could get the following object:

  "a": { "val1": "d", "val2": "g" },
  "b": { "val1": "e", "val2": "h" },
  "c": { "val1": "f", "val2": "i" }

I am aware of jsonb_build_object() but that doesn't seem to fit here.

How could the above result be created?

postgresql – Postgres: relationship does not exist error

I used pg_restore to load my postgres database with a dump file.
I have connected to my database with my user:

sudo -u arajguru psql dump

select current_user;

Now I could see all the newly created tables:

dump=> dt
               List of relations
 Schema |       Name        | Type  |  Owner   
 public | Approvals         | table | arajguru
 public | Approvers         | table | arajguru
 public | Conditions        | table | arajguru
 public | Entities          | table | arajguru
 public | EntityDefinitions | table | arajguru
 public | Projects          | table | arajguru
 public | Rules             | table | arajguru
 public | run_history       | table | arajguru
(8 rows)

However, when I try to trigger a select * query, I get the following error:

dump=> select * from Approvals;
ERROR:  relation "approvals" does not exist
LINE 1: select * from Approvals;

What can be the reason for this error? Please help.

postgresql – Does the configuration parameter of the replica affect the primary / master RDS postgres?

i want to add hot_standby_feedback = on to an RDS readonly replica.

The problem is that the replica currently has a parameter group that is inherited from the master. My question is: add hot_standby_feedback = on to the master parameter group negatively influence the master instance so that I can better append a new parameter group to the replica?

postgresql – AWS RDS Postgres database connection password

I have an AWS RDS Postgres instance.

I connect to Powershell and the ODBC connection classes on Windows. I encountered an issue where the connection failed when my password contained a plus or minus sign. Other passwords connected as expected.

$DBConn = New-Object System.Data.Odbc.OdbcConnection
$DBConn.ConnectionString = "Driver={PostgreSQL UNICODE(x64)};;Port=5432;Database=postgres;Uid=xxxx;Pwd=xxxx;SslMode=Require;"

I assumed that this was a problem with the ODBC connection class, so I implemented it using the NpgsqlConnection classes. However, I have the exact same problem. A password with +/- has failed. Without being connected as expected.

var connString = ";Username=" + txtUsername.Text + ";Password=" + txtPassword.Text + ";Database=postgres;Ssl Mode=Require";
var conn = new NpgsqlConnection(connString);

I cannot see that it is a Windows problem as more people would see it. But I also can't see that it's an RDS Postgres edition. But I don't think it's my code since I've tested both ODBC and NpgsqlConnection. they fail in the same way.

So I'm looking for clues?