Tags » PostgreSQL

Using Row Security Policies

-- Simple passwd-file based example
CREATE TABLE passwd (
  user_name             text UNIQUE NOT NULL,
  pwhash                text,
  uid                   int  PRIMARY KEY,
  gid                   int  NOT NULL,
  real_name             text NOT NULL,
  home_phone            text,
  extra_info            text,
  home_dir              text NOT NULL,
  shell                 text NOT NULL
);

CREATE ROLE admin;  -- Administrator
CREATE ROLE bob;    -- Normal user
CREATE ROLE alice;  -- Normal user

-- Populate the table
INSERT INTO passwd VALUES
  ('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash');
INSERT INTO passwd VALUES
  ('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh');
INSERT INTO passwd VALUES
  ('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh');

-- Be sure to enable row level security on the table
ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;

-- Create policies
-- Administrator can see all rows and add any rows
CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);
-- Normal users can view all rows
CREATE POLICY all_view ON passwd FOR SELECT USING (true);
-- Normal users can update their own records, but
-- limit which shells a normal user is allowed to set
CREATE POLICY user_mod ON passwd FOR UPDATE
  USING (current_user = user_name)
  WITH CHECK (
    current_user = user_name AND
    shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh')
  );

-- Allow admin all normal rights
GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;
-- Users only get select access on public columns
GRANT SELECT
  (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
  ON passwd TO public;
-- Allow users to update certain columns
GRANT UPDATE
  (pwhash, real_name, home_phone, extra_info, shell)
  ON passwd TO public;

Postgresql

Importing from Oracle to PostgreSQL using CSV file OR How to get Oracle data without a Foreign Data Wrapper

Hi, folks.

At my work, my boss needed urgently a report involving PostgreSQL and Oracle Databases. As almost the situations that already uses data from both are gathered by PHP, I’ve never had the need to install… 541 more words

Oracle

Exception: org/postgresql/Driver : Unsupported major.minor version 52.0

change jdk version use openjdk-8

using

#update-alternatives –config java

loading vector files from postgis to geoserver

We will demonstrate how to publish vector layers stored in a PostGIS as layer in Geoserver. We will be loading a vector file from our PostGIS server and publish it as a layer in Geoserver. 242 more words

Science

Audit Log and Partitioning

Hi all,

For those who have to log high load databases, and worse, need to keep this logs for a long time and always available, partitioning is the way. 1,633 more words

Computer

Auto vacuum and analyse larger tables

Postgresql database operations becomes slower if table gets larger. In order to improve the performance, we can configure to auto-vacuum the table for every x inserts/updates/deletes. 41 more words