How to limit connections in a postgres database

This is how you can restrict connections for a specific database to zero:

postgres=# alter database db01 connection limit 0;
ALTER DATABASE

Verify

SELECT datname, datconnlimit FROM pg_database WHERE datname = 'db01';
-[ RECORD 1 ]+-------------
datname      | db01
datconnlimit | 0

Set back to unlimited:

ALTER DATABASE db01 CONNECTION LIMIT -1;
ALTER DATABASE

Verify:

SELECT datname, datconnlimit FROM pg_database WHERE datname = 'db01';
-[ RECORD 1 ]+-------------
datname      | db01
datconnlimit | -1

To limit the connections for a specific user only:

 psql
psql (15.13)
Type "help" for help.

postgres=# alter user read_db01 connection limit 0;
ALTER ROLE
postgres=# alter user read_db01 connection limit -1;
ALTER ROLE
postgres=#

The current setting can be verified with:

 SELECT rolname, rolconnlimit
FROM pg_roles
WHERE rolname = 'read_db01';
      rolname      | rolconnlimit
-------------------+--------------
 read_db01         |           -1

or, list all users that does have restrictions on the number of connections:

 SELECT rolname, rolconnlimit
FROM pg_roles
WHERE rolconnlimit != -1;
      rolname      | rolconnlimit
-------------------+--------------
 pganalyze         |            5
 read_db01         |            0
(2 rows)

How to connect via sqlplus with the connect string

To connect as sys using the entire connect string, use the following syntax:

sqlplus /nolog
SQL>connect sys@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=proddb01.mydomain.com)(PORT=1534))(CONNECT_DATA=(SERVICE_NAME=proddb01.mydomain.com)(INSTANCE_NAME=proddb01)(SERVER=DEDICATED)))' as sysdba

Result:

Enter password:
Connected.
SQL> select name,database_role from v$database;

NAME      DATABASE_ROLE
--------- ----------------
proddb01  PRIMARY

Deprecated security parameter in Oracle 12c: SEC_CASE_SENSITIVE_LOGON

Oracle states:

"The use of the Oracle instance initialization parameter SEC_CASE_SENSITIVE_LOGON is deprecated in favor of setting the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter to 12 to ensure that passwords are treated in a case-sensitive fashion.
Disabling password case sensitivity is not supported in Exclusive mode (when SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12 or 12a.)

See this post for an example used during implementation

Documentation here

Missing trailing slash in listener.ora caused ORA-27101 when attempting to connect

Not too long ago, I got an error when connecting to my database using TOAD:

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
IBM AIX RISC System/6000 Error: 2: No such file or directory

However, when connecting to the same database with sqlplus from the command line from a remote client, I could connect without errors.


Doc ID 1296982.1 "DVCA receives ORA-01034, ORA-27101" pointed me in the right direction:


"The value of ORACLE_HOME (or ORACLE_SID) passed to the DVCA utility does not match the value of ORACLE_HOME (or ORACLE_SID) that was in effect when the instance was started.

The shared memory segment key for an Oracle instance uses a hashed value based on the contents of ORACLE_HOME and ORACLE_SID. So, if one or the other of these values does not match what was used to start the instance, the resulting hash will not match, and one will encounter "ORA-27101: shared memory realm does not exist" when trying to connect to the instance.

This can commonly be caused by the presence (or lack thereof) of trailing slashes in the string for ORACLE_HOME, as passed to the -oh parameter of DVCA."

I changed my listener.ora file to read as follows:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mydb)
      (ORACLE_HOME = /u01/oracle/product/11204 )
      (SID_NAME = mydb)
    )
  )

to

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mydb)
      (ORACLE_HOME = /u01/oracle/product/11204/) <-- Note the trailing slash character at the end of the ORACLE_HOME path
      (SID_NAME = mydb)
    )
  )

Reload the listener with lsnrctl reload, and the listener once again accepted connections from TOAD.