Author: admin

Posted on

How to set Postres Read Only Role for a Read only user script

connect by command console to the database:
1) /usr/pgsql-10/bin/psql -U postgres
select the current db for which you want to create the read only user:
2) \c <database>
3) create the role
4) grant the permission after the connection to the right db

Reference taken from this blog:

Script to Create Read-Only user:

CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'Test1234' 
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

Assign permission to this read only user:

GRANT CONNECT ON DATABASE YourDatabaseName TO Read_Only_User;
GRANT USAGE ON SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read_Only_User;
Posted on

Tomcat Manager Configuration

  1. Find the CATALINA_HOME/webapps/manager/META-INF/context.xml file and add the comment markers around the Valve.
    <Context antiResourceLocking="false" privileged="true" >
    <!--
    <Valve className="org.apache.catalina.valves.RemoteAddrValve" allow="127\.\d+\.\d+\.\d+|::1|0:0:0:0:0:0:0:1" />
    -->
    </Context>
  2. in the file apache-tomcat-9.0.11/conf/tomcat-users.xml
    set up these rows :

    <role rolename=”manager-gui”/>
    <role rolename=”manager-script”/>
    <user username=”admin” password=”password” roles=”manager-gui,manager-script” /></tomcat-users>

  3. Connect to localhost:8080/manager/status to verify the account
Posted on

Drop active postgres connection

down vote
accepted
This will drop existing connections except for yours:

Query pg_stat_activity and get the pid values you want to kill, then issue SELECT pg_terminate_backend(pid int) to them.

PostgreSQL 9.2 and above:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = ‘TARGET_DB’ — ← change this to your DB
AND pid <> pg_backend_pid();

PostgreSQL 9.1 and below:

SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = ‘TARGET_DB’ — ← change this to your DB
AND procpid <> pg_backend_pid();

Once you disconnect everyone you will have to disconnect and issue the DROP DATABASE command from a connection from another database aka not the one your trying to drop.