Difference between revisions of "Postgrest row level security via postgres"

From UVOO Tech Wiki
Jump to navigation Jump to search
(Created page with "https://postgrest.org/en/stable/how-tos/sql-user-management-using-postgres-users-and-passwords.html#sql-user-management-using-postgres-users-and-passwords")
 
 
(11 intermediate revisions by the same user not shown)
Line 1: Line 1:
 +
https://www.postgresql.org/message-id/CACxu%3DvKzshfnFrRb%3DDvTUdGTj1omiwwc4%2B8knDO9hZRpsgt31g%40mail.gmail.com
 +
 
https://postgrest.org/en/stable/how-tos/sql-user-management-using-postgres-users-and-passwords.html#sql-user-management-using-postgres-users-and-passwords
 
https://postgrest.org/en/stable/how-tos/sql-user-management-using-postgres-users-and-passwords.html#sql-user-management-using-postgres-users-and-passwords
 +
 +
```
 +
postgres=# CREATE EXTENSION IF NOT EXISTS pgjwt WITH SCHEMA ext_pgjwt;
 +
ERROR:  extension "pgjwt" is not available
 +
DETAIL:  Could not open extension control file "/opt/bitnami/postgresql/share/extension/pgjwt.control": No such file or directory.
 +
HINT:  The extension must first be installed on the system where PostgreSQL is running.
 +
```
 +
 +
Let's enable debug and go in as root to add extension
 +
 +
postgresql.values.yaml
 +
```
 +
tls:
 +
  enabled: true
 +
  autoGenerated: true
 +
primary:
 +
  containerSecurityContext:
 +
    enabled: true
 +
    # runAsUser: 1001
 +
    runAsUser: 0
 +
  service:
 +
    type: LoadBalancer
 +
    annotations:
 +
      metallb.universe.tf/address-pool: pool1
 +
diagnosticMode:
 +
  enabled: true
 +
```
 +
 +
```
 +
helm upgrade --install postgresql bitnami/postgresql -f postgresql.values.yaml --set audit.pgAuditLog=all
 +
```
 +
 +
 +
```
 +
# postgresql-server-dev-all
 +
apt-get update && apt-get -y install git gcc make
 +
cd /root/
 +
git clone https://github.com/michelp/pgjwt.git
 +
cd pgjwt
 +
make install
 +
cp pgjwt.control /opt/bitnami/postgresql/share/extension/
 +
```
 +
output
 +
```
 +
/bin/mkdir -p '/opt/bitnami/postgresql/share/extension'
 +
/bin/mkdir -p '/opt/bitnami/postgresql/share/extension'
 +
/usr/bin/install -c -m 644 .//pgjwt.control '/opt/bitnami/postgresql/share/extension/'
 +
/usr/bin/install -c -m 644 .//pgjwt--0.1.1.sql .//pgjwt--0.1.0--0.1.1.sql .//pgjwt--0.1.1--0.2.0.sql  '/opt/bitnami/postgresql/share/extension/'
 +
```
 +
 +
 +
```
 +
postgresql 22:07:45.71 INFO  ==> Configuring TLS
 +
chmod: changing permissions of '/opt/bitnami/postgresql/certs/tls.key': Read-only file system
 +
postgresql 22:07:45.71 WARN  ==> Could not set compulsory permissions (600) on file /opt/bitnami/postgresql/certs/tls.key
 +
postgresql 22:07:45.73 INFO  ==> Configuring synchronous_replication
 +
postgresql 22:07:45.77 INFO  ==> Loading custom scripts...
 +
postgresql 22:07:45.77 INFO  ==> Enabling remote connections
 +
postgresql 22:07:45.78 INFO  ==> ** PostgreSQL setup finished! **
 +
 +
postgresql 22:07:45.80 INFO  ==> ** Starting PostgreSQL **
 +
2023-04-27 22:07:45.853 GMT [1] LOG:  pgaudit extension initialized
 +
2023-04-27 22:07:45.855 GMT [1] FATAL:  could not load private key file "/opt/bitnami/postgresql/certs/tls.key": Permission denied
 +
2023-04-27 22:07:45.855 GMT [1] LOG:  database system is shut down
 +
```
 +
 +
```
 +
kubectl delete pv <pv_name> --grace-period=0 --force
 +
And then deleting the finalizer using:
 +
 +
kubectl patch pv <pv_name> -p '{"metadata": {"finalizers": null}}'
 +
```
 +
 +
```
 +
  finalizers:
 +
  - kubernetes.io/pv-protection
 +
```

Latest revision as of 00:17, 28 April 2023

https://www.postgresql.org/message-id/CACxu%3DvKzshfnFrRb%3DDvTUdGTj1omiwwc4%2B8knDO9hZRpsgt31g%40mail.gmail.com

https://postgrest.org/en/stable/how-tos/sql-user-management-using-postgres-users-and-passwords.html#sql-user-management-using-postgres-users-and-passwords 

postgres=# CREATE EXTENSION IF NOT EXISTS pgjwt WITH SCHEMA ext_pgjwt;
ERROR:  extension "pgjwt" is not available
DETAIL:  Could not open extension control file "/opt/bitnami/postgresql/share/extension/pgjwt.control": No such file or directory.
HINT:  The extension must first be installed on the system where PostgreSQL is running.

Let's enable debug and go in as root to add extension

postgresql.values.yaml 

tls:
  enabled: true
  autoGenerated: true
primary:
  containerSecurityContext:
    enabled: true
    # runAsUser: 1001
    runAsUser: 0
  service:
    type: LoadBalancer
    annotations:
      metallb.universe.tf/address-pool: pool1
diagnosticMode:
  enabled: true

helm upgrade --install postgresql bitnami/postgresql -f postgresql.values.yaml --set audit.pgAuditLog=all

# postgresql-server-dev-all
apt-get update && apt-get -y install git gcc make
cd /root/
git clone https://github.com/michelp/pgjwt.git
cd pgjwt
make install
cp pgjwt.control /opt/bitnami/postgresql/share/extension/

output 

/bin/mkdir -p '/opt/bitnami/postgresql/share/extension'
/bin/mkdir -p '/opt/bitnami/postgresql/share/extension'
/usr/bin/install -c -m 644 .//pgjwt.control '/opt/bitnami/postgresql/share/extension/'
/usr/bin/install -c -m 644 .//pgjwt--0.1.1.sql .//pgjwt--0.1.0--0.1.1.sql .//pgjwt--0.1.1--0.2.0.sql  '/opt/bitnami/postgresql/share/extension/'

postgresql 22:07:45.71 INFO  ==> Configuring TLS
chmod: changing permissions of '/opt/bitnami/postgresql/certs/tls.key': Read-only file system
postgresql 22:07:45.71 WARN  ==> Could not set compulsory permissions (600) on file /opt/bitnami/postgresql/certs/tls.key
postgresql 22:07:45.73 INFO  ==> Configuring synchronous_replication
postgresql 22:07:45.77 INFO  ==> Loading custom scripts...
postgresql 22:07:45.77 INFO  ==> Enabling remote connections
postgresql 22:07:45.78 INFO  ==> ** PostgreSQL setup finished! **

postgresql 22:07:45.80 INFO  ==> ** Starting PostgreSQL **
2023-04-27 22:07:45.853 GMT [1] LOG:  pgaudit extension initialized
2023-04-27 22:07:45.855 GMT [1] FATAL:  could not load private key file "/opt/bitnami/postgresql/certs/tls.key": Permission denied
2023-04-27 22:07:45.855 GMT [1] LOG:  database system is shut down

kubectl delete pv <pv_name> --grace-period=0 --force
And then deleting the finalizer using:

kubectl patch pv <pv_name> -p '{"metadata": {"finalizers": null}}'

  finalizers:
  - kubernetes.io/pv-protection
Retrieved from "https://tech.uvoo.io/index.php?title=Postgrest_row_level_security_via_postgres&oldid=4146"